Nested formula

[Guest]

Cell A1 contains a date and is formatted as a date
Cell B1 should show the text NO COMPETENCY if A1 is blank, however, if A1 is
populated with a date, B1 should equal A1+(365*2)
Cell C1 should be blank if A1 is blank, however, if the date in B1 is less
than TODAY() display "EXPIRED", otherwise display "VALID".
I have tried to use IF and OR functions to do this but I obviously have the
syntax wrong is it merely returns VALUE#! for any option.
HELP!!!!

 

[Guest]

In cell B1 use this formula:

=IF(A1<>"",DATE(YEAR(A1),MONTH(A1),DAY(A1)+(365*2)),"No Competency")

In cell C1 use this formula:

=IF(A1<>"",IF(TODAY()>A1,"Expired","Valid"),"")

I'm not sure the significance of the (365*2) portion in the first formula,
but if you want to add 2 years, then you could just use YEAR(A1)+2 instead.
This would account for Leap Years. Adding to the DAY() function would not.
Just something to keep in mind.

HTH,
Elkar

 

[Guest]

Elkar,

Thank you very much - it works a treat!

In cell B1 use this formula:

=IF(A1<>"",DATE(YEAR(A1),MONTH(A1),DAY(A1)+(365*2)),"No Competency")

In cell C1 use this formula:

=IF(A1<>"",IF(TODAY()>A1,"Expired","Valid"),"")

I'm not sure the significance of the (365*2) portion in the first formula,
but if you want to add 2 years, then you could just use YEAR(A1)+2 instead.
This would account for Leap Years. Adding to the DAY() function would not.
Just something to keep in mind.

HTH,
Elkar