Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    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. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Future Date (2007)

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

    Cheers!

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    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. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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"))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    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
  •