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 5 his
gratuity will be 14 days
If the employee resigns from 5 years till infinity years his gratuity
pay will be 21 days for each year for each
working day
Total day Years Gratuity $ to be paid
150 1 7 1050
150 2 7 2100
150 3 14 6300
150 4 14 8400
150 5 21 15750
150 6 21 18900
150 7 21 22050


If the employers terminate the employee under total years of service
from 1 till 5 years his gratuity pay will be 21 days for each
year for each working day+ Ticket.
from 5 till infinity years his gratuity pay will be 30 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
Total Per Day Years Gratuity Total To be paid
150 1 21 3,150.00
150 2 21 6,300.00
150 3 21 9,450.00
150 4 21 12,600.00
150 5 21 15,750.00
150 6 30 27,000.00
150 7 30 31,500.00
150 8 30 36,000.00


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