# Fourmula for calculation of End of service

I Need calculation of End of service ("L" Column). You may refer the attached sheet to better understand my query.
Example: employee number A-1 service is 9.5 years with salary of  5,900. then he will get first five year half salary (5*(5900)/2) and remaining years he will get one salary (4.5*5900)
Payroll-formula---Copy.xlsx
###### Who is Participating?

Commented:
Without optimization:
``````=(YEARFRAC(D7,"31 jan 2018")-IF(YEARFRAC(D7,"31 jan 2018")>5,YEARFRAC(D7,"31 jan 2018")-5,0))*K7/2+IF(YEARFRAC(D7,"31 jan 2018")>0,K7*IF(YEARFRAC(D7,"31 jan 2018")>5,YEARFRAC(D7,"31 jan 2018")-5,0),0)
``````
1

Distinguished Expert - 2017Commented:
Hi Ashraf,

Please try below formula in L7 and drag down:
``````=IF(P7>5,((K7/2)*5+(P7-5)*K7),((K7/2)*P7))
``````
In attached I have above formula in Col N.
Ashraf_Payroll-formula---Copy.xlsx
1

Author Commented:
Thanks all.....
@ als315...  your formula meet my requirement in a great  way.
0

Commented:
You are welcome. Do not forget to close question
0

Distinguished Expert - 2017Commented:
No comment has been added to this question in more than 14 days, and OP confirmed provided solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.