Description: 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:
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.
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.
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.
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.