Indemnity Calculation based on the conditions

Hi All,

Appreciate your kind help to find a solution based on the below;

This is to calculate service indemnity for the employees as per the below conditions;

1- If the employee completes Five years or less then the employee is eligible for 15 days of salary for each year. However calculation will be (Gross Salary/ 26 X <no.of days worked>/365 X 15)

2- If the employee completes more than Five years of service, then the employee is eligible for one month salary for each additional year (after 5) till the last anniversary (Eg: joining date: 1-Oct-2000 and end/termination date: 13-Jul-2011, then his last anniversary will be 30-Sep-2010). In that case calculation will be (Gross Salary X 5) which means after the completion of 5 years employee is eligible for one month salary for each additional year till the last anniversary.

3- From the last anniversary till his end date then the calculation will be (Gross Salary/26 X <difference days between the last anniversay till employment end date> / 360 X 26)

I need a formula which includes all the above conditions, i'm trying IF conditions but stuck in between. Hope you clear what i'm looking for.

For easy reference please find the attached worksheet.

Thanks & Regards,
Nawas

If you want actual whole years completed only in your calculations, use DATEDIF.

e.g.

=SUM((B7/26*DATEDIF(B3,B9,"y")*15),DATEDIF(B9,B5,"y")*B7,(B7/26*DATEDIF(B14,B5,"y")*26))

else if you are doing decimal years, then:

=SUM((B7/26*((B9-B3)/365)*15),((B5-B9)/365)*B7,(B7/26*((B5-B14)/365)*26))