Reading Time/Dates from Excel
69 views (last 30 days)
Show older comments
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.
0 Comments
Accepted Answer
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
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
More Answers (5)
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
Sarah
on 3 Jan 2012
3 Comments
Walter Roberson
on 3 Jan 2012
That solution shows using cellfun() to process the cell array of date strings.
Sarah
on 3 Jan 2012
6 Comments
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?
Sarah
on 3 Jan 2012
2 Comments
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
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.
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!