Convert serial number date in date string - Excel file

Hi everyone!

This is the first time importing an excel file in matlab. Here the problem:

I have used the following code to read the excel file in matlab

[num,txt,raw] = xlsread('filename.xls');

Now, time has been imported in the form of serial numbers (I guess)

time = num(:,1); % create time vector 
time (1:2) % just to show you what I mean as serial numbers..
ans =

       42094
       42094

I wuold like to convert time into string in order to have time as expressed in the excel file, so in this format:

       '03/31/2015'

So far, I tried doing this, but something went wrong..

      dd = datetime(time, 'ConvertFrom','datenum');
      >> dd(1:2)

ans = 

  2×1 datetime array

   01-Apr-0115 %%
   01-Apr-0115 %% 
   

Also, following another code that I used in the past (that was working), I got an error:

time = num(:,3)

tref = datenum ('1950-01-01 00.00.00'); % 00.00.00 here i wrote dot and not colon because in the excel file time was like '03/31/15 21.00.25'
time_greg = (time./24)+tref; 

t = datestr(time_greg);

TT = datenum(t);

   Array indices must be positive integers or logical values.

Error in formatdate (line 161)
            month = char(strrep(month(dtvector(:,2)), '.', '')); %remove period

Error in dateformverify (line 32)
S = char(formatdate([y,mo,d,h,minute,s],dateformstr,islocal));

Error in datestr (line 200)
S = dateformverify(dtnumber, dateformstr, islocal);

Error in untitled (line 7)
t = datestr(time_greg);

Thank you a lot for your precious help!

[ANKUR KUMAR]

Could you please attach the sample xls file. This would help us to help you.

[Jan]

tref = datenum ('1950-01-01 00.00.00')
% 00.00.00 here i wrote dot and not colon because in the excel file time 
% was like '03/31/15 21.00.25'

It does not matter what the format in the Excel file is. But fortuantely datenum() replies the correct value for the dots also.

tref = datenum ('0000-01-01 00:00:00') - datenum ('1950-01-01 00:00:00')

This converts the serial date number of Excel to the one of Matlab.

But your Excel file does not contain serial date numbers. A format like "1950-01-01 00.00.00" sounds like a string. So please post a small example file to clarify the contents.

Answer 1

[Jeremy Hughes]

First, I would suggest using readcell if you want to get datetimes. This will give you the right thing by default.

Otherwise, checkout datetime's convert from parameter which accepts 'excel' as a value.

[Peter Perkins]

Seconding what Jeremy said, I strongly recommend not using xlsread. I would have recommended readtable, not readcell, but maybe Jeremy is seeing something in your post that I am not.

In any case, stay away from using datenums in MATLAB. But you don't even have datenums! You have excel serial date numbers:

>> datetime(42094, 'ConvertFrom','excel')
ans = 
datetime
31-Mar-2015