Conditional Formatting Based on Date Column

[psycoperl]

Good Morning and Happy (belated) New Year Team Mr. Excel.

I am trying to figure out how to do conditional formatting on my spreadsheet. (Excerpt below in a XL2BB format) My goal is to grey-out (for lack of a better term) any rows where the date (Column A) is in the future. The table includes future expected items, however they are not "official" until the date arrives and is earned.

While I am able to use the Conditional Formatting built in option Highlight Cell Rules > Greater Than on Column A and setting it to =NOW() and it colored Column A as expected I cannot get it to format the additional columns even when changing the "applies to" value to $A$2:$I$2000, what it did was color everyone except the cells in A that are less than today's date. I had also tried to use a formula in Conditional Formatting of =$A2>NOW() and applying it from $A$2:$I$2000 but it does not work.

Any assistance you can provide would be greatly appreciated.

Leave_Use_Plan_FY2021.xlsx
	A	B	C	D	E	F	G	H	I
1	ANNUAL LEAVE Date	Leave Type	NOTE	D	H	M	LEAVE MINUTES	AL BAL	FY DAYS MUST USE
2	Monday, August 31, 2020	AL	BALANCE	69	6	55	29395	AL BAL: 69D 06H 55M	MUST USE: 24D 06H 55M
3	Wednesday, September 2, 2020	AL	FY20 SPEND	-1	0	0	28975	AL BAL: 68D 06H 55M	MUST USE: 23D 06H 55M
4	Thursday, September 3, 2020	AL	FY20 SPEND	-1	0	0	28555	AL BAL: 67D 06H 55M	MUST USE: 22D 06H 55M
5	Friday, September 4, 2020	AL	FY20 SPEND	-1	0	0	28135	AL BAL: 66D 06H 55M	MUST USE: 21D 06H 55M
6	Tuesday, September 8, 2020	AL	FY20 SPEND	-1	0	0	27715	AL BAL: 65D 06H 55M	MUST USE: 20D 06H 55M
7	Wednesday, September 9, 2020	AL	FY20 SPEND	-1	0	0	27295	AL BAL: 64D 06H 55M	MUST USE: 19D 06H 55M
8	Thursday, September 10, 2020	AL	FY20 SPEND	-1	0	0	26875	AL BAL: 63D 06H 55M	MUST USE: 18D 06H 55M
9	Friday, September 11, 2020	AL	FY20 SPEND	-1	0	0	26455	AL BAL: 62D 06H 55M	MUST USE: 17D 06H 55M
10	Wednesday, September 30, 2020	AL	Increment +	2	0	35	27330	AL BAL: 65D 00H 30M	MUST USE: 20D 00H 30M
11	Tuesday, October 20, 2020	AL	FY20 SPEND	-1	0	0	26910	AL BAL: 64D 00H 30M	MUST USE: 19D 00H 30M
12	Wednesday, October 21, 2020	AL	FY20 SPEND	-1	0	0	26490	AL BAL: 63D 00H 30M	MUST USE: 18D 00H 30M
13	Thursday, October 22, 2020	AL	FY20 SPEND	-1	0	0	26070	AL BAL: 62D 00H 30M	MUST USE: 17D 00H 30M
14	Friday, October 23, 2020	AL	FY20 SPEND	-1	0	0	25650	AL BAL: 61D 00H 30M	MUST USE: 16D 00H 30M
15	Monday, October 26, 2020	AL	FY20 SPEND	-1	0	0	25230	AL BAL: 60D 00H 30M	MUST USE: 15D 00H 30M
16	Saturday, October 31, 2020	AL	Increment +	2	0	35	26105	AL BAL: 62D 01H 05M	MUST USE: 17D 01H 05M
17	Tuesday, November 10, 2020	AL	FY20 SPEND	-1	0	0	25685	AL BAL: 61D 01H 05M	MUST USE: 16D 01H 05M
18	Thursday, November 12, 2020	AL	FY20 SPEND	-1	0	0	25265	AL BAL: 60D 01H 05M	MUST USE: 15D 01H 05M
19	Tuesday, November 17, 2020	AL	FY20 SPEND	-1	0	0	24845	AL BAL: 59D 01H 05M	MUST USE: 14D 01H 05M
20	Thursday, November 19, 2020	AL	FY20 SPEND	-1	0	0	24425	AL BAL: 58D 01H 05M	MUST USE: 13D 01H 05M
21	Wednesday, November 25, 2020	AL	FY20 SPEND	-1	0	0	24005	AL BAL: 57D 01H 05M	MUST USE: 12D 01H 05M
22	Monday, November 30, 2020	AL	Increment +	2	0	35	24880	AL BAL: 59D 01H 40M	MUST USE: 14D 01H 40M
23	Thursday, December 10, 2020	AL	FY20 SPEND	-1	0	0	24460	AL BAL: 58D 01H 40M	MUST USE: 13D 01H 40M
24	Friday, December 11, 2020	AL	FY20 SPEND	-1	0	0	24040	AL BAL: 57D 01H 40M	MUST USE: 12D 01H 40M
25	Friday, December 18, 2020	AL	fY20 SPEND	-1	0	0	23620	AL BAL: 56D 01H 40M	MUST USE: 11D 01H 40M
26	Tuesday, December 22, 2020	AL	FY20 SPEND	-1	0	0	23200	AL BAL: 55D 01H 40M	MUST USE: 10D 01H 40M
27	Wednesday, December 23, 2020	AL	FY20 SPEND	-1	0	0	22780	AL BAL: 54D 01H 40M	MUST USE: 09D 01H 40M
28	Monday, December 28, 2020	AL	FY20 SPEND	-1	0	0	22360	AL BAL: 53D 01H 40M	MUST USE: 08D 01H 40M
29	Tuesday, December 29, 2020	AL	FY20 SPEND	-1	0	0	21940	AL BAL: 52D 01H 40M	MUST USE: 07D 01H 40M
30	Wednesday, December 30, 2020	AL	FY20 SPEND	-1	0	0	21520	AL BAL: 51D 01H 40M	MUST USE: 06D 01H 40M
31	Thursday, December 31, 2020	AL	Increment +	2	0	35	22395	AL BAL: 53D 02H 15M	MUST USE: 08D 02H 15M
32	Sunday, January 31, 2021	AL	Increment +	2	0	35	23270	AL BAL: 55D 02H 50M	MUST USE: 10D 02H 50M
33	Sunday, February 28, 2021	AL	Increment +	2	0	35	24145	AL BAL: 57D 03H 25M	MUST USE: 12D 03H 25M
ANNUAL LEAVE PLAN

 

[Joe4]

I had also tried to use a formula in Conditional Formatting of =$A2>NOW() and applying it from $A$2:$I$2000 but it does not work.

This should definitely work (I do stuff like this all the time).
If it does not, check for the following:
1. Your dates in column A are really dates and not text (can be confirmed with a formula like =ISNUMBER(A2) )
2. There are no other Conditional Formatting conditions being applied to the same range that might be interfering with it
3. There is no VBA code that might be interfering with it
4. You didn't forget to actually select the desired formatting (sounds silly, but I have actually forgotten this step once or twice!)

To verify it should, try setting up the same scenario on a new blank workbook, and see if you can get that to work.

 

[psycoperl]

1. Your dates in column A are really dates and not text (can be confirmed with a formula like =ISNUMBER(A2) )

Yes they are all date

It turned out that when I saved the condition, it put the formula in Quotes. Once I removed the quotes it actually worked as expected.

Thank you for confirming that I was on the right track.

Best

 

[Joe4]

It turned out that when I saved the condition, it put the formula in Quotes. Once I removed the quotes it actually worked as expected.

Ah yes, that would treat the whole thing as Text (it is literally checking for the text "$A2>NOW()" in the cells).
Glad you got it working!

 

[psycoperl]

At least you guidance proved that I was using the right logic. Thank you!

 

[Joe4]

At least you guidance proved that I was using the right logic. Thank you!

You are welcome.