Take the 2-minute tour ×

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:


convertDate[serialdate_] :=
  (Mathematica form of DateTime.FromOADate (serialdate))


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.

share|improve this question
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. –  Sjoerd C. de Vries Jan 7 '13 at 10:34
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. –  Sjoerd C. de Vries Jan 7 '13 at 11:01
@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. –  Chris Degnen Jan 7 '13 at 11:08
@SjoerdC.deVries - my data is in CSVs, and there are lots of them. –  Chris Degnen Jan 7 '13 at 11:09
What if you import those CSVs in Excel and export as XLS? –  Sjoerd C. de Vries Jan 7 '13 at 11:45

1 Answer 1

up vote 5 down vote accepted

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


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

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

share|improve this answer
Marvellous! :^) –  Chris Degnen Jan 7 '13 at 16:15
@Chris Thanks. FYI, I just changed the implementation of excelSerialNumber slightly to eliminate one of those ever-present COM leaks. –  WReach Jan 7 '13 at 16:28

Your Answer


