Bug 152877

Summary:	Dates are getting formatted as dollar amounts and can't change
Product:	LibreOffice	Reporter:	keith hayden <keith73>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED INSUFFICIENTDATA
Severity:	normal	CC:	leslinetesting, stephane.guillou
Priority:	medium
Version:	7.3.7.2 release
Hardware:	x86-64 (AMD64)
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=154286
Whiteboard:
Crash report or crash signature:		Regression By:

Description keith hayden 2023-01-04 14:43:14 UTC

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

Comment 1 Lesline 2023-01-23 05:19:03 UTC

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

Comment 2 eisa01 2023-03-18 22:13:07 UTC

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

Comment 3 keith hayden 2023-03-19 19:15:57 UTC

(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.

Comment 4 keith hayden 2023-03-19 19:23:54 UTC

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.

Comment 5 Stéphane Guillou (stragu) 2023-03-20 15:13:31 UTC

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.)

Comment 6 eisa01 2023-12-29 12:07:12 UTC

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