Cannot read in times from excel spreadsheet and datenum is off by at least 10 seconds?
3 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
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 Commenti
Risposta accettata
Gani
il 22 Feb 2019
Modificato: Gani
il 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');
5 Commenti
Walter Roberson
il 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
il 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.
Più risposte (0)
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!