Reading Time/Dates from Excel

Hey guys,

I have a problem using the xlsread function. In my excel spreadsheet, my dates are given in the following format:

2010-11-9 (November 9th, 2011)

However, when I use the following code:

[T1.Sig, T1.TStr] = xlsread('2011FIELD.xls',3,'A1:B42292');     
T1.TNum = datenum(T1.TStr);                                                    
T1.TVec = datevec(T1.TNum);

MATLAB reads my dates in the following format:

11/9/2010

Then, when I process the code, I get the following error:

Error in ==> readfield2011 at 10 T2.TNum = datenum(T2.TStr); %Converts date string into numerical format

Caused by: Error using ==> dtstr2dtvecmx Failed on converting date string to date number.

Can someone help me understand what to do at this point? I have tried several things, but I still get the same error.

PS: In Excel, if I have the date in one cell (A1), and the time in another cell (B1), is there any way I can merge the two cells together and combine both information into a single cell? Thanks for all the help.

Answer 1

[Dr. Seis]

I tried creating my own XLS document with date info in column A and time info in column B:

11/9/2010  9:00:00
11/10/2010  10:00:00
11/11/2010  11:00:00
11/12/2010  12:00:00
11/13/2010  13:00:00
11/14/2010  14:00:00
11/15/2010  15:00:00

I then tried reading in this information:

[Sig, TStr, Raw] = xlsread('Book1.xls',1,'A1:B7');

I then get this as a result:

>> Sig

Sig =

   1.0e+04 *

    4.0491    0.0000
    4.0492    0.0000
    4.0493    0.0000
    4.0494    0.0001
    4.0495    0.0001
    4.0496    0.0001
    4.0497    0.0001

>> TStr

TStr = 

     {}

>> Raw

Raw = 

    [40491]    [0.3750]
    [40492]    [0.4167]
    [40493]    [0.4583]
    [40494]    [0.5000]
    [40495]    [0.5417]
    [40496]    [0.5833]
    [40497]    [0.6250]

So running datenum on TStr would not work. Why can't you use the date information in Sig?

It looks like the date info is number of days since 12/30/1899, so a simple fix for Matlab to understand would be to:

>> DateTime = sum(Sig,2)

DateTime =

   1.0e+04 *

    4.0491
    4.0492
    4.0493
    4.0495
    4.0496
    4.0497
    4.0498

>> datestr(DateTime+datenum('30-Dec-1899'))

ans =

09-Nov-2010 09:00:00
10-Nov-2010 10:00:00
11-Nov-2010 11:00:00
12-Nov-2010 12:00:00
13-Nov-2010 13:00:00
14-Nov-2010 14:00:00
15-Nov-2010 15:00:00

[Robert Welsh]

Correct. 1900-01-01 is day 1.

[Walter Roberson]

That would be excel numbering and generally has a bug in dates March 1 1990 onwards.

datetime with 'convertfrom' 'excel' can map the days

Answer 2

[Sean de Wolski]

How is the date stored in your structure T? When I run:

datenum('11/9/2010')

the conversion works:

ans =
    734451

Place a breakpoint on line 10 of readfield2011 and see what T2.Tstr is.

More Copying your data:

A = {
'11/9/2010' 
'11/9/2010' 
'11/9/2010' 
'11/9/2010' 
'11/9/2010' 
'11/9/2010' 
'11/9/2010' };

datenum(A)

%{
ans =

      734451
      734451
      734451
      734451
      734451
      734451
      734451}

[Sarah]

I am sorry, the error pertains to T1.TStr, not T2. But it is the same code...just my mistake. Anyways, T1.Tstr is a cell array. It correctly scans all of the dates in the excel file and stores it in the format mentioned above. However, the datenum command seems to fail for some reason. I am not sure why that is?

One thing though, I changed my range from A1:B42292 to A1:B1. In essence, reading only one date. If that is the case, the code works properly. Any more than one entry for the date causes the error. Not sure if that is helpful or not...

[Sean de Wolski]

Can you provide a small example of the cell array?

[Sarah]

I just don't understand then...I don't know why my code is acting up. Based on what you are showing me, it shouldnt...

Answer 3

[Sarah]

Based on this link:

http://www.mathworks.com/support/solutions/en/data/1-7FVIEF/index.html?product=ML

It seems that the ability to use datenum on a cell array of dates is not available. I think it would work if I included the time values, which I do have. The problem is, the time values are in a different column altogether...which means I have to read them as separate variables into MATLAB...unless I either combine them in Excel (which might be difficult to do) or I combine the data in MATLAB...which I think is much easier to do. Any suggestions?

[Walter Roberson]

Hmmm, the documentation specifically says it can be done, so this must be a datenum bug. This explains why some people were having trouble with some code I had proposed before.

[Sarah]

perhaps? It doesnt make sense to me that datenum needs a time value in addition to the date value in order to successfully convert...and sean clearly showed earlier that it is possible. I am not sure what the solution is at all at this point.

[Walter Roberson]

That solution shows using cellfun() to process the cell array of date strings.

Answer 4

[Sarah]

Interesting, Elige. Check this out, here is my data in excel:

Column A
      2010-11-9      
      2010-11-9
      2010-11-9
      2010-11-9
      2010-11-9
Column B
1:41:32 PM
1:45:13 PM
1:50:13 PM
1:55:13 PM
2:00:13 PM

When I use:

[Sig, TStr, Raw] = xlsread('2011FIELD.xls',3,'A1:B42292');

I get the actual string values in TStr, unlike you. For Sig, I get the following values:

0.570509259259259
0.573067129629630
0.576539351851852
0.580011574074074
0.583483796296296

I am assuming that this represents the time that was originally in column B of the excel spreadsheet.

I think, at this point if I could just convert the Sig values (which are in double format) into string values that reflect the original time values from the spreadsheet, I can simply add Sig + TStr to get one final result. Do you think that is possible?

[Sarah]

If that is correct, then how can I convert the sig values into string format?

[Sarah]

Furthermore,

Is '1:41:32 PM' not a string? Why is MATLAB reading this from excel as a double? Why can't it just read it as a string?

[Dr. Seis]

Looks like the values in Sig are just the decimal day (i.e., 12:00:00 PM = 0.5). I wonder why Sig is not a 2 column matrix for you? What does "Raw" look like when you print that out?

[Dr. Seis]

In my xls file, the date info is formatted as "date" and the time info is formatted as "custom". If I convert those cells to text then it coverts the cells from date strings to integers for the date info and from time strings to floating point numbers for time info.

[Sarah]

Here is what raw looks like:

'3/10/2011' [ 0.5335]

'3/10/2011' [ 0.5349]

'3/10/2011' [ 0.5363]

'3/10/2011' [ 0.5377]

'3/10/2011' [ 0.5391]

[Dr. Seis]

What the... so "2010-11-9" converted to "3/10/2011"? I guess I would just convert the format of columns A and B from date/time/custom/whatever to text inside Excel (which should result in numbers like my post above), then read in the data like you did (there should be no string info), then you can sum the columns of Sig (as I do above), then add that value to datenum('30-Dec-1899') so Matlab understands it, then store that value as your T1.TNum. Maybe?

Answer 5

[Sarah]

Yeah I have NO idea about the 3/10/2011. Anyways, I got it until the datestr part, and then I got some more beautiful errors:

>> datestr(DateTime + datenum('30-Dec-1899'))

??? Subscript indices must either be real positive integers or logicals.

Error in ==> formatdate at 134 month = char(month(dtvector(:,2)));

Error in ==> dateformverify at 33 S = char(formatdate([y,mo,d,h,minute,s],dateformstr,islocal));

Error in ==> datestr at 198 S = dateformverify(dtnumber, dateformstr, islocal);

[Dr. Seis]

I did the datestr just to make sure that the numbers associated with "DateTime + datenum('30-Dec-1899')" are understood by Matlab correctly... it was just a quality check. However, I don't understand why it didn't work for you. Can you post the result of:

1. datestr(datenum('30-Dec-1899')) % Should be '30-Dec-1899'

2. datestr(DateTime)

3. DateTime

[Sarah]

Hey Elige, I think I got it! I am still not sure why datestr isn't working, but I used datevec to verify and it came out alright. I just plotted my data compared with the dates and I am now verifying just to be sure...but so far so good :) Thank you for all of your help. And thanks to the rest of you too!

Answer 6

[Robert Welsh]

Dates are read and reported as days since 1900-01-01, with 1900-01-01 as 1. You can do this by creating a spreadsheet with 1900/01/01, and xlsread will read that as as a numeric 1.