How to convert excel date and time data into numerical integer matrix [year, month, day, hour, minute, second]

Greetings,

I have huge excel data starts with the following columns:

Sample data file attached.

I want to sort the data by comparing months/years/hours inside functions. Hence, I need the years, months, days, hours to be seperate integers.

needed output:

year = 2016
month = 11
day = 23
hour = 16
minute = 33

How can I do that (knowing I have tried reading file as table and time table and many other functions).

I would apreciate any help.

[Stephen23]

@Anwaar Alghamdi: please upload a sample file by clicking the paperclip button. This does not have to be your complete data file or contain your confidential data, but it does need to exactly represent the format of your actual data files.

[Anwaar Alghamdi]

Here is a sample data file.

Answer 1

[Stephen23]

fnm = 'sample data.xlsx';
obj = detectImportOptions(fnm);
obj = setvartype(obj,'date','datetime');
obj = setvartype(obj,'time','duration');
tbl = readtable(fnm,obj)

tbl = 14×3 table
       date          time       value
    ___________    _________    _____

    01-Jan-2016    10800 sec      26 
    01-Jan-2016    10800 sec      25 
    01-Jan-2016    14400 sec      24 
    01-Jan-2016    18000 sec      24 
    01-Jan-2016    21600 sec      23 
    01-Jan-2016    21600 sec      23 
    01-Jan-2016    25200 sec    22.4 
    01-Jan-2016    28800 sec      22 
    01-Jan-2016    32400 sec      22 
    01-Jan-2016    32400 sec      22 
    01-Jan-2016    36000 sec      20 
    01-Jan-2016    39600 sec      20 
    01-Jan-2016    43200 sec      19 
    01-Jan-2016    43200 sec      18 

DT = tbl.date + tbl.time

DT = 14×1 datetime array
   01-Jan-2016 03:00:00
   01-Jan-2016 03:00:00
   01-Jan-2016 04:00:00
   01-Jan-2016 05:00:00
   01-Jan-2016 06:00:00
   01-Jan-2016 06:00:00
   01-Jan-2016 07:00:00
   01-Jan-2016 08:00:00
   01-Jan-2016 09:00:00
   01-Jan-2016 09:00:00
   01-Jan-2016 10:00:00
   01-Jan-2016 11:00:00
   01-Jan-2016 12:00:00
   01-Jan-2016 12:00:00

[Year,Mon,Day] = ymd(DT)

Year = 14×1

2016 2016 2016 2016 2016 2016 2016 2016 2016 2016

Mon = 14×1

1 1 1 1 1 1 1 1 1 1

Day = 14×1

1 1 1 1 1 1 1 1 1 1

[Hour,Min,Sec] = hms(DT)

Hour = 14×1

3 3 4 5 6 6 7 8 9 9

Min = 14×1

0 0 0 0 0 0 0 0 0 0

Sec = 14×1

0 0 0 0 0 0 0 0 0 0

[Anwaar Alghamdi]

Thank you! Exactly what I need.

[Stephen23]

"How can I assign the other columns (numerical values) to variables?"

vec = tbl.value

https://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-table.html

[Stephen23]

"...how can I convert the matrix [year month day hour] back to the same date and time excel columns, with all zero minutes?"

What matrix? Why not just work with the DATETIME/DURATION obejcts?

In any case, given such an Nx4 matrix (note an Nx6 matrix would be simpler to work with):

mat = [2022,11,24,6; 2022,11,24,14; 1973,12,31,23]

mat = 3×4

2022 11 24 6 2022 11 24 14 1973 12 31 23

dtm = datetime(mat(:,1),mat(:,2),mat(:,3),mat(:,4),0,0)

dtm = 3×1 datetime array
   24-Nov-2022 06:00:00
   24-Nov-2022 14:00:00
   31-Dec-1973 23:00:00

tbl = table(dtm)

tbl = 3×1 table
            dtm         
    ____________________

    24-Nov-2022 06:00:00
    24-Nov-2022 14:00:00
    31-Dec-1973 23:00:00

writetable(tbl,'myfile.xlsx')

Answer 2

[cr]

An easier way of doing that would be convert the dates into datenum so that you dont have to individually compare years,months,...sec,millisec. date_number = datenum(datescolumn);

Unless you are using older versions of Matlab, a column with dates is automatically imported as datenumbers. If it doesn't datenum() may be used.

[Anwaar Alghamdi]

When I read the excel file for the date and time columns, the output data are presented in the following format:

1.0e+04 *

4.2370    0.0000

I want to compare hours without caring about the minutes, how is this possible?

Tha's why I need to seperate hours and minutes. Also I want to group the data by year and month.

[cr]

Well, then you may create your own date number for dates based on years, days and hours. To seggregate the dates into these use datavec(). E.g.

ymd = datevec(datesColumn);