Conditional Formatting for Dates in Excel

New Contributor

Hi -


I'm trying to create a formula that will change the color of a cell based on a date in 2 and 4 week intervals.  I want to be able to enter in a date (which would start green) and then 2 weeks from that date entered turn yellow, then two weeks from that date, turn red.  What I don't want it to do is base the color changes on today's date, but on the date actually entered in the cell.  


The formula I have now changes the colors based on the current date (TODAY) but not on the date that was actually entered in the spreadsheet.  


Is there a way to change the colors based on the date entered in the column and not today's date?  Once the cycle is complete, I want to be able to enter in a new date, and have the same process repeat.

Basically I have a list of inventory and want to know where it is in the 4 week cycle so that it can be cleaned, and then re-entered back into inventory to go through the 4 week cycle yet again.



6 Replies

Hi @amykehring609


Is the attached file the sort of thing you need?


If not can you post a sample file?

@Wyn Hopkins Ok, so I've got it to do what I want it to do, but I want the rental date to start out as green, then the column that says 2 weeks to change to yellow when it reaches 2 weeks, and the column that says 4 weeks to change to red when it reaches 4 weeks.  Right now, only the current date changes colors.  If that makes sense I hope?




Hi Amy


Is the attached what you are after?



Hi Amy


Is the attached what you are after?



Actually it looks right, but when I change the Rental Date, the yellow and red colors change back to black.  



The formula is set to turn yellow if todays date > Rental Date + 14 days, so if your changing the rental date to something in the last 13 days it will turn back to black
Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies
Adding Display Zero as Dashes to Custom Excel Number Format
emmagreen in Excel on
1 Replies