Wrong dates on axis when plotting from xlsx file

I am not able to plot the correct dates when importing data from an .xlsx file. The year is correct but the months and days are wrong. The first date is supposed to be: 14/09/2018

This is my code:

[Sig, TStr, Raw] = xlsread('EV_F00093.xlsx','Sheet2','A1:D109875');
y=Sig(:,4);
x=Sig(:,2);

y_new=y+693960;

z=datestr(x,'HH:MM:SS');
c=datenum(z,'HH:MM:SS');            % convert date(time) into a number to plot it

b=datestr(y_new,'DD/MM/YYYY');      % convert excel serial dates to matlab serial dates
d=datenum(b,'DD/MM/YYYY');          % convert date into a number to plot it

plot(c,d,'.k');                     % plot the data,
hold on;
datetick('x','HH:MM:SS')            % give the a xaxis time label ticks..
datetick('y','DD/MM/YYYY')          % give the b yaxis time label ticks..

I have attached a screenshot of Sig, y_new, d and the plot.

All help is greatly appreciated.

Best

Answer 1

[Jan]

Do you see, that you use "MM" for minutes and months? The format strings for the date format are case-sensitive.

b=datestr(y_new, 'dd/mm/YYYY');
d=datenum(b, 'dd/mm/YYYY');

The conversion over the string representation and back to a serial date number is indirect. See x2mdate. I've seen several workarounds, e.g.:

y = y + datenum(1900, 1, 1) - 2;   % Or -1 ?!
c = fix(y);     % date
d = rem(y, 1);  % time

Do you have a modern Matlab version? Then the datetime objects are better than datestr.

[Sandra Holthe]

Hi Jan,

Thank you, I can see you point.

Yes, I am using Matlab R2018a. I tried to insert both the x2mdate and the datetime but I cannot make it work. When using x2mdate, the output numbers are still wrong (also with the wrong year) and I am not sure what to insert in 'convention' in the datetime function.

Date = x2mdate(y,1,'datenum');
plot(c,Date,'.k');

Thank you for responding,

Best.

[Sandra Holthe]

I figured it out. Thanks!