Leave entitlement pro rated

[ambz123]

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?

 

[Dave Patton]

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.

 

[ambz123]

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