Years of experience - simple formula needed
I need a simple way to calculate years of service using the upcoming date of 7/1/23 and the date of hire as start date.
Answers
-
@Cathy Betzer The most simple way to do it:
=(DATE(2023, 7, 1) - [Date of Hire]@row) / 365
That will give you number of whole years in the integer portion of the answer, and a fraction of the whole year in the decimal portion.
With a Hire Date of 8/1/2016, the above gives you 6.91781. But that's not exactly what you want, is it?
If you multiply the decimal portion by 365, you get the number of days represented by that decimal:
.91781 * 365 = 335
But you can skip the need to isolate the decimal portion for multiplication by using the MOD function:
=MOD((DATE(2023, 7, 1) - [Date of Hire]@row), 365)
which returns 335.
Divide 335 days by 30 days per month, you get 11.16667. So taking the integer portion of this, you could say this employee has been with the company for approximately 6 years, 11 months.
Tie it all together, using the INT function to just grab the integer portion of each calculation result:
=INT((DATE(2023, 7, 1) - [Date of Hire]@row) / 365) + " years, " + INT(MOD((DATE(2023, 7, 1) - [Date of Hire]@row), 365) / 30) + " months"
And to have it only run on rows where there's a date value in [Date of Hire]:
=IF(ISDATE([Date of Hire]@row), (INT((DATE(2023, 7, 1) - [Date of Hire]@row) / 365) + " years, " + INT(MOD((DATE(2023, 7, 1) - [Date of Hire]@row), 365) / 30) + " months"), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you. It appears that this is a complex formula. I did get something to work. Thank you for helping.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives