Hi
Because I use very old date (before the 01/01/1900), I receive an error message from flexcel :
The value should be between -657435 and 2958466.
Could you extent this range, or because of excel limitation, I have to test the value before to convert it in date ?
regards
olivier
Hi,
The date is an Excel limitation. Excel has 2 different date systems: one starts in 1900 and the other in 1904. (normally the 1904 mode was used in old pre osx macs, but you can still set a 1904 date mode today, and FlexCel fully supports 1904 dates) See https://support.microsoft.com/en-us/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel
Also, I wouldn't use dates before 1901 even if using 1900 date system, because Excel incorrectly assumes 1900 to be a leap year ( https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year ) so there is a "january 29, 1900" in Excel that didn't really existed. Any date before jan 29 1900 will be wrong by one day due to this, and that's why I wouldn't use 1900 itself in the date system.
The only solution here is to enter those old dates as strings, not dates.
Thanks for your answer
I was almost sure that your "string solution" is the only way
olivier
Hi
It is just a comment.
I convert very old date to string, and it works of course. However, your limit value is very very far from 01/01/1900.
-657435, this date is around 1000 AJ ;)
regards
olivier
The main issue here is that you can enter older dates in Excel. They just will show as #####
Dates are just numbers, so you can go to a cell in Excel, enter -650000 and format the cell as date. It will show as ########, but it is possible to enter the date, so FlexCel will let you too.
So you can do for example:
xls.SetCellValue(1, 1, EncodeDate(1, 1, 1));
And FlexCel won't complain (but Excel will show #########).
There are 3 different date formats playing here:
1)The Excel date format. This is just a double, so you could enter numbers even less than 657435, but Excel won't show them, and they would be wrong anyway because they are 1 day off because of the "1900 leap year" issue in Excel.
2)The OLE Automation Format. This is what Excel somehow is trying to implement (but wrong, because of the leap year), and also what Delphi is supposed to implement.
The OLE Automation Date format min value is -657435 (1/1/0100), and that is why you see that error if you try an even older value.
https://msdn.microsoft.com/en-us/library/system.datetime.tooadate(v=vs.110).aspx
[quote]
The base OLE Automation Date is midnight, 30 December 1899. The minimum OLE Automation date is midnight, 1 January 0100. The maximum OLE Automation Date is the same as DateTime.MaxValue, the last moment of 31 December 9999.
[quote]
3)The TDateTime format in Delphi. This is supposed to implement an OLE Automation Date (so it can interop with COM and ActiveX), but it lowers the limit a little: you can enter dates from 1/1/1 instead of 1/1/100.
http://docwiki.embarcadero.com/Libraries/Seattle/en/System.TDateTime
In a perfect world, those 3 date formats would be the same, but as said there are little differences. The 3 are double precision numbers counting the days since 1/1/1900 where the fractional part is the time. But due to the bug in the 1900 leap year which is older than the OLE automation datetime definition, both 2) and 3) start at 31/12/1899 instead of 1/1/1900. And due to the inability of the original macos to display dates in 1900, Excel dates might start at 1904 too.
So you can't cast the double Excel returns to a Delphi TDateTime directly as TDateTime(0) in Delphi would be 31/12/1899 and 0 in Excel is 1/1/1900.
That is why we have the ToOADate/FromOADate method to convert between them. This will add 1 day to the dates between 1/1/1900 and 28/1/1900 so they show the same as in Excel, and then just convert directly (since dates after 29/1/1900 are the same in Delphi or Excel: they get in sync with the extra 29/1/1900 day added in Excel that didn't exist).
As you can see this is all a little of a mess, and we try to fix it as good as we can. But some border cases do exist: For example if you enter 29/1/1900 in Excel, what date should we show in Delphi? You can't put 29/1/1900 in a TDateTime as that date didn't exist.
To be completely honest, in Delphi the limit could be a little lower than -657435: In delphi you can enter from 1/1/1 and not 1/1/100. But we preferred to keep the limit 1/1/100 since that is also the minimum date VBA will use (in case you are using macros), and also to keep compatibility with FlexCel.NET or anywhere else where OLE Automation dates are used. But really it doesn't matter much, since dates before 1900 are wrong in Excel anyway.
Really thanks for this very complete answer (maybe could be a part of the FAQ in Flexcel ?)
Anyway, I am sad because I believed that the world was perfect :( [please don't tell me that Santa Claus doesn't exist)
regards