Create new matrix by date/time

I have an excel sheet that contains a column of dates and a column of times. Also, each day is designated by its own header (I have attached the sheet to this question). I want to be able to extract each day of data and put it into its own matrix (without any of the headers). Any recommendations on the best way to accomplish this?

[Samantha Wallace]

Also, I need to code to work for different excel sheets with different sets of dates/times. So it needs to recognize different dates but not specific ones. Needs to work on a general basis.

Answer 1

[Nikhil Negi]

Hello Samantha,

If you didn't have any header in your file you could have directly used readtable

but since you have headers for each date, what you can do is read the xls file as table and search for the string 'Date' by lopping over 1st column of the table and save its index and then search for the string 'Date' again and save its index and extract the data from between these two indices. you can do that over and over and get the data between different dates.

Answer 2

[Peter Perkins]

The answer depends on what version of MATLAB you have. In recent versions, if you use readtable, that date column in the spreadsheet will be read in as a datetime variable in the table. The time column will propbably be read in as text.

To convert to durations, use this utility:

https://www.mathworks.com/matlabcentral/fileexchange/66940-text2duration

and then (assuming you want the date and time combined) just add the durations to the datetimes. Something like

t = readtable('Dat3eTimeData.xls');
t.Time = text2duration(t.Time);
t.DateTime = t.Date + t.Time