5

I have a very large Excel file with dates in the format 2013/01/17 08:02:00 PM which is a custom date format. I have to convert this file to CSV in order to import it into Mathematica because I can't get around the Java heap space error (I have tried the suggestions on this site).

When I convert it to CSV the dates automatically change to the number format eg 40969.00069 which I think represents the number of days since 1900. This is a standard date format that is sometimes used in Excel and automatically used when I save the Excel file as a CSV.

When I now import the CSV file into Mathematica the dates stay in this number format. How can I get Mathematica to understand these 'number formatted dates'? When I tried the following code, just trying to get a year from the number formatted date, Mathematica returned 1900 no matter what year in number format I apply it to. This tells me that Mathematica doesn't understand what these numbers are.

DateString[41291.88819, {"Year"}]

I have looked at the help files and searched on this site but I can't seem to solve this.

cormullion
  • 24,243
  • 4
  • 64
  • 133
lara
  • 1,028
  • 7
  • 17

2 Answers2

5

This function converts serial dates only since March 1st, 1990, due to the Excel bug described here: Converting Excel serial dates with NETLink

convertDate[serialdate_] := 
 If[serialdate < 61, "N/A", DatePlus[{1901, 1, 0}, serialdate - 366]]

convertDate[40969.00069]

{2012, 3, 1, 0, 0, 59.616}

DateString@Round@%

Thu 1 Mar 2012 00:01:00

Community
  • 1
Chris Degnen
  • 30,927
  • 2
  • 54
  • 108
0

DateString[40969.00069*86400] seems to provide the correct date. Just input the date number and multiply it by 86400.

rcollyer
  • 33,976
  • 7
  • 92
  • 191
March Ho
  • 649
  • 5
  • 15