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)
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(Date@row), DAY(Date@row))
-
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)
-
Do you have a formula for the anniversary notify like for 5,10,15 years in the company?
-
I try this formula but its not working.
=IF(OR(Anniversary@row = 5, Anniversary@row = 10, Anniversary@row = 15), Anniversary@row, "")
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives