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.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
    
DateString[40969.00069*86400] should be March 1st, not 3rd. You can find the explanation via my answer. –  Chris Degnen Apr 5 '13 at 12:58
    
Thanks for the clarification. –  March Ho Apr 5 '13 at 13:33
    
You're welcome. –  Chris Degnen Apr 5 '13 at 13:42
add comment

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.