Forum Discussion
Conditional Formatting for Quarters & Years
Hi all,
I had a spreadsheet that had months along the top and used conditional formatting to colour in the cells between the start and finish date using the formula: =AND($C5<=E$2,$D5>=E$2) and it worked perfectly.
I've now been asked to replace the months with Quarters and so based on this, I've tried to add in 2 further columns with the Start & finish year, and then the Quarter Start and Finish Year in the hope that I could just change the AND formula to include all 4 conditions =AND($C$13<=G$3,$D$13>=G$3,$E$13<=G$2,$F$13>=G$2)
however I can't seem to get this to work? Is there any way to get the conditional formatting to colour in all cells that are within both the start and finish years as well as the start Quarter start & Quarter finish?
Thanks in advance!
I'd use a slightly different setup.
You can then use the original formula in the conditional formatting rule:
13 Replies
Rule formula could be
=( (LOOKUP(9999,$G$3:G$3)*10+G$2) >= ($C13*10+$E13))*( (LOOKUP(9999,$G$3:G$3)*10+G$2) <= ($D13*10+$F13))which gives
Hi Sergei, this works great - the only thing is that it doesn't work for the milestones, which have the same start and finish dates (table below shows this). I want it to look like the blue/pink/yellow vertical lines below?
2026 2026 4 4 2025 2025 4 4 2026 2026 1 1 2023 2023 4 4 Few questions to clarify
- which layout of suggested you finally prefer?
- milestone means same start/finish date, will you keep dates, or same start/finish quarter works?
- what is the logic for different colours for the milestones?
- shall we highlight headers as well for the milestones?
Thanks
I'd use a slightly different setup.
You can then use the original formula in the conditional formatting rule:
For some reason, it's not reading the dates correctly. It reads the single date milestones (pink/green/yellow/blue vertical columns) but not the horizontal ones. Do you know if I need to change Q1 2023 to 2023Q1 like you've done or is there a different reason?
With similar layout
rule formula could be
=( LOOKUP(9999,$G$3:G$3)&G$2 >= $C12)*( LOOKUP(9999,$G$3:G$3)&G$2 <= $D12)
