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

6 次查看（过去 30 天）
Anwaar Alghamdi
Anwaar Alghamdi 2022-11-21
编辑： Stephen23 2022-11-24
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
Stephen23 2022-11-21
Ran in:
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 个评论
显示 1更早的评论
Stephen23
Stephen23 2022-11-24
编辑：Stephen23 2022-11-24
Ran in:
"...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 个评论
Anwaar Alghamdi
Anwaar Alghamdi 2022-11-21
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
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);

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by