Years in a Rank?

Elizabeth Anderson

What formula would you use to count how many years someone has been in a rank. For example they are hired on 11/1/10 & became a professor on 10/31/11 but was a visiting professor from 11/1/10 to 10/31/11?

Paul Newcome

What would be the expected outcome for the above example?

Elizabeth Anderson

Hi Paul- to count the years in that rank- this is formula we had but it says #invalid data type.

=YEAR([Associate Professor]@row) - YEAR([Assistant Professor]@row)

Paul Newcome

Ok. Are you able to provide a screenshot for reference? And what is the desired outcome?

Based on that error, I would start with making sure both columns are date type columns.

Elizabeth Anderson

Hi Paul ,

Yes I do have them set up as "date Type" in the columns. Yes so for example this person was an associate professor from 10/31/11 until 8/31/15 when we was promoted to a Professor. So we would like a formula that calculates based on date how many years they were in that rank.

Thank you for your help!

Paul Newcome

How exactly are the dates being entered? What column type are you putting the formula in?

Elizabeth Anderson

Hi Paul they are entered in the Columns as "dates" with the formula of " =YEAR([Associate Professor]@row) - YEAR([Assistant Professor]@row)". Hopefully I am understanding & answering your inquiry correctly & we also have "=YEAR(Professor@row - [Hired Date]@row)"

Paul Newcome

The formula should be going into a text/number type column.

Elizabeth Anderson

Sorry the formula is set up for

text/ # column & we are still getting that error

Paul Newcome

In your most recent screenshot there is no date in the [Assistant Professor] column. Therefor there is no date for the YEAR function which will cause an error.

Elizabeth Anderson

Thanks Paul- yes we figured that out this morning haha we will get working on that to update the sheet. The formula was dragged down & so #'s do exist for some of the cells, appreciate your time and review.

Paul Newcome

If you want to set it as a column formula (to avoid having to dragfill) but want to "hide" the errors on rows without dates, you can wrap the whole thing in an IFERROR.

=IFERROR(YEAR([Associate Professor]@row) - YEAR([Assistant Professor]@row), "")