# Leave entitlement pro rated

#### ambz123

##### Board Regular
Hi All,

I am task to create a leave system to count the number of leave based on join date.
This is the formula im using
=IF(G9<3,0,IF(F9="M",IF(G9<24,16,IF(G9<=60,18,20)),IF(F9="ESO",IF(G9<24,14,IF(G9<=60,16,18)),IF(F9="TFO",IF(G9<24,10,IF(G9<=60,12,16)),IF(G9<24,8,IF(G9<=60,10,12))))))

Their entitlement is based on their position and length of services as shown in the attachment.
E.G Staff A position is Technician/Foreman/Office Staff = TFO, he work with us since 1 August 2019, his entitlement should be 10.5 .
In a Year we will giving them earn leave, so every 1 month he works he will earn 1 day leave and max is 10.5 for the entire year.
And when he work 2 years but less than 5 years he will be entitled to 16 days the next year.

Can i ask if there is any way to formula it to achieve such condition?

#### Attachments

• Capture.PNG
12.6 KB · Views: 21

#### Dave Patton

##### Well-known Member
We require clear information in order to help.
It is not productive to guess.
At a minimum, you could have answered the questions.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### ambz123

##### Board Regular
The following example his Job Grade is TFO, and his 1st year entitlement starts with 10 days, while 2 years but less than 5 years his entitlement is 12 days.

If an employee joint the firm for 2 years and above we will count their leave based on the follow:

Staff Join on 1 Aug 2019

We will take in 1 Aug 2019 to July 2020 = 1st Year of work
2nd year onwards we will take in from Aug 20 to July 21 = 2nd year of work

Jan 2021 to July 2021 = 7 months/12*10 =5.83 (Multiple 10 is because from Jan 21 - Jul 21 is his 1st Year)
Aug 2021 to Dec 2021 = 5 months/12*12 =4.99 (Multiple 12 is because from Aug 21 to Dec 21 is his 2nd Year)
Next add up 5.83 + 4.99 = 10.82 (Round down to 10.5 days AL)

For instance the staff join in at mid month e.g 17 Aug 2019 we will straight add 0.5 into his leave entitlement first
For Staff join in less than 1 year just give entitlement based on job grade entitlement

Hope this clarifies

Replies
1
Views
133
Replies
13
Views
369
Replies
0
Views
133
Replies
23
Views
867
Replies
6
Views
647

1,130,087
Messages
5,640,028
Members
417,122
Latest member
kirk5370

### 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