How to import correct time format from Excel

112 views (last 30 days)
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....

Accepted Answer

Star Strider
Star Strider on 9 Jan 2020
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 Comments
Daphne PARLIARI
Daphne PARLIARI on 9 Jan 2020
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 on 9 Jan 2020
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.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!