Excel Conditional Formatting with Dates – have you used this feature before in your spreadsheets?

Excel Conditional Formatting with Dates – taking conditional formatting a step further in your spreadsheets

Excel Conditional Formatting with Dates: Excel iconThis week’s hint and tip is on Excel conditional formatting with Dates. Conditional formatting is a very useful feature in Excel and being able to use it alongside dates can be very useful. This is covered in our Intermediate Excel training course but we also decided to do a hint and tip on it. We are going to go through it now below.


Conditional Formatting with Dates

In this blog we shall look at a real-life example of how a company may manage using colour appliance tests which are due for re-testing. The background to this blog and video was born out of a recent question at a client site who asked about managing their testing procedure better.

The worksheet is attached for you to look at (further down in the post) but here is a screen print of it too.

Conditional Formatting with Dates: Spreadsheet screenshot

Effectively the first test took place on the date in column B. Note also today’s date was 16/05/2022 and 6 months after the first date there is a date in column D.

Effectively the colour chart at the bottom of the sheet explains what colour means what. To compose this we had to use Conditional Formatting using formulas.


Red Rule (Overdue)

Conditional Formatting with Dates: Red rule screenshot

If today’s date is greater than the date of re-test its late!


Orange Rule (Due within the month)

Conditional Formatting with Dates: Orange rule screenshot

If the difference is only up to 30 days, then its due within the month. To compose this we used an AND function and in this function both conditions need to be true for it to turn orange.


Green Rule (Due between month 2 and month 6)

Conditional Formatting with Dates: Green rule screenshot

Here again an AND function is used and the difference between due test date and today is between 30 and 180 days i.e. month 2 and month 6 to turn green.

We cover other elements of conditional formatting in our Excel series of courses in both our onsite and virtual offerings.


Worked example video

The video below shows you how you can use this feature in the spreadsheet attached here. (clicking here will download a copy to your computer so you can try it out!). We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more and then try it out on your own computer!

We hope you have enjoyed this hint and tip on Excel conditional formatting with dates. Why not take a look at our previous video hint and tip on the Excel TEXTJOIN function?