Tenure - Years and Months Formula - help!

Hi All!

I know there have been many posts re: figuring out tenure, but I am super new to spreadsheets and some of the community posts keep losing me with the round ups, @rows etc,

I was wondering if someone might be able to explain with my own row examples - how to get years and months of tenure using 1. Start Date and 2. Today's Date and then 1. Start Date and 2. Termination Date.

I started to pick up on if the column is a date column - it may not work, the column has to be numer/ text. If that's the case- do you just manually update today's date, for everytime you need to figure out the tenure vs. setting the column as a date, and then in the column writing =today ?

Here are the columns as I have them labeled currently:

Find more posts tagged with

Accepted answers

All comments

TVang

Hi, @Ally Yeargan ,

This should help...

Smartsheet functions used...

NETDAYS(), returns the number of days between two dates. For example, "Start Date" and "Date of Departure".

TODAY(), returns today's date

ISBLANK(), used to check if the cell is blank

IF()

Other details...

365 days in a year

This is how to use NETDAYS()... NETDAYS( a_start_date , an_end_date )

For "a_start_date" you want to use "Start Date" if "Date of Rehire" is blank. Otherwise, use "Date of Rehire". The expression is..

IF(ISBLANK([Date of Rehire]@row), [Start Date]@row, [Date of Rehire]@row)

For "an_end_date" you want to use today's date, TODAY(), if "Date of Departure" is blank. Otherwise, use the date in "Date of Departure". Here's the expression...

IF(ISBLANK([Date of Departure]@row),TODAY(),[Date of Departure[@row)

Copy-paste into the NETDAYS() function...

NETDAYS(IF(ISBLANK([Date of Rehire]@row), [Start Date]@row, [Date of Rehire]@row) , IF(ISBLANK([Date of Departure]@row),TODAY(),[Date of Departure]@row))

However, NETDAYS() returns number of days, and you want tenure in years, so divide it by 365... NETDAYS()/365... and the complete formula is...

NETDAYS(IF(ISBLANK([Date of Rehire]@row), [Start Date]@row, [Date of Rehire]@row) , IF(ISBLANK([Date of Departure]@row),TODAY(),[Date of Departure]@row))/365

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.