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

02/11/21
Answered - Pending Review

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)

Answers

  • 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?

  • 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([email protected]), DAY([email protected]))


  • Ahmed ElmonoufyAhmed 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()[email protected])/365)

Sign In or Register to comment.