Calc - conditional formatting based on month value from date

English
, , ,
#1

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?

#2

Please see here for an explanation of the feature.

In essence, this is what you actually do. Let’s say you have Feb 28 in A3. Then your formatting is translated to this: “If a cell in range has value less that or equal to formula MONTH(Feb 28)=1 (i.e. FALSE, or 0) then apply color 1; else if its value is greater than or equal to formula MONTH(Feb 28)=12 (i.e. FALSE again, or 0) then apply color 2; else create gradient”. As you see, this is an invalid condition, where both limits are the same, so nothing happens. Again: the “All cells” condition is always applied to cell values as whole; formula is used only to calculate the reference value.

I suggest you to use Formula is instead of All cells, and create 12 conditions. That is not that much, and also would allow to use custom color values instead of automatically computed.

#3

Ok, thanks for that. It’s sometimes difficult to find helpful information about LibreOffice, documentation being the poor relation of development. Also, I didn’t realize I would have to create a separate condition for each month. The ‘formula’ processing seems to be much less capable than I was expecting.

#4

In addition to the recommendations Mike Kaganski, I want to recall an old trick, which, to my knowledge, is not available in some other office suites - using function STYLE() in conditional formatting. Of course, this does not release you from the creation of 13 different styles (one style used to leave an empty cells without color). But the condition in the conditional formatting is only one. See this file - 12ColorsFor12Month.ods

#5

Wow great! I never understood the purpose of that function before.

#6

Please note that the "trick " comes with disadvantages.

  1. It does not overlay a format conditionally for the view as CF does.
  2. It actually assigns the named cell style to the respective cells.
  3. As the calculation is part of a CF condition this is only done for cells if once the cell gets part of the view.
  4. (Beware!) If a user routine should ask for the cell style the result will depend on the history with respect to this.
  5. There are style aware features, alas!
#7

See also this thread in the other forum, in specific my first comment there mainly directed @keme.
Do you want a demo?

#8

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.