Bug 124231 - Day function for creating date is off by one
Summary: Day function for creating date is off by one
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: filter:xlsx
Depends on:
Blocks: Calc-Function XLSX
  Show dependency treegraph
Reported: 2019-03-20 21:20 UTC by Mel Llopart
Modified: 2019-04-23 10:31 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

This is a single work sheet showing the day bug. (11.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-03-21 00:51 UTC, Mel Llopart

Note You need to log in before you can comment on or make changes to this bug.
Description Mel Llopart 2019-03-20 21:20:30 UTC
I use a spreadsheet between windows and linux and it does some date calculations.  I noticed that some entries on the Excel side had a value of 3/21/2019 and on the linux side had a value of 3/20/2019.  So when I checked the two systems were converting a date using the function Date(Year(xxx), Month(xxx), Day(21))
On excel the value was 03/21/2019  on Libreoffice in linux it was 03/20/2019.  This caused some values to be incorrectly calculated.  Everything catches up by the end of the month, but seemed wrong.

Steps to Reproduce:
1.Use the date function =DATE(
2. Generate a date from another date with Year, Month, Day
3. Or just enter =DAY(21) and you will get 20

Actual Results:
Entered =DAY(20) got 19 and entered =DAY(1) and got 31

Expected Results:
Expected 20 and expected 1

Reproducible: Always

User Profile Reset: No

Additional Info:
Comment 1 Xisco Faulí 2019-03-20 22:37:16 UTC
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 2 Mel Llopart 2019-03-21 00:51:50 UTC
Created attachment 150135 [details]
This is a single work sheet showing the day bug.

Here is the xlsx files.  I also have an ods version, both show the same problem.  This is just one worksheet out of a file that has 17 worksheets, one per month.
Comment 3 Aron Budea 2019-04-23 05:42:58 UTC
This is because of Calc's starting date counting being different from Excel's to account for Excel's 1900 leap year bug. For details please see bug 44453 comment 3.

However, your usage of DAY(...) function is unnecessary, instead of DAY(21) you should just have 21 in there.
Comment 4 Eike Rathke 2019-04-23 10:31:41 UTC
To add to this, DAY(21) is not only unnecessary, it's wrong. DAY() calculates the day of month of the date serial number (days since null date) passed as argument. The date serial number 21 here happens to represent 1900-01-21 in Excel so there's no visible difference in the result, but 1900-01-20 in Calc because of Excel's leap year bug for which Calc shifts the null date.