How to import correct time format from Excel

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....

Answer 1

[Star Strider]

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

[Daphne PARLIARI]

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]

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.