Forum Discussion
Date Range Conditional format outside the Cells without data
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
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
2 Replies
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
Hi Sergei, I appreciate. Thanks so much for your help. That really make me understand the date compare concept.
