Conditional formatting for training date reminders

[Tom Carron]

I have a list of dates that training was completed (so these are dates in the past), and this particular training should be refreshed every 12 months. What I'd like to know is can I use conditional formatting to change the colour of the date (to orange for example) 11 months after the training date to remind that it is coming up to refresh time, and then to red 12 months after the training date to show that a refresh is overdue?

 

[arthurbr]

Try the following applied to the range starting in C2

For red :

= TODAY()>(year($c2)+1,month($c2),day($c2)

For orange

=TODAY()>edate($c2,11)

 

[Tom Carron]

Try the following applied to the range starting in C2

For red :

= TODAY()>(year($c2)+1,month($c2),day($c2)

For orange

=TODAY()>edate($c2,11)

Thanks arthurbr, the orange one works great, but the red it won't accept. Could you just check the formula as I'm not great on resolving formula issues. I've tried adding an extra closed bracket etc. but can't fix it.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try these

Tom Carron.xlsm
	C
1	ARG Training
2
3	9/06/2020
4
5
6	5/12/2019
7
8	30/12/2019
9	31/01/2020
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C9	Expression	=AND(C2<>"",TODAY()>EDATE(C2,12))	text	NO
C2:C9	Expression	=AND(C2<>"",TODAY()>EDATE(C2,11))	text	NO

 

[Tom Carron]

Welcome to the MrExcel board!

Try these

Tom Carron.xlsm
	C
1	ARG Training
2
3	9/06/2020
4
5
6	5/12/2019
7
8	30/12/2019
9	31/01/2020
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C9	Expression	=AND(C2<>"",TODAY()>EDATE(C2,12))	text	NO
C2:C9	Expression	=AND(C2<>"",TODAY()>EDATE(C2,11))	text	NO

Thanks Pete, that sorted it. Appreciate your help.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.