Excel help

[Russ]

hi guys

doing a spreadsheet for work, and just wondered if this formula was possible.

i want it to say a date, and in x amount of years, i want that date to change to the word exprired.

i.e. 29/11/07 - on the 29/11/10 (where x = 3 ) read Expired

can this be done?

[SiM]

Do you mean you want to display the date until the expiry date?
for 29\11\10 its this
if(today()=40511,"expired",40511)

[SiM]

Oh I think I understand what you mean now...
You will need to input either the start date and x value or just the end date - unless you want to write a vba function, then you could get it to save the date that you ran the function, then it will only need x value. The method above has the end date hard coded into the formula...

if(today()=start_date+(365)*x,"expired",40511)
need to input start_date and x value with this

[Russ]

sent you a pm sim

[Lucio]

It's slightly more accurate to use the following (only because it covers leap years TBH)

IF(NOW()>date,IF(YEARFRAC(NOW(),date)>=3,"Expired",date),date)

The reason for two IF, formula's is it covers a case when a future date is entered and is so far in the future, it'd trigger the "Expired" message

Replace "date" with cell reference for your date column and you can replace the 3 with whichever number you want to expire the column on.

[Russ]

sorted, cheers sim and lucio