Jun 11 2018
03:20 PM
- last edited on
Jul 31 2018
08:26 AM
by
TechCommunityAP
Jun 11 2018
03:20 PM
- last edited on
Jul 31 2018
08:26 AM
by
TechCommunityAP
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
Jun 12 2018 02:07 AM
SolutionHi,
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
Jun 12 2018 02:27 AM
Hi Sergei, I appreciate. Thanks so much for your help. That really make me understand the date compare concept.
Jun 12 2018 02:07 AM
SolutionHi,
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