Clear Filters
Clear Filters

Reading Time/Dates from Excel

112 views (last 30 days)
Sarah
Sarah on 3 Jan 2012
Commented: Walter Roberson on 21 Aug 2020
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.

Accepted Answer

Dr. Seis
Dr. Seis on 3 Jan 2012
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
  2 Comments
Robert Welsh
Robert Welsh on 21 Aug 2020
Correct. 1900-01-01 is day 1.
Walter Roberson
Walter Roberson on 21 Aug 2020
That would be excel numbering and generally has a bug in dates March 1 1990 onwards.
datetime with 'convertfrom' 'excel' can map the days

Sign in to comment.

More Answers (5)

Sean de Wolski
Sean de Wolski on 3 Jan 2012
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}
  3 Comments
Sean de Wolski
Sean de Wolski on 3 Jan 2012
Can you provide a small example of the cell array?
Sarah
Sarah on 3 Jan 2012
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...

Sign in to comment.


Sarah
Sarah on 3 Jan 2012
Based on this link:
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?
  3 Comments
Sarah
Sarah on 3 Jan 2012
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
Walter Roberson on 3 Jan 2012
That solution shows using cellfun() to process the cell array of date strings.

Sign in to comment.


Sarah
Sarah on 3 Jan 2012
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?
  6 Comments
Sarah
Sarah on 3 Jan 2012
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
Dr. Seis on 3 Jan 2012
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?

Sign in to comment.


Sarah
Sarah on 3 Jan 2012
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);
  2 Comments
Dr. Seis
Dr. Seis on 3 Jan 2012
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
Sarah on 3 Jan 2012
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!

Sign in to comment.


Robert Welsh
Robert Welsh on 21 Aug 2020
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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!