CODEWITHSUNDEEP
excelmatlabcsvdatetimetimestamp
zlon
zlon

Reputation: 834

Convert timestamp from excel to a matlab

I have a csv file with timestamp column. One example timestamp:43606.4791492245. If I insert this number in Excel, it gives me a correct time:5/21/2019 11:29:58 AM. But I need to convert it to a Matlab datetime object. I tried:

d = datetime(43606.4791492245, 'ConvertFrom', 'posixtime')
d = 
  datetime
   01-Jan-1970 12:06:46

And it is incorrect result.

Could you help me to convert this timestamp correctly? What kind of magic happens inside Excel conversion?

Upvotes: 0

Views: 177

Answers (1)

Matteo V
Matteo V

Reputation: 1163

Found it, use the 'excel' format:

datetime(43606.4791492245, 'ConvertFrom', 'excel')

which returns 21-May-2019 11:29:58.

Apparently, 'excel' datenums are the number of days between the given date and 31-Dec-1899, as you can read on the MATLAB documentation.

Quoting MrFuppes' comment below for a better explanation:

In Excel serial date, day 0 is [...] 1899-12-31. But since Excel inherited the leap year "bug" from Lotus 123 (1900 incorrectly considered a leap year), it is common to use 1899-12-30 as day zero. Between ordinal dates 0 and 60, this actually gives wrong results too, but most of the time, you won't encounter dates in that range.

Upvotes: 1

Related Questions