1. 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. One way to calculate one month before today would be:
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

3. 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. 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. 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. 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. See attached workbook.

8. [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

9. Thanks Hans and Steve

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. [quote name='verada' post='795607' date='30-Sep-2009 07:45']Thanks Hans and Steve

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]

[attachment=85759:untitled.JPG]

11. Prasad - thanks for the help, will include the condition

Cheers

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

12. 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.

13. 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
•