How to convert excel date and time data into numerical integer matrix [year, month, day, hour, minute, second]
4 views (last 30 days)
Show older comments
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.
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
fnm = 'sample data.xlsx';
obj = detectImportOptions(fnm);
obj = setvartype(obj,'date','datetime');
obj = setvartype(obj,'time','duration');
tbl = readtable(fnm,obj)
DT = tbl.date + tbl.time
[Year,Mon,Day] = ymd(DT)
[Hour,Min,Sec] = hms(DT)
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]
dtm = datetime(mat(:,1),mat(:,2),mat(:,3),mat(:,4),0,0)
tbl = table(dtm)
More Answers (1)
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.
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);
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!Start Hunting!