Total Gratuity days to pay

[Tia]

Dear All,

I am trying to calculate the end of service gratuity.
The Rules are as follows:
Employee:
If the employee resigns under total years of service from 1 till 3
years his gratuity pay will be 7 days for each year
For each working day
If the employee resigns under total years of service from 3 till
infinity years his gratuity pay will be 21 days for each year for each
working day

If the employers terminate the employee under total years of service
from 1 till infinity years his gratuity pay will be 21 days for each
year for each working day+ Ticket.

What I am looking for is a formula for F1 that allows me to get total
gratuity days to pay

A1: Employee Name
B1: Total Package per day
C1: Starting Date
D1: Total Years so far
E1: Termination/ Resignation
F1: Total Gratuity days to be paid


Thank you in advance

 

[Bernard Liengme]

Here is a start
=IF(E1="T",21,IF(D1<3,7,21))*D1
This assumes E1 can only be T or R

I am not sure what "ticket" means so you many need to modify this to
=IF(E1="T",21 + something, IF(D1<3,7,21))*D1

Also I am confused by the phrase "each working day" in the specification
"gratuity pay will be 7 days for each year for each working day". You may
need to adjust for this.
best wishes

 

[pogiman via OfficeKB.com]

I'm assuming that the no. of years of service is equivalent to "x" no. days
pay and that a zero (0) years of service may be present (e.g. <1yr).


F1 =if(and(E1="Termination",D1>=1),text((B1D1*21),"#,#00.00")&" + "&"Ticket",
if(and(E1="Resignation",D1>=1,D1<=3),B1*D1*7,if(and(E1="Resignation",D1>3),
B1*D1*21,"Service is less than 1 year")))

Just add the column for the rate/day

Hope this work...