If, then

[hitman]

I'm looking for an If/THEN formula. If B2:B100 is less than todays date, then shade cell grey.

In the photo, B4, B5 should be grey, B6 should be grey at midnight, and B7 should have no fill.

Every time I think excel can't do something, it can do that thing better. What do you have?

 

[dreid1011]

Conditional Formatting, but why should B5 be gray? It's not less than today yet. Or did you mean less than or equal to?

Book1
	B
2	10/29/2024
3	10/30/2024
4	10/31/2024
5	11/1/2024
6	11/2/2024
7
8
9
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=AND(B2<TODAY(),B2<>"")	text	NO

 

[hitman]

Conditional Formatting, but why should B5 be gray? It's not less than today yet. Or did you mean less than or equal to?

Book1
	B
2	10/29/2024
3	10/30/2024
4	10/31/2024
5	11/1/2024
6	11/2/2024
7
8
9
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=AND(B2<TODAY(),B2<>"")	text	NO

Yeah, B5 should be grey at midnight - my mistake

 

[KRice]

Similar idea, but using NOW() to give the current date and time. It's not clear whether you want the current date (e.g., 30 Oct 2024) to be gray right now. Assuming the dates shown in your original post are midnight times, using NOW() as shown will turn a cell gray at midnight if the date shown in the cell is on or before midnight of the current date.

MrExcel_20241030.xlsx
	B
1
2
3	Date
4	10/29/2024
5	10/30/2024
6	10/31/2024
7	11/1/2024
8	11/2/2024
9
10
11
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B10	Expression	=AND(B4<=NOW(),B4<>"")	text	NO