# Pro-rated leave for range

#### ambz123

##### Board Regular
Hi,

I am task to create a leave management that auto calculates the pro-rated leave entitlement based on the following condition.

If the employee is Manager he will be entitled to 16 days of annual leave but it has to be pro-rated in accordance to his join date and less than 2 years is 16 days, 2 years but less than 5years of service is 18 days, 5 years and above is 20 days.

While if the employee is Executive he will be entitled to 14 days of annual leave but same thing, the 14 days will be pro-rated in accordance to his join date when he first join the company. Less than 2 years is 14 days, 2 years but less than 5years of service is 16 days, 5 years and above is 18 days.

Need some help in this.

Thank you.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

##### Well-known Member
If your Job code (Type) is in F column (first F2) & Month Joined is in Clumn G (first G2) write this and drag it down:
Excel Formula:
``=IF(F2="M",IF(G2<=24,16,IF(G2<=60,18,20)),IF(G2<=24,14,IF(G2<=60,16,18)))``

#### ambz123

##### Board Regular
Hi if i have more than 1 job code that formula wont work correctly

##### Well-known Member

If you have Word "Manager" in all of your job code for Managers, Use This:
Excel Formula:
``=IF(RIGHT(E2,LEN(E2)-FIND(" ",E2,1))="Manager",IF(G2<=24,16,IF(G2<=60,18,20)),IF(G2<=24,14,IF(G2<=60,16,18)))``

Last edited:

#### ambz123

##### Board Regular
my Job Grades consist of the word "Manager" , "Executive", "Technician", "Foreign Worker"
Each of them have different leave entitlement based on their Job grade and length of services as follow:

Manager : <2 = 16, 2≤5 = 18, >5 = 20
Executive: <2 = 14, 2≤5 = 16, >5 = 18
Technician: <2 = 10, 2≤5 = 12, >5 = 16
Foreign Worker: <2 = 8, 2≤5 = 10, >5 = 12

##### Well-known Member

Then what is Abbreviation at Job Grades and what they Means? How I can Use them.
I don't see exact of this word at Job Code OR Job Grade Columns.

#### ambz123

##### Board Regular
Hi for the Job Code i have assign codes for each position as follow:

For Manager = M
For Executive = ESO
For Technician = TFO
For Foreign Worker = FW

##### Well-known Member
Technician: <2 = 10, 2≤5 = 12, >5 = 16
I think Last one should be 14 at this part then Use this code:
Excel Formula:
``=IF(F2="M",IF(G2<=24,16,IF(G2<=60,18,20)),IF(F2="ESO",IF(G2<=24,14,IF(G2<=60,16,18)),IF(F2="TFO",IF(G2<=24,10,IF(G2<=60,12,14)),IF(G2<=24,8,IF(G2<=60,10,12)))))``
If Not Use this:
Excel Formula:
``=IF(F2="M",IF(G2<=24,16,IF(G2<=60,18,20)),IF(F2="ESO",IF(G2<=24,14,IF(G2<=60,16,18)),IF(F2="TFO",IF(G2<=24,10,IF(G2<=60,12,16)),IF(G2<=24,8,IF(G2<=60,10,12)))))``

#### ambz123

##### Board Regular
Another condition is if employee who works less than 3 months their leave entitled 0 after 3 months will be pro-rated accordingly to their join date.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,504
Messages
5,837,725
Members
430,512
Latest member
sciplants

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back