View Issue Details

IDProjectCategoryView StatusLast Update
0038742Lazarus CCRFPSpreadsheetpublic2021-05-16 08:08
ReporterMAndreato Assigned Towp  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionwon't fix 
Platformx86-64OSWindows 
Summary0038742: TsWorksheetGrid shows incorrect day for dates between 01-jan-1900 and 28-feb-1900
DescriptionOn TsWorksheetGrid, the day representation is incorrect for dates between 01-jan-1900 and 28-feb-1900.
Steps To ReproduceCreate a new Excel (XLSX) spreadsheet; on A1 type, for example, "8/1/1900" and press enter (Excel will recognize it as a date), then save and close the file.
Create a new Lazarus Application project; on Form1 drop a TsWorksheetGrid and set the filename (and path) of its internal WorkbookSource to the saved Excel.
The A1 cell will show "7/jan" (a day before the correct value).

I've tested it with dates between 01-jan-1900 and 28-feb-1900 and it always show a day before.
TagsNo tags attached.
Widgetset
Attached Files

Activities

MAndreato

2021-05-14 15:49

reporter   ~0130871

Additional info: https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year.

wp

2021-05-14 16:23

developer   ~0130875

I know, and it seems that you seem to know the reason, too. Microsoft...

In an earlier version of fpspreadsheet, IIRC, this was correct, but it had the consequence that short time intervals could be wrong (because they exactly map into this number range). I decided to fix the date interval issue, and ignore the leap year 1900 issue which will become more and more less important with every day.

MAndreato

2021-05-14 16:39

reporter   ~0130876

Thanks wp.
It would be useful to document this behaviour on https://wiki.lazarus.freepascal.org/FPSpreadsheet, so maybe one could think to manage it on its own application by adding/removing one day unit to the date value.
IMHO, the best would be an option in TsWorkbookSource to choose between "Microsoft way" and "fixed way" of 1900 leap year management, both with their pros/cons.

wp

2021-05-15 12:43

developer   ~0130897

Looked into the issue in more detail and found a few related bugs -> reopening.

wp

2021-05-15 21:51

developer   ~0130911

Looking at the issue again confirms again that it is not solvable. Even if a date offset by 1 is introduced before March 1 1900 a new issue is introduced because now the weekdays become out of order...
I finally removed the buggy attempt in FPSpreadsheet to fix this - this is the same what LibreOffice is doing (they accept the same 1-day error in dates before March 1 1900).

Added a note to the documentation: https://wiki.lazarus.freepascal.org/FPSpreadsheet#A_note_on_Excel_dates

MAndreato

2021-05-16 05:44

reporter   ~0130913

Thank you wp.
(not related to this issue) how can I download the "trunk" version without SVN?

wp

2021-05-16 08:08

developer   ~0130914

> how can I download the "trunk" version without SVN?

"Download snapshot" from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/

Issue History

Date Modified Username Field Change
2021-04-12 09:48 MAndreato New Issue
2021-05-14 14:32 Bart Broersma Assigned To => wp
2021-05-14 14:32 Bart Broersma Status new => assigned
2021-05-14 15:49 MAndreato Note Added: 0130871
2021-05-14 16:23 wp Status assigned => resolved
2021-05-14 16:23 wp Resolution open => won't fix
2021-05-14 16:23 wp Note Added: 0130875
2021-05-14 16:39 MAndreato Note Added: 0130876
2021-05-15 12:43 wp Status resolved => confirmed
2021-05-15 12:43 wp Note Added: 0130897
2021-05-15 21:51 wp Status confirmed => resolved
2021-05-15 21:51 wp Note Added: 0130911
2021-05-16 05:44 MAndreato Note Added: 0130913
2021-05-16 08:08 wp Note Added: 0130914