Change the background color of a cell in Excel based on the date in the cell

This was a special request by a Manager. He had a list of records with dates that the record was opened and wanted to easily see:

• Files open 0-60 days = Green Background
• Files open 61-120 days = Yellow Background
• Files open more than 120 days = Red Background

In Microsoft Excel, you can accomplish this using a very powerful tool called Conditional Formatting (CF).
CF is found under HOME–>Styles Group on the Ribbon and while it has alot of commonly used formatting built-in, the formatting described above doesn’t match any existing option.
So we do some formulas:

1. Goto CF–>New Rule
2. Click on “Use a formula to determine which cells to format“
3. Enter the formula in the box provided.
   In my case, the first formula was the RED Background: =IF(DAYS360(F1,TODAY())>120, TRUE, FALSE)
   F1 in this example is the first cell of the column that has the data I want to examine.
4. Make sure that under “Applies to” you have the correct range of cells. If you want to select the entire “F” column, then that would look like: =$F:$F
   You can use the chooser (chart icon) to select the range using your mouse if you wish..
   Click Format button and select the style you want applied to cells which match this Rule. Click OK.
5. Create your other Rules:
   =IF(DAYS360(F1,TODAY())<121, TRUE, FALSE)
   =IF(DAYS360(F1,TODAY())<61, TRUE, FALSE)
   =IF(ISBLANK(F1)=TRUE, TRUE, FALSE)
6. Note the last rule I created is for blank cells. It seems that if a Date/Time cell is blank, then its DAYS360 function results in 40824. So you need to setup that rule to look for blanks and format them White background so they aren’t changed by the other Rules. So that it gets applied in the right order, place this rule at the top of your Rules list. Here is a picture of my Rules setup for reference: