# Excel Formula - Return Expired Dates



## texboy (Nov 20, 2003)

Hello guys,

I'd like a formula that can return expired (past) dates and perhaps with a message such; "expired" or something that flags that date.



Please help!


----------



## darkwood (May 15, 2005)

Probably go with an IF statement...but my suggestion would be to include a cell that shows the current date, as in =TODAY() in whatever cell you wish, so that everytime you open the spreadsheet, that cell will default to the current date. 

Then..say the current date is in A1 and your date is in B2

=IF(B2<$A$1, "Expired","")

You could even play with conditional formatting and make the Expired part turn red or another color when it appears, to make it more noticeable to the eye.

Hope that helps.


----------



## texboy (Nov 20, 2003)

Thank you darwood,

Sorry I didn't reply before, but I got really busy at work.

The IF and the Conditional Formatting worked great. 

What about a formula that can flag the date 30 days before it expires... I'd like to be able to notify the licensed personnel that their license is about to expire and that they need to renew it.

Thanks for the help.


----------



## darkwood (May 15, 2005)

On your date fields , do a conditional format that says

If cell value is less than or equal to (the cell number of your current date) minus 60. So in my above example in B2, you select conditional formatting, and If cell value is less than or equal to =$D$1-60, then format as you wish.

Try that and let me know if it suits your needs.

Regards,

darkwood


----------



## texboy (Nov 20, 2003)

I'm confused...
I must be doing something wrong. 
Let's see:
A1 =TODAY()
B2 = Expiration dates

I did the conditional formatting as you mentioned. 
Am I supposed to add another column to get the date that will expire within 30 days? or do I add another formula in another cell?

Thanks


----------



## darkwood (May 15, 2005)

Actually, you can do multiple formulas within conditional formatting. Click the "add" button, to input a second formula. Use the first formula as the 30 day one, and the last formula as the initial one I gave you for the expired ones. Make the 30 day yellow, and the expired, red, for example. 

Try that, and hopefully that will fix everything. If not, I'd be glad to create a brief example and send it to you.


----------



## texboy (Nov 20, 2003)

Can you post an example.

Texboy


----------



## darkwood (May 15, 2005)

Sorry texboy...I typed it wrong in the previous message..here is the example I quickly wrote.


----------



## texboy (Nov 20, 2003)

Hey darkwood,

I example you attached is fine, however, this had already worked fine for me. What I was wondering is if for example on your spreadsheet, the date "2-5-06" if I could get a result of, let say: will expire within 30 days or even better: "20 day" (date will expire in 20 days from today).

For example, if one of the licenses will expire on 2-17-06 and today is 1-17-06, when I open the spreadsheet, I want to be able to identify this date that it will be expiring in 31 days from today. And tomorrow (1-18-06), it can show that it will expire in 30 days etc... 

We want to know when a specific date will expire 30-31 days before they do.

Thanks for you help. I hope this is not too confusing.


----------



## ReeKorl (Mar 25, 2005)

Is this what you are after?


----------



## texboy (Nov 20, 2003)

That's exactly what I needed.

Thanks again Reekorl.

Thanks to you too dardwood.


----------



## zoesadler (Oct 17, 2008)

Just to say thanks for your thread!

It's really helped me out!!

ray:


----------



## swlamedic (Apr 3, 2010)

I know this post is old, but I am working with the same issues. I have read everything, and have fixed my problems. You guys are life savers. They should make a book on excel formulas. They probably do, I have just never seen one. Thanks again.


----------



## N00b I.T Me (Aug 23, 2010)

thanks guys this really have helped me out in doing some excel stuff best place for advices ray: to the I.T Gods LOL


----------



## N00b I.T Me (Aug 23, 2010)

Quick question i have been trying to get rid of a #VALUE! i have done everything but cant seem to make it dissappear when it has been corrected. because the dates i have made = true but the #VALUE! keeps on coming up how do i fix it?


----------



## RSpecianjr (Jan 20, 2010)

Hey N00b I.T Me,

Can you post your example? Its easier to troubleshoot something like this with a full example.

Thanks,

Robert D. Specian Jr.


----------

