1. ## Future Date (2007)

I have a spreadsheet that records expiratory dates of tickets, ie expires on 1/6/2009.

I'm trying to work out a formula that give the following results, "Current", "One Month Left" and "Expired".

I use this formula =IF(I2="","",IF(I2>=TODAY(),"Current","Expired")) which is OK, but can't figure out how to calculate the "One Month Left" component.

Any thoughts?

2. ## Re: Future Date (2007)

Hi Dean

Presuming that 2007 does the same in conditional formatting would the reponses to my post starting here <post#=743,542>post 743,542</post#> help at all?

Cheers

3. ## Re: Future Date (2007)

Steve,

Thanks for the reply, as I understand your previous post, the conditional formatting will format the cell containing the date to indicate currency, etc, however I need the related cell to return the text as detailed in my post. I use conditional formatting to highlight the different events in the related cell.

I hope that makes sense

4. ## Re: Future Date (2007)

Try

=IF(I2="","",IF(I2<TODAY(),"Expired",IF(I2>DATE(YE AR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())),"Curren t","One Month Left")))

5. ## Re: Future Date (2007)

Hans - You have done it again - just what I need.

Cheers!

6. ## Re: Future Date (2007)

Hi Dean,

Here's another way:
=IF(ISERROR(DATEDIF(TODAY(),I2,"M")),DATEDIF(I2,TO DAY(),"M")-1,DATEDIF(TODAY(),I2,"M"))
with a custom cell format:
"Current";"Expired";"One Month Left"

FWIW, the formula returns numbers, which might be useful if you want to use the month remaining/expired values elsewhere.

7. ## Re: Future Date (2007)

Thanks Paul,

May be able to use this someplace. Not sure what you mean about the custom cell format?

By the way , what does FWIW stand for?

8. ## Re: Future Date (2007)

Hi Dean,

For What It's Worth, the custom cell format is applied via Format|Cells|Number > Custom, then inputting the desired format into the 'Type' box.

9. ## Re: Future Date (2007)

Macropod's formula returns a number, for example -1 or 0 or 3.
Select the cell with the formula.
Select Format | Cells...
In the Number tab, select the Custom category.
Enter macropod's custom format
<code>
"Current";"Expired";"One month left"
</code>
in the Type box and click OK.
"Current" will be used if the number is positive, "Expired" if it is negative, and "One month left" if it is zero.

(Macropod's formula doesn't return the correct result for me if the date is more than one month in the past, BTW = by the way)

FWIW = for what it's worth.

10. ## Re: Future Date (2007)

Hi Hans,
<hr>formula doesn't return the correct result for me if the date is more than one month in the past<hr>
Well spotted! This can be fixed with:
=IF(ISERROR(DATEDIF(TODAY(),I2,"M")),-DATEDIF(I2,TODAY(),"M")-1,DATEDIF(TODAY(),I2,"M"))

11. ## Re: Future Date (2007)

Paul/Hans

TVM (thanks very much) - for your help.

Will give it a go

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•