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

9 ビュー (過去 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 日
@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.

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

採用された回答

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 月 22 日
"How can I assign the other columns (numerical values) to variables?"
vec = tbl.value
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 件のコメント
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 ExchangeDates and Time についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by