Conditional formatting in Excel to highlight Dates Expiring

Question

I want to show a table of staff training with the completed dates and the the cells automatically then highlighting in red if overdue, orange due in next three months sand if due in the next year and green if up to date. How do I use conditional formatting to achieve this so I don't have to manually work it out every 3 months please

Answer 1

A simple way is to write the formulas into the sheet for testing purposes first.

C2: =A2<TODAY()

D2: =A2<EDATE(TODAY(),3)
E2: =A2<EDATE(TODAY(),12)

drag down. As you see if the formula returns TRUE the cell is formatted.

There is no need for a green condition, format all cells with green by default. Use the same formulas inside the conditional formatting.

Andreas.

Answer 2

Hi Caroline

It is not very clear what you want to achieve

Would you mind showing us a picture of your data and manually color formatting the cells according to your needs?

Answer 3

Below is an example: so for example Mental Health should be red as its overdue

Answer 4

Now I am completely confused.

Can you please explain which date is relevant to your statement and why the other dates are not overdue?

We have December 2021 and all your dates are in the past and therefore are overdue.

Andreas.

Answer 5

Sorry, I was in a rush this morning.

So for example in the table above:

1 - The GDPR course was last completed 15/11/2020 and should be renewed annually; so was overdue by 16/11/2021

As it is overdue I would like it to show in red.

2 - if a course is due to expire in the next 3 months; I would like it to show in Amber

3 - If a course is required to be re-taken within 6 months, Light brown

4 - If the course is not required to be renewed within the 6 months it is up to date and should show as green

Does this now make sense - Sorry!!

I don;t even know if its possible,

thanks for your help

Caroline