Cond. format empty cell based on column header and dates in other cells

[haruspication]

Hello excel enthusiasts,

I want to use a conditional formatting formula to highlight empty cells that fall between the first and last harvest period for each crop based on the column header date. The cells that should be highlighted say "fill". I've tried if/and formulas using relative cells, but haven't gotten it to work. Any suggestions?

For example, I applied conditional formatting to cell D2:J2 with the formula below, but nothing happened: =AND(D$1>=$B2,D$1<=$C2).


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Crop[/TD]
[TD]First Harvest[/TD]
[TD]Last Harvest[/TD]
[TD]10-Apr[/TD]
[TD]17-Apr[/TD]
[TD]24-Apr[/TD]
[TD]1-May[/TD]
[TD]8-May[/TD]
[TD]15-May[/TD]
[TD]22-May[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Asparagus[/TD]
[TD]17-Apr[/TD]
[TD]8-May[/TD]
[TD][/TD]
[TD]fill[/TD]
[TD]fill[/TD]
[TD]fill[/TD]
[TD]fill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Turnips[/TD]
[TD]1-May[/TD]
[TD]15-May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fill[/TD]
[TD]fill[/TD]
[TD]fill[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[haruspication]

I figured it out! In my formula, excel did not convert the column header dates into serial numbers, however excel did convert the dates in column B & C into serial numbers. So, I adjusted the formula so that the column header dates became serial numbers and the formula worked perfectly.

AND(datevalue(D$1)>=$B2,datevalue(D$1)<=$C2)