Convert serial number date in date string - Excel file

7 次查看（过去 30 天）
CARLOTTA DENTICO
CARLOTTA DENTICO 2021-11-16
评论： Peter Perkins 2021-11-23
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!
  2 个评论
ANKUR KUMAR
ANKUR KUMAR 2021-11-16
Could you please attach the sample xls file. This would help us to help you.
Jan
Jan 2021-11-16
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Jeremy Hughes
Jeremy Hughes 2021-11-16
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 个评论
Peter Perkins
Peter Perkins 2021-11-23
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

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by