excel dates

I understand that matlab cannot read excel dates in the sense that it changes hh:mm into decimals. This being said, is there a way of importing the dates as a string so that they would remain the same as in excel? I've tried using importdata and xlsread but neither seem to be able to!

cheers

Answer 1

[Fangjun Jiang]

Use [Num,Txt,Raw]=xlsread() and then "Raw" will keep the "hh:mm" string format, then you can use datenum() to convert it.

If the format of the cells in Excel is "Time", it looks like the numerical value is imported using xlsread(). You can use datestr() to convert it back. For example:

>> [Num,Txt,Raw]=xlsread('test.xls')
Num =
    0.4743
    0.5160
    0.5576
    0.5576
Txt = 
    'Date'          'Time'
    '12/31/1899'        ''
    '1/1/1900'          ''
    '1/2/1900'          ''
    '1/3/1900'          ''
Raw = 
    'Date'          'Time'  
    '12/31/1899'    [0.4743]
    '1/1/1900'      [0.5160]
    '1/2/1900'      [0.5576]
    '1/3/1900'      [0.5576]
>> datestr(Num,'HH:MM')
ans =
11:23
12:23
13:23
13:23

[ricco]

This gives the date vector in decimals, the only difference is that it contains the header from excel as well.

[Fangjun Jiang]

Okay, I see your problem. See update.

[ricco]

that's great. How do I get to each of the time steps though? By 'get to' I mean say if

d=datestr(Num,'HH:MM')

d(1) would equal 11:23 instead at the moment d(1)=1 etc...

[Fangjun Jiang]

d is a char array, you could use d(1,:), or do a further conversion

e=cellstr(d); e{1}

Answer 2

[Walter Roberson]

Warning: Excel represents numeric time data with a different time base than MATLAB does. There is a toolbox function that does the correction, http://www.mathworks.com/help/toolbox/finance/x2mdate.html, but you can code it yourself using the information referenced in my note in http://www.mathworks.com/matlabcentral/answers/254-converting-excel-serial-number-of-time-to-hh-mm-ss-format

[ricco]

This is great thanks. However, is it possible in any way to do the following:

By using

S = DATESTR(D, 31)

Number String Example

31 'yyyy-mm-dd HH:MM:SS' 2000-03-01 15:45:17

Is it possible for matlab carry out an operation of producing a string which starts and ends at specific days with an interval of so many minutes.

For example, if I had data in Excel with a time vector starting at 2009-01-01 00:00:00 and finishing at 2009-10-10 10:00:00 with measurements taken every four minutes, is it possible to type this into matlab so that you could avoid loading the dates from excel? If I'm not clear please let me know.

[Walter Roberson]

datestr(datenum('2000-03-01 15:45:17','yyyy-mm-dd HH:MM:SS'):4/(60*24):datenum('2009-10-10 10:00:00','yyyy-mm-dd HH:MM:SS'),'yyyy-mm-dd HH:MM:SS')

[ricco]

awesome.