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

1 次查看（过去 30 天）
Erin Winkler
Erin Winkler 2019-2-22
评论： Peter Perkins 2019-2-25
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 个评论
显示 1更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Gani
Gani 2019-2-22
编辑：Gani 2019-2-22
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 个评论
显示 3更早的评论
Walter Roberson
Walter Roberson 2019-2-23
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 2019-2-25
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.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by