Expiration dates

[Mike]

I've created a spread sheet that lists employee names and the date
their license was issued in col E. All the licenses are good for only
one year. I have a formula
=IF(DATEDIF(TODAY(),E5,"D")<20,"Renew","Current") in D5.

If the license is within 20 days of expiring I get the word "Renew"
back and if it's current then I get "Current" back. However I have one
slight problem. If the license has actually expired I get #NUM! back.
How do I fix this? And is there a way I can have "Expired" returned to
D5?

Any help would be appreciated.

-Mike

 

[Bob Phillips]

Mike,

Try this

=IF(DATE(YEAR(E5)+1,MONTH(E5),DAY(E5))-TODAY()<20,"Renew","Current")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

One way:

=IF(DATE(YEAR(E5)+1,MONTH(E5),DAY(E5))<TODAY(), "Expired",
IF(DATE(YEAR(E5)+1,MONTH(E5),DAY(E5))-TODAY()<20,"Renew","Current"))