How to convert date/time from excel in 2 separate columns to a single date/time variable in matlab

2 次查看（过去 30 天）
Michael Wolf
Michael Wolf 2018-10-30
评论： Peter Perkins 2018-11-1
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?

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

回答（3 个）

Star Strider
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
Peter Perkins
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.
Michael Wolf
Michael Wolf 2018-11-1
Update: I learned that the MS Excel saves dates as a number greater than 1 and times as a decimal (<1), and that a user can add the date number to the time number to obtain a combined date-time. I then used datestr() to convert this number to a date-time string (formatted as '11-Aug-2022 01:14:20')
I now have one 1x122 double composed of these date/times and another 1 x 122 double consisting of measurements at each date/time and am wondering how to combine these two series, such that each time can be paired to its corresponding data value.
  1 个评论
Peter Perkins
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!

Translated by