Forum Discussion
Solved
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 i...
I'd use a slightly different setup.
You can then use the original formula in the conditional formatting rule:
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