Conditional formatting for expired dates and for 2 months before expiry date of course

[mipasodu]

Hi, I have a sheet detailing each employees list of trainings that they have taken, some courses run for 12 months, 24 months, 36 months or only need to be taken once. I have validity period of each course, title of course, employee name.

I want conditional formatting to highlight any course that has expired. I would also like to highlight any course that will expire within 2 months. Course that are only taken once don't need to highlight.

	VALIDITY PERIOD (Months) >	12	36	Once	24
Team	Name	Information	Safety	Security	Conduct
5	Bill Gates	26.10.2023	12.5.2022	26.10.2023
5	Bob Doorpost	7.9.2022		28.3.2022	18.8.2021

 

[etaf]

is that formatted as a date - or is it text - cant tell from the post
also what does

Course that are only taken once don't need to highlight.

Mean ???

if you right click on a date and format
change to general - do you now see a number

because of the format and justification , it looks like text
need to be dates

all your dates are expired or will do within 2 months
i added a 2024 date

used this formula in conditional formatting
=AND(C3<>"",C3<DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))

Book7
	A	B	C	D	E	F
1	VALIDITY PERIOD (Months) >	12	36	Once	24
2	Team	Name	Information	Safety	Security	Conduct
3	5	Bill Gates	10/26/23	5/12/22	10/26/23
4	5	Bob Doorpost	9/7/22	2/1/24	3/28/22	8/18/21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:F4	Expression	=AND(C3<>"",C3<DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))	text	YES

 

[mipasodu]

Hi, they are all formatted as dates.

In B1:F1 of my example the validity of 36 months so it should show as expired on 26th October 2026.

I would like the CF to take the "course completion date" in C3:F4, check the period of validity in cells B1:F1 and then deduct 2 months and mark the cell in red

 

[mipasodu]

is that formatted as a date - or is it text - cant tell from the post
also what does

Mean ???

if you right click on a date and format
change to general - do you now see a number

because of the format and justification , it looks like text
need to be dates

all your dates are expired or will do within 2 months
i added a 2024 date

used this formula in conditional formatting
=AND(C3<>"",C3<DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))

Book7
	A	B	C	D	E	F
1	VALIDITY PERIOD (Months) >	12	36	Once	24
2	Team	Name	Information	Safety	Security	Conduct
3	5	Bill Gates	10/26/23	5/12/22	10/26/23
4	5	Bob Doorpost	9/7/22	2/1/24	3/28/22	8/18/21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:F4	Expression	=AND(C3<>"",C3<DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))	text	YES

in my example the validity of the course is given in months, 12, 24, 36 or "Once" (which means that it never expires, or only needs to be taken once.