How do I calculate the number of years between a date and today?

I have a Smartsheet tracking our team member work anniversary dates so that supervisors get alerted prior to a work anniversary occurring and then can congratulate that team member on the day of.

I currently have:

A hire date column: Brand Hire Date

Anniversary date column (for the current year): Anniversary Date

And tenure column: Tenure

But my formula's are not working properly!

This is the formula I am using to give me their next anniversary date: =IF(DATE(YEAR(TODAY()), MONTH([Brand Hire Date]86), DAY([Brand Hire Date]86)) > TODAY(), DATE(YEAR(TODAY()), MONTH([Brand Hire Date]86), DAY([Brand Hire Date]86)), DATE(YEAR(TODAY()) + 1, MONTH([Brand Hire Date]86), DAY([Brand Hire Date]86)))

Formula for tenure: =ROUNDUP(NETDAYS([Brand Hire Date]86, TODAY()) / 365)

Find more posts tagged with

Human Resources

Accepted answers

All comments

Katy B.

On the same sheet, I also have employee birthdays - the month and year. At present I go in and manually change the current year to the next year - is there a way to automate that? Perhaps using their hire date year for the birthday and an annual reminder?

Mark S Krebs

Heres one way to do this using helper columns. Then use date automation to send X days before the helper date. Caution on the today() function. It only updates when a sheet is accessed. I've had to do daily helper automations to do something like lock and then unlock a row to make sure the today function updates everyday.

=DATE(YEAR(Today#), MONTH(Date@row), DAY(Date@row))

Ahmed Elmonoufy

Here is how i have done it, I had a complex formula in past using helper columns and have simplified to this

=INT((TODAY()-BrandHireDate@row)/365)

Yanie

Do you have a formula for the anniversary notify like for 5,10,15 years in the company?

Yanie

I try this formula but its not working.

=IF(OR(Anniversary@row = 5, Anniversary@row = 10, Anniversary@row = 15), Anniversary@row, "")

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.