How to convert date/time from excel in 2 separate columns to a single date/time variable in matlab
5 次查看(过去 30 天)
显示 更早的评论
I have a long data spreadsheet in excel which has date in 1 column and time in a separate column.
Example:
Date 08/09/2018
Time 04:12:32
I would like to convert each date/time pair into a single value representing the date and time, so that I can follow the exact times of other variables from this excel spreadsheet.
Is there a way to do this?
0 个评论
回答(3 个)
Star Strider
2018-10-30
One option:
dn = [datenum({'08/09/2018'; '08/10/2018'}, 'mm/dd/yyyy') + rem(datenum({'04:12:32'; '04:12:35'}, 'HH:MM:SS'),1)];
dt = datetime(dn, 'ConvertFrom','datenum')
dt =
09-Aug-2018 04:12:32
10-Aug-2018 04:12:35
0 个评论
Peter Perkins
2018-10-31
The right answer depends on how they are stored in the spreadsheet and what version of MATLAB you are using.
In recent versions, assuming they are stored appropriately in the spreadsheet, readtable should create a table with a datetime variable for the dates, and a duration variable for the times. Just add those two together and create a new variable in the table.
Otherwise, you may end up with text, or numbers that you will need to convert. Mostly that's easy, unless you end up with text for the times in an older version of MATLAB. For that, use text2duration on the FEX.
0 个评论
Michael Wolf
2018-11-1
1 个评论
Peter Perkins
2018-11-1
You have left out a crucial step in either your description, or your code. Excel counts days from 1900. datestr uses MATL:AB's (old) convention of counting days since 0.
Unless you are using a pretty old version of MATLAB, use readtable. Then follow my previous advice to use datetimes, not datenums. If what you find yourself with is a set of Excel serial date numbers, there is a very easy conversion to datetime, see 'ConvertFrom' in the doc.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!