3
$\begingroup$

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}}

enter image description here

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.

$\endgroup$
6
  • 1
    $\begingroup$ It depends on the date range in your data whether this is an issue. This bug in Excel only exists for 1900 and if you only have data after March 1900 this should not be an issue. $\endgroup$ Jan 7, 2013 at 10:34
  • $\begingroup$ Besides, if you use 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$ Jan 7, 2013 at 11:01
  • $\begingroup$ @SjoerdC.deVries - Thanks. Yes I can use 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$ Jan 7, 2013 at 11:08
  • $\begingroup$ @SjoerdC.deVries - my data is in CSVs, and there are lots of them. $\endgroup$ Jan 7, 2013 at 11:09
  • $\begingroup$ What if you import those CSVs in Excel and export as XLS? $\endgroup$ Jan 7, 2013 at 11:45

1 Answer 1

8
$\begingroup$

Here is a way to invoke System.DateTime.FromOADate using NETLink:

Needs["NETLink`"]
InstallNET[];
LoadNETType["System.DateTime"];

fromOADate[d_] :=
  DateList @ NETBlock @ System`DateTime`FromOADate[d]@ToString[]

Note, however, that FromOADate does not share Excel's backward-compatible implementation of Lotus 1-2-3's date bug. To see this, we introduce a helper function that invokes Excel to obtain date serial numbers:

excelSerialNumber[dates_List] :=
  NETBlock@Module[{excel, workbooks, workbook, worksheet, cell, result}
  , excel = CreateCOMObject["Excel.Application"]
  ; workbooks = excel@Workbooks
  ; workbook = workbooks@Add[]
  ; worksheet = workbook@ActiveSheet
  ; cell = worksheet@Range["a1"]
  ; result =
      Table[
        cell@Formula = DateString[d, {"=Date(","Year", ",", "Month", ",", "Day",")"}]
      ; cell@Value2[]
      , {d, dates}
      ]
  ; workbook@Close[False]
  ; excel@Quit[]
  ; Scan[ReleaseCOMObject, {excel, workbooks, workbook, worksheet, cell}]
  ; result
  ]

We can now compare the Excel and OLE Automation date serial numbers:

$serials =
  excelSerialNumber@{{1900,1,1}, {1900,2,27}, {1900,2,28}, {1900,3,1}, {2013,1,1}}
(* {1., 58., 59., 61., 41275.} *)

fromOADate /@ $serials
(*{{1899,12,31,0,0,0.},{1900,2,26,0,0,0.},{1900,2,27,0,0,0.},{1900,3,1,0,0,0.},{2013,1,1,0,0,0.}}*)

We can see that the OLE Automation implementation matches the Excel implementation only after Feb 28 1900.

$\endgroup$
2
  • $\begingroup$ Marvellous! :^) $\endgroup$ Jan 7, 2013 at 16:15
  • $\begingroup$ @Chris Thanks. FYI, I just changed the implementation of excelSerialNumber slightly to eliminate one of those ever-present COM leaks. $\endgroup$
    – WReach
    Jan 7, 2013 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.