MATLAB Answers

N =B>=>2

Time data import from Excel

Asked by N =B>=>2
on 25 Apr 2013

I am importing data from Excel and one of the columns is with time stamps in the format 09:00:15 etc. I import it with XLS read, then I use datestr in order to convert the numbers in decimals Matlab gives me into proper time values. Then the resulting variable is char and I cannot use it, since I need it to be double. str2double does not do the job for me, it gives me NaN or Inf. Any suggestions?



No products are associated with this question.

1 Answer

Answer by per isakson
on 25 Apr 2013
Edited by per isakson
on 25 Apr 2013

then I use datestr in order to convert the numbers in decimals Matlab gives me into proper time values

does that mean something like

    str = datestr( numbers, 'HH:MM:SS' );

then you need

    num = datenum( str, 'HH:MM:SS' );

no I don't understand what you do. Why do you use datestr?


My timedate in Excel looks like 09:04:23. When I import it in Matlab, it looks like 40148. It is in a matrix with some other columns also. Then I use datestr:

Time = datestr(whatever, 'HH:MM:SS')

After this the data looks like 09:00:23, but it is char, and I cannot use it in my matrix with the other info, it has to be double. And this is where my problem is, I cannot convert char into double

I think "40148" is the number of days since 1900-01-01 or 1899-12-31

    >> datestr( (40148+datenum( 1900, 1, 1 )), 'yyyy-mm-dd HH:MM:SS' )
    ans =
    2009-12-03 00:00:00

The time information is lost? some other columns also is that there the time information hides?


Excel help says:

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0.  For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0.  The number 1 represents 1900-Jan-1.  It should be noted that the number 0 does not represent 1899-Dec-31.  It does not. If you use the MONTH function with the date 0, it will return January, not December.  Moreover, the YEAR function will return 1900, not 1899. 

Join the 15-year community celebration.

Play games and win prizes!

Learn more
Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

MATLAB Academy

New to MATLAB?

Learn MATLAB today!