Issue 107626 - The DAY() function fails under conditional formatting
Summary: The DAY() function fails under conditional formatting
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOO310m19
Hardware: Mac Mac OS X 10.5
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2009-12-11 21:49 UTC by daninilnw
Modified: 2013-08-11 19:28 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

spreadsheet. conditional formatting plays havoc with the DAY() function (13.12 KB, text/plain)
2009-12-11 21:50 UTC, daninilnw
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description daninilnw 2009-12-11 21:49:16 UTC
Making a calendar. Columns A-G have incremental dates formatted in custom format DD/MM/YY. using 
the formula e.g. B4 = A4+1, where A=28/12/09.
columns I to O use the DAY() formula to display the day of the month. I want the days of the previous 
month to be grayed, so I use conditional formatting on the first row to apply a grayed backgound if the 
value of the call is less than 15. When I do this, the DAY() function subtracts 1 from the value. So if cell A4 
= 28/12/09 then cell I4 is at first 28. But after conditional formatting it is 27. This is very strange. 
However, it does nothing to the cells for which the conditional formatting is false.
Comment 1 daninilnw 2009-12-11 21:50:36 UTC
Created attachment 66617 [details]
spreadsheet. conditional formatting plays havoc with the DAY() function
Comment 2 Joe Smith 2009-12-12 14:56:08 UTC
As far as I can see, it's not conditional formatting, or the DAY function,
causing the wrong day to be displayed, it's your cell format.

E.g. I4: you're applying a custom number format of "D" to the result of
DAY(2009-12-28), which is 28. Serial date 28 is 1900-01-27 and the day ("D") of
that date is 27, so your cell format displays the 28 from DAY() as 27.

Changing the number format in style "Grayed" to Number/General gives correct day
numbers in the grayed cells.

You can discuss problems like this on the users mailing list or the community forum:
Comment 3 Edwin Sharp 2013-08-11 19:28:35 UTC
As given in description.

Rev. 1503704 Debian