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

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?

Answer 1

[Star Strider]

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

Answer 2

[Peter Perkins]

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.

Answer 3

[Michael Wolf]

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.

[Peter Perkins]

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.