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

2 ビュー (過去 30 日間)
Anwaar Alghamdi 2022 年 11 月 21 日

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 件のコメント表示非表示 1 件の古いコメント
Anwaar Alghamdi 2022 年 11 月 21 日
Here is a sample data file.

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

### 採用された回答

Stephen23 2022 年 11 月 21 日
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 件のコメント表示非表示 2 件の古いコメント
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 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 件のコメント表示非表示 1 件の古いコメント
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