Importing Excel Data with Custom Data Format [hh]:mm:ss.00

My excel data contains a Custom format which includes milliseconds. It looks as follows: [hh]:mm:ss.00

How do I import this data using xlsread?

Answer 1

[Fangjun Jiang]

use [Num, Txt, Raw]=xlsread() to read the data. Use the data in Raw which is suppose to be the raw format. Then use the MATLAB function datenum() to process it.

Raw={'[12]:23:45.023','[13]:34:34.230'}
t=datenum(Raw,'[HH]:MM:SS.FFF')

Answer 2

[bym]

dt = xlsread('testdate.xls'); %sample ->978378:58:10.340

mdates = datenum('30-Dec-1899')+dt;

datestr(mdates,'dd-mm-yy HH:MM:SS.FFF')

Answer 3

[George]

Thanks for the responses, but I am still having issues with the results.

For example, my Excel data shows 00:11:41.49 and when exported by xlsread the answer becomes 0.0081.

When I format the Excel cell, the result becomes 0.008119101. I want the result to be 00:11:41.49 in Matlab.

Thanks again.

[Fangjun Jiang]

You need to add a single quote symbol in front the data in Excel to tell Excel that you intend to keep the numerical data as text. For example, typing in '00:11:41.49 in a cell of the Excel sheet will keep it that way. You can use Concatenate technique in Excel to add the single quote to all of your data. Once that is done, you can read it in using xlsread but then you'll have to remove those single quotes. You can sue strrep().

http://www.mathworks.com/matlabcentral/answers/12413-writing-time-data-into-excel

[bym]

I don't understand your issue:

datestr(.008119101,'HH:MM:SS.FFF')

ans =

00:11:41.490 % ??

[Fangjun Jiang]

@proecsm, I guess he was not expecting the automatic conversion when using xlsread(), and also not expecting the smart formatting of Excel.

[bym]

@Fangjun, maybe not, but it seems the urgency of finding a solution has gone away...