UAE Gratuity Calculation

[hjakhan]

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

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Glenn Kennedy]

Hi. I don't understand what you mean by this bit:

as well as if the person is on 6 years the first 5 have to be on 21 days.

[hjakhan]

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.

[Glenn Kennedy]

Hi there. You're right bonus calculation does take a long time. However, I was at the cinema for a few hours. Take a look at this. I have made a few assumptions - mainly that the bonuses are paid on the basis of 365 datys per year (as opposed to a smaller number of working days per year). If I'm wrong, please let me know...

[hjakhan]

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. :-)

[Glenn Kennedy]

I'll get back to you tomorrow.

[Glenn Kennedy]

...

So. In your example (above) the pay (3000) is per what week/month??

If you have 1 year, you get nothing. That I follow. If you work for 2 years do you get 2 sets of 21 days pay or only one (as you have only one year over the 1year/no bonus threshold?