Years of Service formula

Marijan
Marijan ✭✭
edited 11/30/22 in Formulas and Functions

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

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    THREAD 1

    THREAD 2

    THREAD 3


    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Marijan
    Marijan ✭✭

    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).

  • Marijan
    Marijan ✭✭
    edited 04/20/20

    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...



  • Marijan
    Marijan ✭✭

    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 :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 02/11/21

    @Katy B. Try something like this...


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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome

    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!