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).

	A	B	C	D	E	F	G	H	I	J
1	Crop	First Harvest	Last Harvest	10-Apr	17-Apr	24-Apr	1-May	8-May	15-May	22-May
2	Asparagus	17-Apr	8-May		fill	fill	fill	fill
3	Turnips	1-May	15-May				fill	fill	fill

<tbody>
</tbody>

 

[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)