Excel Formula - Return Expired Dates

texboy · 01-03-2006, 11:45 AM

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 · 01-03-2006, 10:23 PM

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 · 01-06-2006, 02:46 PM

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 · 01-08-2006, 06:32 PM

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 · 01-11-2006, 05:26 PM

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 · 01-12-2006, 03:33 PM

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 · 01-13-2006, 02:19 PM

Can you post an example.

Texboy

darkwood · 01-14-2006, 10:58 AM

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

texboy · 01-17-2006, 10:35 AM

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** · 01-18-2006, 04:20 AM

Is this what you are after?

texboy · 01-18-2006, 11:41 AM

That's exactly what I needed.

Thanks again Reekorl.

Thanks to you too dardwood.

zoesadler · 10-17-2008, 10:44 AM

Just to say thanks for your thread!

It's really helped me out!!

01-03-2006, 11:45 AM	#1 (permalink)
texboy Registered User Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro	Excel Formula - Return Expired Dates 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! __________________ Asus A8N Sli Dx. AMD Opty 165@2400. XP-90 S/Fan. 1 Raptor HD 10Krpm, 250gb Seagate. 2 DVD+/-RW Sata Plextor Drive. 1 XFX 7800GT OCZ 2GB (2 x 1GB)DDR SDRAM Dual Ch.Plat. Logitech THX Z-2300 3PC Spk. Navigator Case 500W Antec Dell 2405FPW 24 in. LCD Monitor

01-06-2006, 02:46 PM	#3 (permalink)
texboy Registered User Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro	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. __________________ Asus A8N Sli Dx. AMD Opty 165@2400. XP-90 S/Fan. 1 Raptor HD 10Krpm, 250gb Seagate. 2 DVD+/-RW Sata Plextor Drive. 1 XFX 7800GT OCZ 2GB (2 x 1GB)DDR SDRAM Dual Ch.Plat. Logitech THX Z-2300 3PC Spk. Navigator Case 500W Antec Dell 2405FPW 24 in. LCD Monitor

01-11-2006, 05:26 PM	#5 (permalink)
texboy Registered User Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro	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 __________________ Asus A8N Sli Dx. AMD Opty 165@2400. XP-90 S/Fan. 1 Raptor HD 10Krpm, 250gb Seagate. 2 DVD+/-RW Sata Plextor Drive. 1 XFX 7800GT OCZ 2GB (2 x 1GB)DDR SDRAM Dual Ch.Plat. Logitech THX Z-2300 3PC Spk. Navigator Case 500W Antec Dell 2405FPW 24 in. LCD Monitor

01-13-2006, 02:19 PM	#7 (permalink)
texboy Registered User Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro	Can you post an example. Texboy __________________ Asus A8N Sli Dx. AMD Opty 165@2400. XP-90 S/Fan. 1 Raptor HD 10Krpm, 250gb Seagate. 2 DVD+/-RW Sata Plextor Drive. 1 XFX 7800GT OCZ 2GB (2 x 1GB)DDR SDRAM Dual Ch.Plat. Logitech THX Z-2300 3PC Spk. Navigator Case 500W Antec Dell 2405FPW 24 in. LCD Monitor

01-17-2006, 10:35 AM	#9 (permalink)
texboy Registered User Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro	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. __________________ Asus A8N Sli Dx. AMD Opty 165@2400. XP-90 S/Fan. 1 Raptor HD 10Krpm, 250gb Seagate. 2 DVD+/-RW Sata Plextor Drive. 1 XFX 7800GT OCZ 2GB (2 x 1GB)DDR SDRAM Dual Ch.Plat. Logitech THX Z-2300 3PC Spk. Navigator Case 500W Antec Dell 2405FPW 24 in. LCD Monitor

01-03-2006, 10:23 PM	#2 (permalink)
darkwood Registered User Join Date: May 2005 Location: Indianapolis, IN Posts: 77 OS: Windows XP Pro	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.

01-08-2006, 06:32 PM	#4 (permalink)
darkwood Registered User Join Date: May 2005 Location: Indianapolis, IN Posts: 77 OS: Windows XP Pro	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

01-12-2006, 03:33 PM	#6 (permalink)
darkwood Registered User Join Date: May 2005 Location: Indianapolis, IN Posts: 77 OS: Windows XP Pro	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.

01-18-2006, 11:41 AM	#11 (permalink)
texboy Registered User Join Date: Nov 2003 Posts: 136 OS: WinXP MCE Pro	That's exactly what I needed. Thanks again Reekorl. Thanks to you too dardwood. __________________ Asus A8N Sli Dx. AMD Opty 165@2400. XP-90 S/Fan. 1 Raptor HD 10Krpm, 250gb Seagate. 2 DVD+/-RW Sata Plextor Drive. 1 XFX 7800GT OCZ 2GB (2 x 1GB)DDR SDRAM Dual Ch.Plat. Logitech THX Z-2300 3PC Spk. Navigator Case 500W Antec Dell 2405FPW 24 in. LCD Monitor

10-17-2008, 10:44 AM	#12 (permalink)
zoesadler Registered User Join Date: Oct 2008 Posts: 1 OS: Windows XP	Re: Excel Formula - Return Expired Dates Just to say thanks for your thread! It's really helped me out!!