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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies