Forum Discussion

Brass Contributor

Aug 02, 2023

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...

excel

Formulas and Functions

office 365

HansVogelaar
Aug 02, 2023
clh_1496

I'd use a slightly different setup.

You can then use the original formula in the conditional formatting rule:

HansVogelaar

MVP

Aug 02, 2023

clh_1496

I'd use a slightly different setup.

You can then use the original formula in the conditional formatting rule:

clh_1496
Brass Contributor
Aug 02, 2023
HansVogelaar
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?
- SergeiBaklan
  Diamond Contributor
  Aug 02, 2023
  clh_1496
  
  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)
  cf years quarters.xlsx15 KB
- HansVogelaar
  MVP
  Aug 02, 2023
  clh_1496
  
  Yeah, you must mention the year first, then the quarter: 2023Q1 or 2023 Q1 or 2023-Q1.
  
  Q1 2023 won't work because Q4 2023 is greater than Q1 2024 when you compare the values as text.
  - applepie
    Copper Contributor
    Jan 13, 2025
    is it possible to have the user key in with format "YYYY-MM-DD" and still have the quarterly visualisation working ?