Conditional Formatting using TODAY()

[smarkel]

I am trying to create a formula that will turn a cell yellow if today's date falls between two dates and red if today's date falls later than another date. I'm not even sure if this formula exists.

Example: We want the cell to turn yellow if today's date is between 8/1/2023 and 9/30/2023. We want the cell to turn red if today's date falls after 10/1/2023. The purpose is to alert us when we are due/overdue complete a periodic inspection on equipment.

 

[awoohaw]

Try this:

mr excel questions 5.xlsm
	C	D	E	F
2	BDate	EDate	TodayDate
3	08/01/2023	09/30/2023	07/31/2023	FALSE
4	08/01/2023	09/30/2023	08/01/2023	FALSE
5	08/01/2023	09/30/2023	09/15/2023	FALSE
6	08/01/2023	09/30/2023	09/30/2023	FALSE
7	08/01/2023	09/30/2023	10/01/2023	TRUE
Sheet4

Cell Formulas
Range	Formula
F3:F7	F3	=E3>=DATE(2023,10,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E7	Expression	=E3>=DATE(2023,10,1)	text	NO
E3:E7	Expression	=AND(E3>=C3,D3<=D3)	text	NO

 

[awoohaw]

Instead of using E3 in the formula you would use the TODAY() function.

=TODAY()>=DATE(2023,10,1)

=AND(TODAY()>=C3,TODAY()<=D3)