Leave entitlement pro rated

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
    Capture.PNG
    12.6 KB · Views: 21

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,666
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
We require clear information in order to help.
It is not productive to guess.
At a minimum, you could have answered the questions.
Good luck on your project.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,143
Messages
5,640,384
Members
417,140
Latest member
whiteprose

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top