How to convert excel date and time data into numerical integer matrix [year, month, day, hour, minute, second]
10 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Anwaar Alghamdi
am 21 Nov. 2022
Bearbeitet: Stephen23
am 24 Nov. 2022
Greetings,
I have huge excel data starts with the following columns:
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1201508/image.png)
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 Kommentare
Stephen23
am 21 Nov. 2022
@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.
Akzeptierte Antwort
Stephen23
am 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)
3 Kommentare
Stephen23
am 22 Nov. 2022
"How can I assign the other columns (numerical values) to variables?"
vec = tbl.value
Stephen23
am 24 Nov. 2022
Bearbeitet: Stephen23
am 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)
writetable(tbl,'myfile.xlsx')
Weitere Antworten (1)
cr
am 21 Nov. 2022
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 Kommentare
cr
am 21 Nov. 2022
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);
Siehe auch
Kategorien
Mehr zu Dates and Time finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!