#Invalid Data Type Error for years in rank

Elizabeth Anderson

Trying to figure out what broke in a formula. Will you get this error if the cell is blank? For instance trying to figure out years in rank for a certain role in this example how many years from Assistant Professor to Professor

Bassam Khalil

Hi@Elizabeth Anderson

Could you check the type of the columns you used in the formula.

And could you please add a sample data with the result of the formula manually to help me to design the exact formula for you.

Elizabeth Anderson

@Bassam Khalil absolutely & thank you for your help! The columns are set up with the Date Field:

Additionally the formula is: =YEAR([Assistant Professor]@row) - YEAR(Instructor@row)

I guess I was thinking if the cell is empty because they never held that rank the formula would default to "0" instead?

Thanks,

Liz

Bassam Khalil

@Elizabeth Anderson 

Please try the following formula:

=IFERROR(IF(OR(ISBLANK([Assistant Professor]@row), ISBLANK(Instructor@row)), "", YEAR([Assistant Professor]@row) - YEAR(Instructor@row)), "")

Elizabeth Anderson

@Bassam Khalil thank you! Do I need to select the select the columns or copy/paste into the cell should work? I copy/pasted into the cell but the cell is now blank. I'm sure Im doing something wrong.

Elizabeth Anderson

@Bassam Khalil sorry we are confused as the formula we previously had of: =YEAR([Associate Professor]@row) - YEAR([Assistant Professor]@row) seems to be working for some cells but not all- something must have broke along the way. See Screenshot:

Bassam Khalil

@Elizabeth Anderson

My formula solve the problem of blank cells that cause "invalid data type" error but you need to use the same column name or you select your column name and modify my formula to match.