Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question
1  
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:

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.

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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