UAE Gratuity Calculation

Hi,

I am in UAE and the gratuity calculation takes a long time.

to solve this i am attaching the law

which is

if less than an year than no gratuity
if 1 to 5 years its on 21 days Basic
if more than 5 years 30 days basic

the catch is that the remaining number of days have to be calculated as well, so the calculation takes place in Days. as well as if the person is on 6 years the first 5 have to be on 21 days.

I would be really grateful if someone can solve this for me preferably in an excel.

Best Regards,

Harris

Grat-UAE.xlsx

Thanks.

What i meant was if the length of service is lets say 7 years
then the first 5 years are on 21 days basic i.e. Basic/30 * 21

And the subsequent years will be 2 * full basic.

hope that clarifies the problem.

Thanks Glenn you are on the right track, it is on the basis of 365 days

i think i may have over complicated the problem to simplify what i meant is for example

If a person is less then 365 days the formula rules them out as a 0

If it is greater than 365 and less than 1825 it takes from (pay/30)*21 i.e 21 days

if it is greater than 1825 it takes (Pay) * 30 days

for an example

Pay = 3000
Date of Engagement = 1/2/2000
Date of leaving = 31/3/2014

so the first 5 years he gets 3000/30 * 21 * 5 = 10500
Rest 9 Years he gets 3000*9= 27000
And the remaining days 58 he gets 3000/30*58 =5800
So his total becomes 43300.

I know its tricky but i want to bring it into a one cell formula....let me if that is even possible or not so i stop wasting your time and mine. :-)

To calculate gratuity in UAE using Excel, enter the number of years, months, days worked, and basic salary in columns A, B, C, and D, respectively. Use this formula in column E for gratuity calculation:


=IF(A2 < 1, 0, IF(A2 <= 5, ((A2 * 365 + B2 * 30 + C2) / 365) * (D2 / 30) * 21, (5 * 21 * (D2 / 30)) + ((A2 - 5 + B2 / 12 + C2 / 365) * 30 * (D2 / 30))))
This formula accounts for different gratuity rates for years worked (21 days for 1-5 years, 30 days for more than 5 years).