Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi, I have a conditional format formula =today()>A1, this works fine so the cell turn red when the date in A1 this greater than today. However, i would like a formula that will change the color when the date in A1 is one month before today()

    Any suggestions?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One way to calculate one month before today would be:
    =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks for the suggestion, Just not sure how this would work without a cell reference to a date in a specific cell, ie A1?

    Any thoughts



    [quote name='mbarron' post='795076' date='27-Sep-2009 01:24']One way to calculate one month before today would be:
    =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))[/quote]

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured you would know what you wanted to compare to the one month back date. I showed how to determine that date.

    the date is exactly one month ago:
    =A1 = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    date is prior to one month ago:
    =A1< DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    date after one month ago:
    =A1>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    date is anything except one month ago:
    =A1<>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks - I've got it - was thinking that there would be a cell reference in bedded there someplace

    Thanks


    [quote name='mbarron' post='795082' date='27-Sep-2009 03:57']I figured you would know what you wanted to compare to the one month back date. I showed how to determine that date.

    the date is exactly one month ago:
    =A1 = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    date is prior to one month ago:
    =A1< DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    date after one month ago:
    =A1>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    date is anything except one month ago:
    =A1<>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))[/quote]

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks for the suggestions. However, not sure if I'm doing something wrong or if there is another way do to this.

    Basically what I need to try to sort out is - say A1 has a date of 1/10/09, on 1/09/09 it turns (say) Green and stays green, then when A1 is >= today, it turns red.

    I have been using
    =today()>=A1 (red)
    and in a new rule
    =A1< DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) (Green)

    Any other thoughts/suggestions

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See attached workbook.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='verada' post='795419' date='29-Sep-2009 07:35']Thanks for the suggestions. However, not sure if I'm doing something wrong or if there is another way do to this.

    Basically what I need to try to sort out is - say A1 has a date of 1/10/09, on 1/09/09 it turns (say) Green and stays green, then when A1 is >= today, it turns red.

    I have been using
    =today()>=A1 (red)
    and in a new rule
    =A1< DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) (Green)

    Any other thoughts/suggestions[/quote]
    hI dEAN

    The attached is an example of a training workbook that was developed with the help of the other loungers. In this example you can see in column D of each page that there are various expiry dates, for example our security training is every 2 years, the ADR is every 5 years and first aid / fire training is 3 years, you will also need to look at the conditional formatting.

    I hope this helps
    Attached Files Attached Files
    Cheers

    Steve

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

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

    Both samples are very helpful

    Cheers

    Looking at both examples, I notice that if the date in the cell with the conditional formatting is deleted the cell turns red and will only return to the "normal color" if a value is entered into the cell (ie a space or character) . Any ideas how this could be changed so it stays at the "normal" cell color if the date is deleted or the cell is blank

    [quote name='stevehocking' post='795473' date='29-Sep-2009 14:05']hI dEAN

    The attached is an example of a training workbook that was developed with the help of the other loungers. In this example you can see in column D of each page that there are various expiry dates, for example our security training is every 2 years, the ADR is every 5 years and first aid / fire training is 3 years, you will also need to look at the conditional formatting.

    I hope this helps[/quote]

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='verada' post='795607' date='30-Sep-2009 07:45']Thanks Hans and Steve

    Both samples are very helpful

    Cheers

    Looking at both examples, I notice that if the date in the cell with the conditional formatting is deleted the cell turns red and will only return to the "normal color" if a value is entered into the cell (ie a space or character) . Any ideas how this could be changed so it stays at the "normal" cell color if the date is deleted or the cell is blank[/quote]
    You can add another condition

    [attachment=85759:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  11. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Prasad - thanks for the help, will include the condition


    Cheers
    [quote name='prasad' post='795610' date='30-Sep-2009 05:54']You can add another condition

    [attachment=85759:untitled.JPG][/quote]

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you refer the demo file offered by Hans, there is no need for additional condition. It will keep the formatting to normal if cell is blank.
    Regards
    Prasad

  13. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Ok thanks - will revisit the demo file

    Cheers

    [quote name='prasad' post='795616' date='30-Sep-2009 06:29']If you refer the demo file offered by Hans, there is no need for additional condition. It will keep the formatting to normal if cell is blank.[/quote]

Posting Permissions

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