Undefined operator '-' for input arguments of type 'cell'.

1 次查看(过去 30 天)
Hello,
I am trying to subtract two columns (that are in time domain) in spread sheet and create a new column. I attached the .xlsx file for your reference.
T = readtable('T.xlsx');
T.Time=T.BIRTHDT-T.INFODT;
But I am getting an error message: Undefined operator '-' for input arguments of type 'cell'.
I presume either the format of the two columbs might be different or one column is missing the hours/min/seconds information. But not sure how to sort.
Any help would be highly appreciated.

采纳的回答

Star Strider
Star Strider 2020-9-15
Try this:
T = readtable('FindDifference.xlsx');
T.INFODT = datetime(T.INFODT);
T.BIRTHDT = datetime(T.BIRTHDT);
T.Time = years(T.BIRTHDT-T.INFODT);
This puts ‘T.Time’ in units of years. Other units are possible. See the documentation for more information.
  10 个评论
Walter Roberson
Walter Roberson 2020-9-15
Note: years(2000) is 2000 fixed-length years. You should be adding calyears(2000) for calendar years.

请先登录,再进行评论。

更多回答(1 个)

Walter Roberson
Walter Roberson 2020-9-15
filename = 'FindDifference.xlsx';
opt = detectImportOptions(filename);
opt = setvaropts(opt, {'BIRTHDT', 'INFODT'}, 'Type', 'datetime');
T.Time=T.BIRTHDT-T.INFODT;
T.Format = 'y';
However, I think you will be rather startled at the results.
The INFODT column has hard-coded into it years such as 0011 . This is not just a mistake of interpretation of numeric values: the column is a text column, not a date column or a numeric column (I checked in Excel.) If someone deliberately wanted to code in year 11 CE then that is probably how they would code it.
I would suggest that probably before doing the subtraction, you should have
T.INFODT = T.INFODT + calyears(1900);
  3 个评论
Walter Roberson
Walter Roberson 2020-9-15
Which MATLAB release are you using? When I try in R2020a (on Mac), the dates associated with INFODT did not get written in the format of the original file you posted. Instead, the INFODT column in T got written as an Excel date with custom format . The original file you posted has, for example, 01-Feb-0011 00:00:00 as a pure text column.
Vijay
Vijay 2020-9-15
编辑:Vijay 2020-9-15
I have been using 2019b (Windows) which resulted in the format below.
3000 '01-Apr-0016 00:00:00' 0 '01-Jan-1959'
3000 '01-Feb-0011 00:00:00' 0 '01-Jan-1945'
3000 '01-Feb-0013 00:00:00' 0 '01-Jan-1948'
3000 '01-Feb-0018 00:00:00' 0 '01-Jan-1954'
Now, when I tried with 2020a the format became...
3000 'Feb-0011' 0 01-Jan-1941
3000 'Mar-0012' 0 01-Jan-1941
3000 'Feb-0013' 0 01-Jan-1941
3000 'Mar-0014' 0 01-Jan-1941
Not sure why the year is taken as 0011 instead of 2011.
In any case, still I can't subtract the column values.

请先登录,再进行评论。

类别

Help CenterFile Exchange 中查找有关 Calendar 的更多信息

Community Treasure Hunt

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

Start Hunting!

Translated by