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$

2 Answers 2

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

$\endgroup$
0
$\begingroup$

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

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

Your Answer

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

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