Import correct date and time from different columns in Excel

Hi guys!

I have several excel files I want to access in matlab and of course one of the problems is date and time.

I am attaching one .xlsx in order for you to understand: I must combine columns B,C and D to get the date and column E is time (0 means 00:00, 1 means 01:00 etc).

Therefore the first raw of my data express the date and time 1/5/2019 (May 1st, 2019) at 00:00.

If I try this

obsdata(:, 2) = round(obsdata(:, 2)) ;

obsdata(:, 3) = round(obsdata(:, 3)) ;

obsdata(:, 4) = round(obsdata(:, 4)) ;

obsdata(:, 5) = round(obsdata(:, 5)) ;

Date = datetime(obsdata(:, 2), obsdata (:, 3), obsdata(:,4), obsdata(:,5), 0 ,0)

it doesn't work. Date appears messed up...

Any ideas please?

Answer 1

[Star Strider]

Try this:

In = readtable('Airport 2019 (1.5-30.9).xlsx', 'PreserveVariableNames',1);
hrmnsc = datetime(compose('%04d\n',In.Time), 'InputFormat','HHmm', 'Format','HH mm ss');
DVhms = datevec(hrmnsc);
DatesTimesDV = [table2array(In(:,2:4)), DVhms(:,4:6)];
DatesTimes = table(datetime(DatesTimesDV), 'VariableNames',{'DatesTimes'});
Airport = [In(:,1) DatesTimes In(:,6:end)];

The ‘Airport’ assignment is the desired result.

My apologies for the delay. It simply should not require a detour through date vectors to combine datetime dates and times.

[Daphne PARLIARI]

It produces error:

Error using readtable (line 143)

Invalid parameter name: PreserveVariableNames.

I am using R2016a.

[Star Strider]

‘I am using R2016a.’

It would have been helpful to have known that earlier.

In that situation, just use:

In = readtable('Airport 2019 (1.5-30.9).xlsx');

My code still runs and produces the correct result, it just produces a warning about changing the variable names to be compatible with MATLAB variable names conventions.

I was going to suggest that to avoid problems with the variable names to use:

In = readtable('Airport 2019 (1.5-30.9).xlsx', 'HeaderLines',1);

however when I try that it throws the error that 'HeaderLines' is an invalid parameter. This does not make sense, and I have asked MathWorks to look into it.

So without 'PreserveVariableNames', readtable will issue a warning about changing the variable names. It will still read the file correctly, and my code will still work and produce the correct result.

If my Answer helped you solve your problem, please Accept it!

[Daphne PARLIARI]

I tried but failed... I downloaded R2019a and things are much more straightforward:

A.dec_time = datetime(A.year, A.month, A.day, A.Time, zeros(size(A,1),1), zeros(size(A,1),1));

Thank you for your help!

[Star Strider]

As always, my pleasure!

R2019a solved many problems. Upgrade to R2019b if you have the option toi do that. It solves more of them.