Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel - Conditional formatting based on date?
Hi.. I'm trying to set up conditional formatting of cells within Excel based on the following rule...
If the date value entered is greater than 1 month from today's date then it is to show red, greater than 2 weeks but less than 1 month yellow and less than 2 weeks green.
I've been playing around with the rules but can't seem to get it to work?
Any ideas would be gratefully appreciated!
1 Answer
- garbo7441Lv 78 years agoFavorite Answer
As you speak of 'rules', I infer you are using Excel 2007 or later. If so, here are the three rules in rule order. This assumes the cells to format are in column B, beginning in B1.
=AND(AND(B1<>"",B1<TODAY() + 15))
Format for above = Green
=AND(B1>TODAY()+13, B1<TODAY()+31)
Format for above = Yellow
=B1>TODAY()+29
Format for above = Red
Since today is 9/4, dates from 9/4 to 9/18 will format Green. Dates from 9/19 to 10/4 will format Yellow. Dates from 10/4 on will format Red.
Edit: if you wish to actually use '1 month' instead of 30 days as above, you can use these rules:
=AND(B1>TODAY()+13, B1<DATE(YEAR(TODAY()), MONTH(TODAY()+1),DAY(TODAY()))
=B1>DATE(YEAR(TODAY()), MONTH(TODAY()+1),DAY(TODAY()))