What formula can I use to calculate years/months/days of service?
Hi there experts-- I'm currently using this formula to calculate employee length of service, but it is adding the months and years together (so the total years are too high):
=ROUND(ROUND((NETDAYS([Hire Date]@row, Today@row) / 365) * 12) / 12) + "yrs " + MOD(ROUND((NETDAYS([Hire Date]@row, Today@row) / 365) * 12), 12) + "mo"
Is there a formula I can use that will show 1 yr 10 mo or 1 yr 9 mo 19days? Either would work as long as it isn't adding the extra year in.
Thanks!
Answers
-
Hi,
Here are two options; the first using the average number of days per month, the second by calculating the number of days in a year. I didn't use Netdays as that doesn't take into consideration weekends or holidays and may skew your number a bit.
=ROUND((((TODAY() - [Hire Date]@row) / 30.437)) / 12) + "yrs " + MOD(ROUND((((TODAY() - [Hire Date]@row) / 30.437)) / 12), 12) + "mo"
=ROUND((((TODAY() - [Hire Date]@row) / 365))) + "yrs " + MOD(ROUND((((TODAY() - [Hire Date]@row) / 365))), 12) + "mo"
Hope that helps!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!