Convert dates from Excel to Matlab

2
votes

I have a series of dates and some corresponding values. The format of the data in Excel is "Custom" dd/mm/yyyy hh:mm. When I try to convert this column into an array in Matlab, in order to use it as the x axis of a plot, I use:

a = datestr(xlsread('filename.xlsx',1,'A:A'), 'dd/mm/yyyy HH:MM');

But I get a Empty string: 0-by-16.

Therefore I am not able to convert it into a date array using the function datenum.

Where do I make a mistake? Edit: passing from hh:mm to HH:MM doesn't work neither. when I try only 

 a = xlsread('filename.xlsx',1,'A2')

I get: a = []

3
excelmatlabdatedatetime
Can you spilt the commands into 2 (first xlsread then datestr)? That will help you narrow the problem. It may be that xlsread is not working for whatever reason and returning an empty result. - am304
hh (hours) and mm (minutes) doesn't exist in matlab: use HH and MM instead. datestr(string,'dd/mm/yyyy HH:MM'); - obchardon
xlsread outputs numbers as it's first argument, so you are getting Excel serial numbers which are not the same as Matlab serial date numbers. Try either using x2mdate if you have the finance toolbox or just adding 693960 i.e. dates_as_numbers = xlsread('filename.xlsx',1,'A:A') + 693960 - Dan
Also is that 'A2' a typo on your second last line? Sure it should be 'A:A'? - Dan

3 Answers

1
votes

According to the documentation of datestr the syntax for minutes, months and hours is as follows:

HH -> Hour in two digits
MM -> Minute in two digits
mm -> Month in two digits

Therefore you have to change the syntax in the call for datestr. Because the serial date number format between Excel and Matlab differ, you have to add an offset of 693960 to the retrieved numbers from xlsread.

dateval    = xlsread('test.xls',1,'A:A') + 693960;
datestring = datestr(dateval, 'dd/mm/yyyy HH:MM');

This will read the first column (A) of the first sheet (1) in the Excel-file. For better performance you can specify the range explicitly (for example 'A1:A20').

The code converts...

enter image description here

... to:

datestring =
22/06/2015 16:00

Edit: The following code should work for your provided Excel-file:

% read from file
tbl = readtable('data.xls','ReadVariableNames',false);
dateval = tbl.(1);
dateval = dateval + 693960;
datestring = datestr(dateval)

% plot with dateticks as x-axis
plot(dateval,tbl.(2))
datetick('x','mmm/yy')
%datetick('x','dd/mmm/yy')  % this is maybe better than only the months
0
votes

Minutes need to be called with a capital M to distinguish them from months. Use a=datestr(xlsread('filename.xlsx',1,'A:A'),'dd/mm/yyyy HH:MM')

Edit: Corrected my original answer, where I had mixed up the cases needed.

0
votes

I tried with this. It works but it is slow and I am not able to plot the dates at the end. Anyway:

table= readtable ('filename.xlsx');
dates = table(:,1);
dates = table2array (dates);
dates = datenum(dates);
dates = datestr (dates);