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

15 views (last 30 days)
Anwaar Alghamdi on 21 Nov 2022
Edited: Stephen23 on 24 Nov 2022
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 on 21 Nov 2022
@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 on 21 Nov 2022
Here is a sample data file.

Stephen23 on 21 Nov 2022
fnm = 'sample data.xlsx';
obj = detectImportOptions(fnm);
obj = setvartype(obj,'date','datetime');
obj = setvartype(obj,'time','duration');
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
##### 3 CommentsShow 1 older commentHide 1 older comment
Stephen23 on 22 Nov 2022
"How can I assign the other columns (numerical values) to variables?"
vec = tbl.value
Stephen23 on 24 Nov 2022
Edited: Stephen23 on 24 Nov 2022
"...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')

cr on 21 Nov 2022
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 on 21 Nov 2022
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 on 21 Nov 2022
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);