Conditional Format Dates in Excel

If you want to create a system in your spreadsheet to flag dates automatically when they become due you can do this by using conditional formatting. There is a built in option to do this which can be found under Home, Conditional Formatting, Highlight Cells Rules, Date Occurring.... but when you need to set several criteria you will need to use a formula. Here's an example:-

In my spreadsheet I have a list of vehicles whose registrations are due on certain dates. I need to highlight these dates automatically using the following criteria:-

Green - Not due (for at least another 90 days)
Amber - Due within 90 Days
Blue - Due within 60 Days
Red - Due within 30 Days

Notice the order of the 4 criteria, this is important as Excel executes the rules in order. We will use the above criteria to conditionally format the following spreadsheet.

conditional format dates within 30, 60, 90 days

  1. Select cell D2
  2. Click Conditional Formatting
  3. New Rule
  4. Use a formula to determine which cells to format
  5. Enter the first rule =D2-TODAY()<30
  6. Click Format and set the fill colour to red
  7. Repeat this process for the other rules on the same cell
  8. Click and drag the bottom right corner of the cell to fill the formatting down and choose Fill Formatting only to copy the rules to the other cells as needed


Thanks for Reading

If you want to read reviews from satisfied clients go online at Google Reviews.

Thanks for reading and don't forget to get in touch if you need any training or support.


Anita Lund