importdata not reading time stamp from excel sheet accurately

I have been using importdata to read an excel sheet with time stamps. The time stamp on excel sheet is 1/1/2015 12:20:18 AM. When importdata is used, this time stamp gets converted to [42005.014097222]. I tried using datestr([42005.014097222],31) command and this is being converted to '0115-01-02 00:20:18'; but it has to be 2015-01-01 00:20:18'. How can this be fixed? Thank you!

Here is the program:

[filename, pathname] = uigetfile({'*.*'},'Choose the input file');

delimiterIn = ',';

headerlinesIn = 0;

A = importdata([pathname filename],delimiterIn,headerlinesIn); %this is a long array of time stamps, just citing one in the example above.

start_time=A(:,1); %start_time(1)=[42005.014097222]; but the 1st element in the excel sheet is 1/1/2015 12:20:18 AM

stop_time=A(:,2);

[y1,m1,d1,h1,m1,s1]=datevec(datestr(start_time,31)); %the result here gives me y1=115 and d1=2; but the right result should be y1=2015 and d1=1.

[y2,m2,d2,h2,m2,s2]=datevec(datestr(stop_time,31));

Answer 1

[Ajay Kumar]

The default date for excel is 1899/12/30_00:00:00. So you have to add this to the date number to get the correct date.

Try this

t1 = datenum('1899/12/30_00:00:00','yyyy/mm/dd_HH:MM:SS');
datestr(t1+42005.014097222)

Please accept the answer if you got what you need. Thanks :)

[Danya Pradeep Kumar]

That worked! Thank you for your help, Kumar!

[Stephen23]

"The default date in matlab is 1899/12/30_00:00:00..."

In fact the datenum documentation states "A serial date number represents the whole and fractional number of days from a fixed, preset date (January 0, 0000) in the proleptic ISO calendar."

Excel stores dates as the number of days and fractional days since 0-January-1900 (or 1904), so actually the date given is the "default date" for Excel, not for MATLAB:

http://www.cpearson.com/excel/datetime.htm

https://support.microsoft.com/en-gb/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel

[Ajay Kumar]

My pleasure :)

[Danya Pradeep Kumar]

Thank you, Stephen! That makes sense.

[Ajay Kumar]

Corrected. Thanks for the info Stephen :)