Forum Discussion

Copper Contributor

Mar 23, 2022

Solved

Multiple Conditional Formatting based on MONTH()

Hi, I am trying to create a conditional formatting that will create unique color for each month on Column C. Column A is the DATE value in short format Column B sets the YEAR based on calculatio...

excel

HansVogelaar
Mar 23, 2022
MSegas

Try this formula in C2 and fill down.

=DATE(2000,IF(A2>=DATE(YEAR(A2),10,15),1,MONTH(A2)+1),1)

Apply a standard color scale.

HansVogelaar

MVP

Mar 23, 2022

MSegas

Try this formula in C2 and fill down.

=DATE(2000,IF(A2>=DATE(YEAR(A2),10,15),1,MONTH(A2)+1),1)

Apply a standard color scale.

MSegas
Copper Contributor
Mar 23, 2022
HansVogelaar

WOW ! what a nifty and simple solution. Thank you.!
Since my date column expands to 1900, I'll use 1900 instead of 2000.

I noticed that in the second part of the formula you removed the DATE YEAR repetition. Instead of ... , DATE(YEAR(A2),MONTH(A2)+1 your formula uses only MONTH(A2)+1

Your formula: =DATE(2000,IF(A2>=DATE(YEAR(A2),10,15),1,MONTH(A2)+1),1)
My formula: =IF(A2>=DATE(YEAR(A2),10,15),1,DATE(YEAR(A2),MONTH(A2)+1,DAY(1)))

Your formula is cleaner than mine.

Thanks again.
- HansVogelaar
  MVP
  Mar 23, 2022
  MSegas
  
  It's not really necessary to use 1900. Any year would do.
  - MSegas
    Copper Contributor
    Mar 23, 2022
    I see. The year is irrelevant because of the if statement