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

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

[madhan ravi]

upload your file

[Erin Winkler]

I can't. It contains classified information.

[Erin Winkler]

I attached an example spreadsheet

Answer 1

[Gani]

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

[Erin Winkler]

This is perfect. I can convert the times back and then put them into a table to work with. Thank you very much! Do excel and MATLAB have different serial numbers?

[Gani]

What serial numbers ? sorry i didnt get you

[Erin Winkler]

Don't worry about it, I figured it out. Thank you for your help!

[Walter Roberson]

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]

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.