Excel serial dates are not straightforward to convert since they include non-existent dates, e.g.
{DatePlus[{1990, 1, 0}, 59], DatePlus[{1990, 1, 0}, 60]}
{{1990, 2, 28}, {1990, 3, 1}}
As shown, Mathematica finds date 60 is March 1st while Excel converts it to Feb 29th.
To do the conversion I would like to use .NETLink with
DateTime dt = DateTime.FromOADate(60);
as given here.
Can anyone help?
I imagine something of the following form would work:
Needs["NETLink`"];
convertDate[serialdate_] :=
NETBlock[
InstallNET[];
(Mathematica form of DateTime.FromOADate (serialdate))
]
Edit
As Sjoerd pointed out, the problem is caused by an Excel bug, but only for year 1900. Conversion can be done using this:
convertDate[serialdate_] :=
If[serialdate < 61, "N/A", DatePlus[{1901, 1, 0}, serialdate - 366]]
I would still be interested to see how it would be implemented in NETLink, for future reference for applications making quick calls to .NET.
Import
Mathematica converts Excel fields with values 59, 60, 61, but formatted as short date as {1900,2,28}, {1900,3,1} and {1900,3,1}, respectively. The same happens if you enter the dates directly in Excel in a date format (29/02/1900 or 29-Feb-1900). I feel that is the correct way to handle it, because nobody should have manually entered the non-existent 29-Feb-1900, which Mathematica maps to 1-Mar-1900. All other dates map just fine. $\endgroup$convertDate[serialdate_] := If[serialdate < 61, "N/A", DatePlus[{1901, 1, 0}, serialdate - 366]]
. It would still be interesting to see the syntax to do it with NETLink, for future reference for similar applications. $\endgroup$