5
$\begingroup$

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.

$\endgroup$
5
$\begingroup$

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

| improve this answer | |
$\endgroup$
0
$\begingroup$

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

| improve this answer | |
$\endgroup$
  • $\begingroup$ DateString[40969.00069*86400] should be March 1st, not 3rd. You can find the explanation via my answer. $\endgroup$ – Chris Degnen Apr 5 '13 at 12:58
  • $\begingroup$ You're welcome. $\endgroup$ – Chris Degnen Apr 5 '13 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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