Register
Log In
Unanswered
Home
February 9, 2017
Offline
| B | C | D | E | F | H | I | J | K | L |
| SC-CCR# | REGULAR / GLOBAL / MINOR | Document Title | Document # | Next Rev. # | Request By | Issue Date | Document Reviewer(s) | Approval/ Rejected Date | Effective Date |
| SC-CCR-1970 | Regular | 08/04/16 | 03/16/17 | 03/16/17 |
=IF(OR(L2="",L2="void"),"",IF((TODAY()>=EDATE(I2,6)),1,""))
The formula I am trying to create is if the Issue date is over 6 months I want a 1 in the cell I put the formula in, however if column L is not blank then I want the cell with the formula to be blank. Column L will either have a date or the word void. One of the issues I am having is if the date in L is >than 6 months it wants to put a 1 in the cell with the formula.
Dashboards
June 25, 2016
Offline
Hi rdawbrey
Give this a try
=IF(OR(L2="",L2="void"),"",IF(OR(TODAY()>=EDATE(I2,6),TODAY()>=EDATE(L2,6)),1,""))
In future it would be helpful if you can include an attachment with sample data and their expected result.
Sunny
February 9, 2017
Offline
Hi Sunny,
Please see the attached file
Dashboards
June 25, 2016
Offline
Try this then in cell R2
=IF(K2<>"","",IF(OR(TODAY()>=EDATE(H2,6),TODAY()>=EDATE(K2,6)),1,""))
Sunny
February 9, 2017
Offline
Hi Sunny,
The above formula does the trick. I am having a hard time putting this in a sentence so I understand how the formula works. The part that is confusing me is bold, underlined and italic.
=IF(K7<>"","",IF(OR(TODAY()>=EDATE(H7,6),TODAY()>=EDATE(K7,6)),1,""))
In my mind I read it something like this
If K7 is not blank then leave cell blank, but if the date H7 is greater than or equal to 6 months or……then put a 1 otherwise leave blank
Thanks,
Diane
February 9, 2017
Offline
Hi Sunny,
If column H does not have a date I also need the cell with the formula to be blank.
Thanks
Dashboards
June 25, 2016
Offline
Hi Diane
Maybe I misunderstood your request as I thought you wanted a 1 if the effective date is > 6 months. I too was wondering earlier why you wanted this.
One of the issues I am having is if the date in L is >than 6 months it wants to put a 1 in the cell with the formula.
If that is the case, then give this a try in cell R2:
=IF(OR(H2="",K2<>""),"",IF(TODAY()>=EDATE(H2,6),1,""))
Explanation:
If Issue Date (H2) is a blank or Effective Date (K2) is not a blank, then show a 1 else show a blank.
Otherwise
If Issue Date (H2) is more than 6 months (compared to today's date) then show a 1 else show a blank.
Hope I got it right this time.
Sunny
Most Users Ever Online: 57
Currently Online: azri adrin, Mark Lund, Rudi
19 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
