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
Ashraf NomanAsked:
Who is Participating?
 
als315Commented:
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)

Open in new window

1
 
ShumsDistinguished 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))

Open in new window

In attached I have above formula in Col N.
Ashraf_Payroll-formula---Copy.xlsx
1
 
Ashraf NomanAuthor Commented:
Thanks all.....  
@ als315...  your formula meet my requirement in a great  way.
0
 
als315Commented:
You are welcome. Do not forget to close question
0
 
ShumsDistinguished 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.

All Courses

From novice to tech pro — start learning today.