UAE GRATUITY CALCULATION FORMULA

[samadkk1234]

HI,
please help to create formula to calculate GRATUITY FOR UAE
CONDITIONS
1. 21 DAYS SALARY OF BASIC SALARY FOR ONE YEAR UPTO 5 YEARS
2. 30 DAYS SALARY BASIC SALARY FOR ABOVE 5 YEAR , WHICH IS STARTING FROM FIFT YEAR ONWARDS

 

[Jeffrey Mahoney]

Please try this

Book4
	A	B	C	D	E	F	G
1	Employee #	Employee Name	Date Started	Date Completed	Monthly Salary	UAE Gratuity	Fractional Years
2	015	Fred Flintstone	2015/02/01	2023/07/03	AED2,400.00	AED8,880.00	8.4
3	016	Barney Rubble	2018/03/15	2023/07/03	AED2,200.00	AED1,540.00	5.3
4	017	Rocky Balboa	2017/01/01	2022/03/01	AED3,300.00	AED2,310.00	5.2
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LET(YrFrac,YEARFRAC(C2,D2),IFS(YrFrac<1,0,AND(YrFrac>0.9999,YrFrac<5),(E2/30)*21,YrFrac>4.999,((E2/30)*21)+INT(YrFrac-5)*E2))
G2:G4	G2	=YEARFRAC(C2,D2)

 

[samadkk1234]

hi,
thanks you for your reply,

here you round off year ( no need to round off , take actual figure)
GRATUITY FOR FIRST 5 YEARS - 21 DAYS SALARY *5 YEARS
GRATUITY FOR REMIANING YEARS - 30 DAYS SALARY* REMAINING YEARS

 

[Jeffrey Mahoney]

I'm not sure what you are saying. Did you try the example?