MATLAB Answers

0

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

Asked by Erin Winkler on 22 Feb 2019
Latest activity Commented on by 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,
E
EDIT:
Attached is an example spreadsheet

  3 Comments

I can't. It contains classified information.

Sign in to comment.

1 Answer

Answer by Gani
on 22 Feb 2019
Edited by Gani
on 22 Feb 2019
 Accepted Answer

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');

  5 Comments

Don't worry about it, I figured it out. Thank you for your help!
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.
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.