How to import correct time format from Excel

조회 수: 117 (최근 30일)
Daphne PARLIARI
Daphne PARLIARI 2020년 1월 9일
댓글: Star Strider 2020년 1월 9일
Hi guys.
I have a rather trivial problem:
I want to read an .xlsx file that contains a column with time, eg. 01:00 02:00 03:00 etc.
When I use this command
[obsdata,txt,raw]=xlsread([obsdir,'\',StationName])
the column of time appears messed up, like 0.0416666666666667 0.0833333333333333 0.125000000000000 0.166666666666667 etc. I know this is a problem originating from the format used in excel. How can I solve it? I would appreciate any help....

채택된 답변

Star Strider
Star Strider 2020년 1월 9일
It is likely best to use readtable to read the Excel file.
Otherwise, use datetime to convert the vector from Excel to a datetime array:
tv = [0.0416666666666667 0.0833333333333333 0.125000000000000 0.166666666666667].';
times = datetime(tv, 'ConvertFrom','excel', 'Format','HH:mm')
producing:
times =
4×1 datetime array
01:00
02:00
03:00
04:00
  댓글 수: 2
Daphne PARLIARI
Daphne PARLIARI 2020년 1월 9일
OK that works but let's broaden the picture a bit.
For the .xlsx attached I do this
[obsdata,txt,raw]=xlsread([obsdir, '\', Kordelio, '.xlsx']);
raw1=raw(9:end,:)
HRtmp=char(raw1(:,2));
HR=num2str(str2num(HRtmp(:,1:2))-1,'%2d:00\n');
datenames = strcat(raw1(:,1),{' '}, HR);
obsdates = datetime(datenames,'InputFormat','dd/MM/yy HH:mm');
The column obsdates should have the following format
'01-May-2015 01:00:00'
and datenames
'01/05/15 1:00'
but of course that is not the case, as I mentioned in my original question. Trying your solution works (and thank you for that!) but I must make it work for the entire excel file.
Plus, an error appears:
Error using char
Cell elements must be character arrays.
Error in Untitled (line 44)
HRtmp=char(raw1(:,2));
I understand that the error comes from the faulty reading of date and time...
Star Strider
Star Strider 2020년 1월 9일
Try this:
T = readtable('Kordelio.xlsx');
Col2 = datetime(str2double(T{:,2}), 'ConvertFrom','excel', 'Format','HH:mm'); % Dates
Col1 = datetime(T{:,1}, 'InputFormat','MM/dd/yyyy'); % Times
DT = table(Col1+timeofday(Col2), 'VariableNames',{'DateTime'}); % Combined
T = [DT T(:,3:end)]; % New Table
FirstFiveRows = T(1:5,:) % Show Result (Delete Later)
Producing:
FirstFiveRows =
DateTime WS WD Ta RH
____________________ ____ _____ _____ ____
01-May-2015 01:00:00 0.92 85.9 15.53 73.6
01-May-2015 01:59:59 0.83 70.5 15.08 75.2
01-May-2015 03:00:00 0.43 38.13 14.32 75.9
01-May-2015 04:00:00 0.53 30.56 14.65 75.4
01-May-2015 04:59:59 0.4 118.4 14.67 75.3
The dates and times are now combined into one variable.
If you want to plot it:
figure
plot(T{:,1}, T{:,2:end})
grid
Make necessary changes to get the result you want.

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by