Total Gratuity days to pay
 

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

Total Gratuity days to pay
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Tia" wrote in message
...
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

Total Gratuity days to pay
 

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(an d(E1="Resignation",D13),
B1*D1*21,"Service is less than 1 year")))

Just add the column for the rate/day

Hope this work...


Tia wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200908/1