MATLAB Answers

Cannot read in times from excel spreadsheet and datenum is off by at least 10 seconds?

26 views (last 30 days)
Erin Winkler
Erin Winkler on 22 Feb 2019
Commented: Peter Perkins on 25 Feb 2019
Hi all,
I have an excel spreadsheet with columns of various times (time 1, time 2, minutes between time 1 and 2, time 3, time 4, minutes between time 4 and time 1).
The times are all of the form HH:mm:ss and the minutes are either something like 00:7:30 or 7.5 (different between the two columns).
I'm using readtable but when I look at what has been read, my times have been converted to what I thought was a datenum? (For example, the time 7:58:20 came in a 0.3323 (which when converted becomes 7:58:30 in MATLAB)).
How can I read in my excel spreadsheet and keep all of my times in their original format?
Thank you,
Attached is an example spreadsheet


Sign in to comment.

Accepted Answer

Gani on 22 Feb 2019
Edited: Gani on 22 Feb 2019
Below code worked for me.
after conversion values are as in excel.
A = readtable('C:\Users\Desktop\Example.xlsx');
naTest = datetime(A.ATime,'ConvertFrom','excel', 'Format','HH:mm:ss');
Also we can import using xlsread and can convert all the columns at once. It may be helpful for you.
[a , ~, ~]= xlsread('C:\Users\Desktop\Example.xlsx');
naTest = datetime(a,'ConvertFrom','excel', 'Format','HH:mm:ss');


Show 2 older comments
Walter Roberson
Walter Roberson on 23 Feb 2019
Excel serial times usually are days relative to Jan 1, 1900. But they also had a bug in leap year calculation, believing that 1900 was a leap year (it was not). As a result there is a different version that is relative to Jan 1, 1904.
MATLAB serial time is days since 0 CE.
In both cases you need to be careful in dealing with dates before the October Revolution due to potential differences in calendar systems.
Peter Perkins
Peter Perkins on 25 Feb 2019
In recent versions of MATLAB, using readtable to read a spreadsheet will automatically convert dates/times in Excel to datetimes in the table. No explicit conversion needed on your part. If you use readtable and find yourself with text where you should have datetimes, you may have an older version of MATLAB, or the spreadsheet may not be formatted appropriately, but you can convert to datetime after reading. If you find yourself with excel serial day numbers, there's probably something funny with the spreadsheet, but you can convert to datetime as in Gani's solution.
I recommend not using xlsread unless you have a pretty old version of MATLAB. And I strongly recommend not using MATLAB's older serial date number representation ("datenums") unless you have a good reason.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!