Annual Leave Entitlement forumla

Brose8

New Member
Joined
Oct 9, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello!
I am trying to automate entitlement every year based on my employer.

Starting leave is 20 days.
Following 2 yrs of employment - increase from 20 to 22 days
Following 3 yrs of employment - increase from 22 to 24 days.
Each year following - increase by 1 day up to a max of 30 total annual leave days.

Entitlement runs Jan- Dec.
Based on when contract started- if start date was Jan 1- June 30 you receive full allocation.
If start date was July 1-Dec 31, you receive 50% of the increase.

Many thanks for your help!
 
You're welcome, thanks for the feedback.
As I was doing this for all employees, I realised it does not take into account half days - so for example - Employee started on 4th Nov 2019, so they are entitled to 24.5 days as they started after June 30th, but calculation puts it as 24 days. I'm not sure if this can be taken into account in the same formula.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Does that mean you need to capture the difference in years as 3.5 for example?

Might be easier to come up with some kind of lookup table, something like the below, i will be able to work with that:
Book1
AB
1Years ServiceHoliday
2020
3120
4222
52.523
6324
73.524.5
8425
9etc.etc.
Sheet1
 
Upvote 0
Does that mean you need to capture the difference in years as 3.5 for example?

Might be easier to come up with some kind of lookup table, something like the below, i will be able to work with that:
Book1
AB
1Years ServiceHoliday
2020
3120
4222
52.523
6324
73.524.5
8425
9etc.etc.
Sheet1
Yes, this looks about right. The full entitlement is allowed from Jan 1 - June 30 for start dates. The half entitlement is Jul 1 -Dec 3 for start dates.
 
Upvote 0
The below works purely from their start date, if from their start date to today is 3.5 years then you will get the value from the table that is next to 3.5. It is not looking at specific months for the start date:
Book1
ABCDEFG
1NameStart DateNewYears ServiceHoliday
2Person 102/08/198730020
3Person 203/05/199930120
4Person 301/01/200130222
5Person 401/01/201727.5324
6Person 501/01/202024.53.524.5
7Person 601/11/202022425
8Person 701/01/2023204.525.5
9526
105.526.5
11627
126.527.5
13728
147.528.5
15829
168.529.5
17930
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),F2:F17,G2:G17,,-1))))
Dynamic array formulas.
 
Upvote 0
The below works purely from their start date, if from their start date to today is 3.5 years then you will get the value from the table that is next to 3.5. It is not looking at specific months for the start date:
Book1
ABCDEFG
1NameStart DateNewYears ServiceHoliday
2Person 102/08/198730020
3Person 203/05/199930120
4Person 301/01/200130222
5Person 401/01/201727.5324
6Person 501/01/202024.53.524.5
7Person 601/11/202022425
8Person 701/01/2023204.525.5
9526
105.526.5
11627
126.527.5
13728
147.528.5
15829
168.529.5
17930
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),F2:F17,G2:G17,,-1))))
Dynamic array formulas.
Sorry to be dumb - What is the 'holiday' vs 'new' column you're using? Thanks :)
 
Upvote 0
I just named the column New so you would see that it is a new formula.
 
Upvote 0
I just named the column New so you would see that it is a new formula.
Is there an easy formula to calculate the years of service column as well so this is automated based on the inputted start date and have it include half as well? i.e. 5.5 years

I was using =INT(YEARFRAC(C7,TODAY())), but this does not make some of them at 5.5 years, etc.
 
Upvote 0
Like this:
b.xlsx
ABCDEFGH
1NameStart DateYears serviceNewYears ServiceHoliday
2Person 102/08/198736.230020
3Person 203/05/199924.430120
4Person 301/01/200122.830222
5Person 401/01/20176.827.5324
6Person 501/01/20203.824.53.524.5
7Person 601/11/20202.922425
8Person 701/01/20230.8204.525.5
9526
105.526.5
11627
126.527.5
13728
147.528.5
15829
168.529.5
17930
Sheet2
Cell Formulas
RangeFormula
C2:C8C2=ROUND(DATEDIF(B2:B8,TODAY(),"m")/12,1)
D2:D8D2=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),G2:G17,H2:H17,,-1))))
Dynamic array formulas.
 
Upvote 0
Solution
Like this:
b.xlsx
ABCDEFGH
1NameStart DateYears serviceNewYears ServiceHoliday
2Person 102/08/198736.230020
3Person 203/05/199924.430120
4Person 301/01/200122.830222
5Person 401/01/20176.827.5324
6Person 501/01/20203.824.53.524.5
7Person 601/11/20202.922425
8Person 701/01/20230.8204.525.5
9526
105.526.5
11627
126.527.5
13728
147.528.5
15829
168.529.5
17930
Sheet2
Cell Formulas
RangeFormula
C2:C8C2=ROUND(DATEDIF(B2:B8,TODAY(),"m")/12,1)
D2:D8D2=BYROW(B2:B8,LAMBDA(x,LET( dd,DATEDIF(x,TODAY(),"m"), XLOOKUP(ROUND(dd/12,1),G2:G17,H2:H17,,-1))))
Dynamic array formulas.
Thank you so much for all the help!
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

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
Back
Top