-
Excel help
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 :p) read Expired
can this be done?
-
Re: Excel help
Do you mean you want to display the date until the expiry date?
for 29\11\10 its this
if(today()=40511,"expired",40511)
-
Re: Excel help
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
-
Re: Excel help
-
Re: Excel help
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.
-
Re: Excel help
sorted, cheers sim and lucio :)