# Years in a Rank?

Options

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?

• ✭✭✭✭✭✭
Options

What would be the expected outcome for the above example?

• Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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)"

• ✭✭✭✭✭✭
Options

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

• Options

Sorry the formula is set up for

text/ # column & we are still getting that error

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

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), "")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!