Clear Filters
Clear Filters

Convert serial number date in date string - Excel file

11 views (last 30 days)
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 =
I wuold like to convert time into string in order to have time as expressed in the excel file, so in this format:
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 on 16 Nov 2021
Could you please attach the sample xls file. This would help us to help you.
Jan on 16 Nov 2021
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.

Sign in to comment.

Answers (1)

Jeremy Hughes
Jeremy Hughes on 16 Nov 2021
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.
  1 Comment
Peter Perkins
Peter Perkins on 23 Nov 2021
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 =

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!