Filter löschen
Filter löschen

Convert serial number date in date string - Excel file

11 Ansichten (letzte 30 Tage)
CARLOTTA DENTICO
CARLOTTA DENTICO am 16 Nov. 2021
Kommentiert: Peter Perkins am 23 Nov. 2021
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 Kommentare
ANKUR KUMAR
ANKUR KUMAR am 16 Nov. 2021
Could you please attach the sample xls file. This would help us to help you.
Jan
Jan am 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.

Melden Sie sich an, um zu kommentieren.

Antworten (1)

Jeremy Hughes
Jeremy Hughes am 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 Kommentar
Peter Perkins
Peter Perkins am 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 =
datetime
31-Mar-2015

Melden Sie sich an, um zu kommentieren.

Community Treasure Hunt

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

Start Hunting!

Translated by