View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0038742||Lazarus CCR||FPSpreadsheet||public||2021-04-12 09:48||2021-05-16 08:08|
|Summary||0038742: TsWorksheetGrid shows incorrect day for dates between 01-jan-1900 and 28-feb-1900|
|Description||On TsWorksheetGrid, the day representation is incorrect for dates between 01-jan-1900 and 28-feb-1900.|
|Steps To Reproduce||Create 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.
|Tags||No tags attached.|
||Additional info: https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year.|
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.
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.
||Looked into the issue in more detail and found a few related bugs -> reopening.|
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
Thank you wp.
(not related to this issue) how can I download the "trunk" version without SVN?
> how can I download the "trunk" version without SVN?
"Download snapshot" from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/
|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|