import time and data from excel

Dear All,

I am facing a seemingly simple problem. I have data in the following format:

 date          var
 01.01.2000    13
 01.04.2000    15
...

in Excel and want to import it in Matlab such that date becomes a date vector and var a numbers vector. The matlab import function and another thread in this forum led me to:

 [~, ~, raw] = xlsread('\data\sandbox.xlsx','Tabelle1','A2:B9');
raw(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),raw)) = {''};
cellVectors = raw(:,1);
raw = raw(:,2);
data = reshape([raw{:}],size(raw));
date1 = cellVectors(:,1);
ARBANS010010 = data(:,1);
clearvars data raw cellVectors;
str = datestr( date1, 'DD/MM/YYYY' );
num = datenum( str,'DD/MM/YYYY' );

which produces an error. A minimum example is attached to this post. If you download the excel file and run my code you should get the same error. I am somewhat confused. My dates are not even in a weird format or something. Looks like a straight forward thing to do for matlab?

If anyone can produce code that succesfully imports the data or points me towards what I fail at seeing here, I would really appreciate it!

Thanks in advance!

Answer 1

[Andrei Bobrov]

[n,st] = xlsread('sandbox.xlsx',1,'A2:B9');
out = [datenum(st,'dd.mm.yyyy'),n];

[roblocks]

mh does this create a time vector for you Andrei Bobrov? For me it just creates a vector. (matlab 2015b).

converting with datetime(.) doesnt work as this is not 3 or 6 arrayed...

[roblocks]

ah I think I now what you mean:

 [n,st] = xlsread('\data\sandbox.xlsx',1,'A2:B9');
time  =  datenum(st,'dd.mm.yyyy');
date_d = datetime(time, 'ConvertFrom', 'Excel', 'Format', 'mm/dd/yyyy');

So finally I am now trying to get it in quarterly format. Changing to q/yyyy messes things up though.

[roblocks]

It is weird.

[n,st] = xlsread('\data\sandbox.xlsx',1,'A2:B9');
time  =  datenum(st,'dd.MM.yyyy');
a= datevec(time)
c=datetime(a, 'Format', 'dd/MM/yyyy')

Now c is different from st... somehow it loses information along the way...