Reading Time/Dates from Excel

168 次查看（过去 30 天）
Sarah
Sarah 2012-1-3
评论： Walter Roberson 2020-8-21
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.

请先登录，再回答此问题。

采纳的回答

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

请先登录，再进行评论。

更多回答（5 个）

Sean de Wolski
Sean de Wolski 2012-1-3
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 个评论
显示 1更早的评论
Sean de Wolski
Sean de Wolski 2012-1-3
Can you provide a small example of the cell array?
Sarah
Sarah 2012-1-3
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...

请先登录，再进行评论。

Sarah
Sarah 2012-1-3
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?
  3 个评论
显示 1更早的评论
Sarah
Sarah 2012-1-3
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 2012-1-3
That solution shows using cellfun() to process the cell array of date strings.

请先登录，再进行评论。

Sarah
Sarah 2012-1-3
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 个评论
显示 4更早的评论
Sarah
Sarah 2012-1-3
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 2012-1-3
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
Sarah 2012-1-3
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 个评论
Dr. Seis
Dr. Seis 2012-1-3
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 2012-1-3
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!

请先登录，再进行评论。

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

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by