February 9, 2017
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.
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
February 9, 2017
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
VIP
Trusted Members
June 25, 2016
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
1 Guest(s)