Years of Service formula

Years of Service formula

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


  • Paul NewcomePaul 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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • Paul NewcomePaul 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.

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

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

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

Sign In or Register to comment.