How to convert excel date and time data into numerical integer matrix [year, month, day, hour, minute, second]
10 次查看(过去 30 天)
显示 更早的评论
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 个评论
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
2022-11-21
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 个评论
Stephen23
2022-11-22
"How can I assign the other columns (numerical values) to variables?"
vec = tbl.value
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]
dtm = datetime(mat(:,1),mat(:,2),mat(:,3),mat(:,4),0,0)
tbl = table(dtm)
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 个评论
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!