One of the columns in my Calc spreadsheet is the date of each transaction, with the number type set to ‘date’. I want these date cells to be grouped by having a background colour based on the month value - so all cells containing dates in January have one colour, those containing dates in February another colour, etc.
I thought I could use conditional formatting with the ‘Color Scale (2 Entries)’ option and a formula to get this to work but after wasting about an hour on this I haven’t been able to get it to work. I’m not sure I understand how this is supposed to work. I’ve gone through the help documentation but that didn’t provide any clues.
Using the ‘Conditional Formatting’ dialog for the range A3:A1048576 I’ve selected ‘All Cells’ and ‘Color Scale (2 Entries)’. What formula do I put into the ‘Formula’ boxes for each colour to achieve this? I’ve tried ‘=(MONTH(A3)=1’ and ‘=(MONTH(A3)=12’ as the min and max values but there is never any change to the background colour.
What is the correct way to use a formula for specifying a colour scale in the conditional formatting dialog?