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

2 ビュー (過去 30 日間)
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.
  2 件のコメント

サインインしてコメントする。

採用された回答

Stephen23
Stephen23 2022 年 11 月 21 日
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
  3 件のコメント
Stephen23
Stephen23 2022 年 11 月 24 日
編集済み: Stephen23 2022 年 11 月 24 日
"...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')

サインインしてコメントする。

その他の回答 (1 件)

cr
cr 2022 年 11 月 21 日
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.
  2 件のコメント
cr
cr 2022 年 11 月 21 日
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 Tables 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!

Translated by