Description: I have a series of spreadsheets I use for monthly spending tracking in Calc. I had been using LibreOffice 6.x since it's release but decided to upgrade this week. I downloaded and installed version 7.4.3 on Mac OS Catalina. The first time I opened one of my spreadsheets (an ods file), the dates already entered were displaying as dollar amounts. I tried clicking to format as date, creating a new column and starting over, but still no joy. I created a new, blank spreadsheet and entered a date into an unformatted cell, 12/23/2022 and then clicked the "format as date" button and it changed the date to a number, 44918 and if I keep clicking it alternates between "12/23/22" and "44918". I'm a software engineer so I was curious what that number represented. I'm used to dates as seconds from epoch (1/1/1970) but these numbers didn't work that way. I used an online date calculator and entered the date 12/23/2022 and subtracted 44918 days, it returned December 30, 1899 as the result. So then I entered 12/23/1899 in the spreadsheet and clicked the "format as date" button and it displayed "-7". So I think I figured out what it's doing, just not WHY. I can provide screenshots and a screen capture video of the issue if needed. Steps to Reproduce: 1.create spreadsheet (.ods) 2.enter date in an unformatted cell as MM/DD/YY 3.click "format as date" button, see change to number Actual Results: in existing spreadsheet, dates are displaying as currency and I'm unable to change it. in new spreadsheet, date is displayed as number when clicking "format as date" Expected Results: should display cell as a date, not a number (or currency) Reproducible: Always User Profile Reset: Yes Additional Info: Version: 7.3.7.2 / LibreOffice Community Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f CPU threads: 12; OS: Mac OS X 10.15.7; UI render: default; VCL: osx Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded
Hello Keith. Thank you for reporting the bug. I can confirm that I was able to reproduce it using the following versions: Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: d07b1cc8148140bd3a78103da668d002b9d266ab CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded Version: 7.4.3.2 (x64) / LibreOffice Community Build ID: 1048a8393ae2eeec98dff31b5c133c5f1d08b890 CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded For clarity: This bug was reproduced using the steps provided. When the right click command is used to format the date, this error does not occur. Kind Regards
So this would likely not be macOS specific It is also not a bug that spreadsheets store Dates as numbers, that's standard behavior For the ods from 6.x series in 7.4.3, does that corruption still happen with the new 7.5? If so, can you upload a sample document that shows fine in 6.x, but not in 7.5? So not the document you may have saved in 7.5
(In reply to eisa01 from comment #2) > So this would likely not be macOS specific > > It is also not a bug that spreadsheets store Dates as numbers, that's > standard behavior > > For the ods from 6.x series in 7.4.3, does that corruption still happen with > the new 7.5? > > If so, can you upload a sample document that shows fine in 6.x, but not in > 7.5? So not the document you may have saved in 7.5 I am aware and agree that storing the date as a number isn't unusual, but the displaying it as one is. Unfortunately, After I upgraded to 7.5, none of my ods files are correct, even when downgrading back to 6.x. So I no longer have a "document that shows fine in 6.x, but not in 7.5". Also, I didn't need to save the doc in 7.x for this to occur, simply had to open it in 7.x and it happened. After downgrading back to 6.x, it was happening there too. I even tried completely uninstalling and reinstalling, nothing worked anymore.
correction on previous comment where I said "Unfortunately, After I upgraded to 7.5" I meant to say "7.4.3". I have not tried any version since that. I had to stop using Calc and move to Google sheets because this bug made Calc totally unusable for what I needed it for.
Using the en-US locale, I wasn't able to reproduce the date-to-currency issue with the following steps: 1) create a sheet in 6.x 2. add a date in MM/DD/YY format (which is automatically recognised as a date) 3. save 4. reopen in 7.x and use the date formatting button off and on (number format is expected when off) Versions I tried: file created in 6.0.0.3 or 6.4.7.2 -> opened in 7.3.7.2 or 7.4.6.2 Without an example file, it will be very difficult to figure out what the issue is. Lesline, what exactly did you see as a result in your tests? (Please keep in mind that Calc displaying a number when date formatting is turned off is an expected behaviour. Wrong behaviour would be dates displayed as dates in one version, dollars in another.)
This has not been confirmed for six months, and while strictly speaking not in NEEDINFO state for that long, it would have been closed as no follow up came