Gratuity Rule in Single Excel formlae

[mannarkkadryd]

HI All,

Anyone please help to resolve the below gratuity calculation in single excel for an optional cell as 'Resignation' and 'Termination'

Resignation
" Eligible to receive 7 days of Basic Salary per year, if service period is more than 1 year and less than 3 years.
 Eligible to receive 14 Days of Basic Salary, if the service period is more than 3 years and less than 5 years.
 If the service is more than 5 years, then eligibility is;
o 21 days’ pay for each year of service until the completion of 5th year PLUS
o 30 days of pay for each additional year after the completion of 5th year. (It should be pro-rated for any uncompleted years)


Termination
" 21 days’ of Basic pay for each year of service until the completion of 5th year provided the employee has at least completed 1 (one) year of service.
 If the service is more than 5 years, then eligibility is ;
o 21 days’ pay for each year of service until the completion of 5th year PLUS
o 30 days of pay for each additional year after the completion of 5th year. (It should be pro-rated for any uncompleted years)"


Thanks in advance for your help...

[mannarkkadryd]

Hi,

Attached calculations in excel; if anyone wanted to help to make it in to single formulae.

Thanks