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
-
=DATE(YEAR([Commencement Date]@row) + ROUNDDOWN((MONTH([Commencement Date]@row) + [ML mm]@row) / 12, 0) + IF(IF(MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12) = 0, 12, MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12)) = 12, -1), IF(MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12) = 0, 12, MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12)), DAY([Commencement Date]@row))
The above will only add the months for the Maternity Leave. To calculate the 5 year, 10 year, etc result, we will add that many years to the YEAR portion (note the bold portion).
=DATE(YEAR([Commencement Date]@row) + 5 + ROUNDOWN((.................................................
That is where you will put your 5, 10, 15, or 20 accordingly, and that should do the trick for you.
Answers
-
-
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.
-
How are you currently calculating the [Years of Service]?
-
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).
-
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...
-
=DATE(YEAR([Commencement Date]@row) + ROUNDDOWN((MONTH([Commencement Date]@row) + [ML mm]@row) / 12, 0) + IF(IF(MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12) = 0, 12, MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12)) = 12, -1), IF(MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12) = 0, 12, MOD(MONTH([Commencement Date]@row) + [ML mm]@row, 12)), DAY([Commencement Date]@row))
The above will only add the months for the Maternity Leave. To calculate the 5 year, 10 year, etc result, we will add that many years to the YEAR portion (note the bold portion).
=DATE(YEAR([Commencement Date]@row) + 5 + ROUNDOWN((.................................................
That is where you will put your 5, 10, 15, or 20 accordingly, and that should do the trick for you.
-
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 :)
-
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.
-
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!
-
@Katy B. Try something like this...
=YEAR(TODAY()) - YEAR([Hire Date]@row) + IF(YEARDAY(TODAY()) < YEARDAY([Hire Date]@row), 1)
-
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!
-
Try this...
=DATE(YEAR(TODAY()), MONTH(Birthday@row), DAY(Birthday@row))
-
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
Categories
Check out the Formula Handbook template!
