# Years of Service formula

Options
✭✭
edited 11/30/22

Hi, I have an employee data sheet with columns, Commencement Date, Years of Service, Maternity Leave Taken. How do I calculate their next Years of Service anniversary date which is acknowledged every 5 years (5, 10, 15, 20 etc) but then add on years and or months taken for Maternity Leave. Some staff have taken 6, 10, 12, 24 months (the 24 months references two periods of 12 months which I'm happy to group together). In general, the Mat Leave is taken within the first 5 years of service. I'm happy to split Mat Leave into two columns, one for years and another for months, or change just the one reflecting months but then that would throw-out the year +1 formula if the period taken is more than 1 or 2 years... Thanks

• ✭✭
Options
• ✭✭✭✭✭✭
Options

I have recently helped someone with a solution that added any number of months to a date. Let me do a little digging, and I will get back to you.

• ✭✭✭✭✭✭
Options

How are you currently calculating the [Years of Service]?

• ✭✭✭✭✭✭
Options

Above are links to 3 recent threads where we needed to add n months to a date. We should be able to modify this to account for your [Years of Service] and Maternity Leave.

• ✭✭
Options

Morning Paul, I was calculating years of service as follows, when I was using just one column [ML mm] for total months of Mat Leave taken.

=(((TODAY()) - [Commencement Date]3) / 365.8) - ([ML mm]3 / 12).

• ✭✭
edited 04/20/20
Options

Hi Paul, Thread 2 formula is not returning an error, however, its not calculating what I need which is [Commencement Date] plus 5 being the date result for the employees "5 Years of Service" less any Maternity Leave [ML mm] months taken. I'll then need to copy the formula into columns for 10, 15, 20 years etc for further Years of Service dates. I"ll try playing with this formula but I'm not really understanding it...

• ✭✭
Options

Hi Paul, Yes I surprised my self and managed to figure that out but it didn't work for all the lines and then I realised I myself has manually miscalculated the date! Thanks for your great work and quick responses - very much appreciated :)

• ✭✭✭✭✭✭
Options

Haha. I can't even begin to tell you how many times I got frustrated with a formula that "wasn't working" only to find out that it was an error on my own part. Ugh. I'm glad you were able to get that figured out.

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful which will let other people searching for a similar solution know that one might be found here.

• ✭✭
Options

Hi Paul!

I started a new thread on this, but taking a chance and dropping a comment here too. Do you have a formula for years of service without maternity leave or anything else? Looking for a straight - here is their hire date, and I want to know how many years they have been with us as of today.

I also want to be able to have alerts go out to team leaders on their hire date/anniversary each year.

Thanks!

• ✭✭✭✭✭✭
edited 02/11/21
Options

@Katy B. Try something like this...

=YEAR(TODAY()) - YEAR([Hire Date]@row) + IF(YEARDAY(TODAY()) < YEARDAY([Hire Date]@row), 1)

• ✭✭
Options

Thanks @Paul Newcome

Do you have a handy formula "in your pocket" for converting a birthdate to a current year date? At present I simply change the year manually.

Our HR person gives me a team members birth month and date when they on board. And I add that to the sheet.

Ex: in "Birthday" column

02/07/21

An alert pops for the supervisor saying "wish this person happy birthday." Then I have to go in and change the "21" in that column to 22 so the alert goes out next year.

Is there a way to generate an alert on that month and day every subsequent year? or a formula to auto update the month and day to the current year?

Thanks so much!

• ✭✭✭✭✭✭
Options

Try this...

=DATE(YEAR(TODAY()), MONTH(Birthday@row), DAY(Birthday@row))

• Options

How would you adjust this to also calculate months/days? I have a Hire Date Column, and then I'd like to be able to show tenure, but my formulas are adding the months and years together so each employee is showing an additional year of service. I tried this one and it is great, but I need the months/days as well. Thanks for any help you can offer!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!