Excel, calculating expiry dates & 3 answers/actions

j-lo · 02-29-2008, 03:23 PM

I have been unsuccessful in finding a formula that can track the status of an expiry date (within relation to the current date) and return one of the following 3 possible status results/messages:
1) "Expiring within next 30 days" (or maybe "Expiring in X days", where the "X" keeps counting down the days)
2) "OK"
3) "Expired"
Using an IF formula, I have been able to make the formula work returning 2 of my 3 responses, but have been unable to successfully add/return the 3rd status option.
Any help would be greatly appreciated!!

**Glaswegian** · 02-29-2008, 03:40 PM

Hi and welcome to TSF.

Let's see if I've understood

=IF(B2=NOW(),"OK",IF(B2<NOW(),"Expired",IF(B2<NOW()+30,"Expiring within next 30 days","")))

j-lo · 03-03-2008, 01:38 PM

Hi Glaswegian,
Thanks for the welcome, and the formula. I totally forgot about the "Now()" function and was trying to do it as a formula, so thanks for the reminder of a simpler formula.
FYI - the formula you provided was only giving me the "Expired" and "Expiring within next 30 days" responses so I tinkered with it and changed the "B2=NOW()" to "B2=NOW()>31" and am finally getting all three responses. It is working like a charm - THANKS!

**Glaswegian** · 03-03-2008, 02:05 PM

Ah - gotcha - sorry about that. I used to be able to count...

Glad you got it sorted.

02-29-2008, 03:23 PM	#1 (permalink)
j-lo Registered User Join Date: Feb 2008 Posts: 2 OS: Windows NT	Excel, calculating expiry dates & 3 answers/actions I have been unsuccessful in finding a formula that can track the status of an expiry date (within relation to the current date) and return one of the following 3 possible status results/messages: 1) "Expiring within next 30 days" (or maybe "Expiring in X days", where the "X" keeps counting down the days) 2) "OK" 3) "Expired" Using an IF formula, I have been able to make the formula work returning 2 of my 3 responses, but have been unable to successfully add/return the 3rd status option. Any help would be greatly appreciated!!

02-29-2008, 03:40 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,529 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel, calculating expiry dates & 3 answers/actions Hi and welcome to TSF. Let's see if I've understood =IF(B2=NOW(),"OK",IF(B2<NOW(),"Expired",IF(B2<NOW()+30,"Expiring within next 30 days",""))) __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

03-03-2008, 01:38 PM	#3 (permalink)
j-lo Registered User Join Date: Feb 2008 Posts: 2 OS: Windows NT	Re: Excel, calculating expiry dates & 3 answers/actions Hi Glaswegian, Thanks for the welcome, and the formula. I totally forgot about the "Now()" function and was trying to do it as a formula, so thanks for the reminder of a simpler formula. FYI - the formula you provided was only giving me the "Expired" and "Expiring within next 30 days" responses so I tinkered with it and changed the "B2=NOW()" to "B2=NOW()>31" and am finally getting all three responses. It is working like a charm - THANKS!

03-03-2008, 02:05 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,529 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel, calculating expiry dates & 3 answers/actions Ah - gotcha - sorry about that. I used to be able to count... Glad you got it sorted. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner