Excel - fill cell when the date in cell falls within a range of dates

1
votes

I have a spreadsheet that has a single column ('I') with dates in it. I would like the cells in this column to change colour depending on the Quarter that the date falls in.

Dates falling in the range;

  • DEC-FEB = RED
  • MAR-MAY = GREEN
  • JUN-AUG = BLUE
  • SEPT-NOV = YELLOW

All dates are formatted as dates (dd/mm/yyyy)

I've tried a range of IF/AND statements but keep getting hung up on the year, and to be honest, I've forgotten most of what I've tried. Sorry for the lack of info, i tried posting a screenshot but got knocked back.

1
exceldateconditional-formatting

1 Answers

1
votes

Try this:

=OR(MONTH(I2)=12,MONTH(I2)=1,MONTH(I2)=2)
=OR(MONTH(I2)=3,MONTH(I2)=4,MONTH(I2)=5)
=OR(MONTH(I2)=6,MONTH(I2)=7,MONTH(I2)=8)
=OR(MONTH(I2)=9,MONTH(I2)=10,MONTH(I2)=11)

Note, I'm assuming you have a header row, hence these formula reference I2.