Share via

formula to calculate a 5 year long service award pro-rata

Anonymous
2020-11-06T10:40:58+00:00

Hello

Please could you help me write a formula to calculate a 5 year long service award on an annual leave sheet.

Here is what I have at the moment

At the moment its not doing exactly what I want.

The formula working out 60 months is in F5 and is =DATEDIF(E5,A9,"m") - E5 is the start date A9 is todays date.

the formula working out 37:00 hours is =IF(F5>59,"37:00","") - it should say 15:25 - here's why.

If this staff member reaches 5 years service part way through the period, they are not entitled to five full days, they are only entitled to 5 months of that long service award which is 15 hours and 25 minutes.  (They accrue 3 hours and 05 minutes per month)

The way I Imagine its done is if we have a formula working out how many months it is from their 5 year anniversary, until the following 31/03/####, and then alter =IF(F5>59,"37:00","") to display the correct number of hours and minutes dependent on that figure? Or is there a better way of doing it?

(Of course the next 31/03/#### would need to be variable as staff join at different years)

Once they reach the next 1st April, they are then entitled to the 5 full days so the formula would need to stop counting at that point leaving them with 37:00

Hope this makes sense?

Here is the monthly accruement chart

Many thanks

Luke

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2020-11-09T13:29:43+00:00

    Hi Luke

    1- Period End Date formula

    cell A20=DATE(YEAR(A22)+1*(MONTH(A22)>3),3,31)

    2-Long service award formula

    cell A18 =IF(F5<60,37*TIME(1,0,0),A17*TIME(3,5,0))

    Notes:

    You missed to include in the dummy file the data in column P (range P6:P17) so I'm not 100% sure the formula above will give you the expected results.

    Check the cases in the pictures below and clarify the results in cell A18

    Case 1 More than 60 months from Starting Date

                   Period End Date 31/03/2020

    Case 2More than 60 months from Starting Date

                   Period End Date 31/03/2021

    Case 3Less than 60 months Starting Date

                   Period End Date 31/03/2022

    Other Formulas

    a) You could use the formula in cell F5  for Total Worked Month =DATEDIF(E5,TODAY(),"m")

    b) I hope with the changes implemented above, the formula in cell L5 as it is, give you the expected results. Come back to us and clarify if it not.

    Regards

    Jeovany

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-11-06T15:32:02+00:00

    Re; "... Perhaps a formula to display the next 31st of March"

    What do you mean by that?

    What 31st of March represents?

    Why it has to be that date?

    For two workers who started work on 15-January-2015 and 15-October-2015 respectively,

    Will you use the same date (31st of March)?

    Awaiting your answer

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-11-06T16:26:57+00:00

    Hi Jeovany

    Thank you for replying

    31st of March is the last day of the annual leave period before it is reset on April 1st.

    I have decided to use this - a17 is where the number of months is - P column is where the long service hours are.

    =IF(A17>11,P6,IF(A17>10,P7,IF(A17>9,P8,IF(A17>8,P9,IF(A17>7,P10,IF(A17>6,P11,IF(A17>5,P12,IF(A17>4,P13,IF(A17>3,P14,IF(A17>2,P15,IF(A17>1,P16,IF(A17>0,P17,""))))))))))))

    Very clunky I'm sure, but it enters the 3.05, 6.10 etc for me when i use =DATEDIF(A22,A20,"m") to work out the difference between their 5 year work anniversary, and the next 31st. It seems to be working quite well.

    The problem I am having right now it that i cannot figure out the formula to display the date for the next 31st of march.

    eg

    If cell a22 is 05/09/2020, i need a20 to display 31/03/2021

    if displays 09/07/1995 a20 would be 31/03/1996

    Hope that makes sense

    Thanks

    Luke

    0 comments
  3. Anonymous
    2020-11-06T14:39:53+00:00

    I'm sorry, I don't know how to make this work on my sheet.

    Perhaps a formula to display the next 31st of March, and then to count the months from the anniversary date to then, and then a vlookup to display the corresponding hours and minutes to that figure?

    0 comments
  4. Anonymous
    2020-11-06T12:27:00+00:00

    Hi Luke

    To streamline the formulas I'll need to understand better your scenario.

    This is the solution I offer you

    Try the following steps

    1- Give a "Named Range" to the "5 Years Service..." table

    2- Then try the formula for the award In cell F6

    =IF(F5>=60,IFERROR(VLOOKUP(DATEDIF(EDATE(E5,60),TODAY(),"m")+5,YearsServiceTable,2,FALSE),VLOOKUP(12,YearsServiceTable,2,FALSE)),"")

    Adapt the ranges in the formulas according to your scenario.

    I hope this helps you and gives a solution to your problem

    Do let me know if you need more help

    Regards

    Jeovany

    0 comments