Date Range Conditional format outside the Cells without data

[Abhinav Gupta]

Hi Everyone!

I have a problem and it works for a finite set of the date range within the current year but do not when extended to next year.

I have to apply conditional formatting on cells range(=$H$5:$AE$28) based upon date range in Column F & Column G.

Conditions:
Grey color the cells if the dates(row 4: Column H to AE) are less than the date in Column F
Grey color the cells if the dates (row 4: Column H to AE) are greater than the date in Column F

it works fine when I apply the format for current year 2018. But then I extend to next year, it fails.

Please if anyone can help me I would be highly obliged. 
Thanks
Abhinav

[#2 · Accepted solution]

Hi,

You compare separately months and years. For next year month condition id FALSE and YEAR is TRUE, result is FALSE.

I'd suggest to compare full dates like

=AND(H$4>EOMONTH($F5,-1),H$4<=$G5)

Please see attached

[#3]

Hi Sergei, I appreciate. Thanks so much for your help. That really make me understand the date compare concept.