ExcelBanter - Nested formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Nested formula (https://www.excelbanter.com/excel-discussion-misc-queries/119689-nested-formula.html)

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!!!!

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

"PhillyD" wrote:

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!!!!

Elkar,

Thank you very much - it works a treat!

"Elkar" wrote:

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

"PhillyD" wrote:

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!!!!