Vacation Accrual Formula

Halosty

New Member
Joined
Aug 13, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I have been tasked with developing a spreadsheet to track employee vacation accrual. The company pays employees fortnightly so I would like the spreadsheet to show the days that each employee has earned.

Our vacation policy is:
1-4 years = 10 days (80 hours/year, 1 Day/ 22 Days )
5 - 10 years = 15 days (120 hours/year, 1.5 Days/ 22 Days )
10 years = 20 days (160 hours/year, 2 Days/ 22 Days)

So far I have the following information set up:
A2 Name
B2 Hire Date
C2 TODAY ()
D2 =(C2-B2) (calculates the number of days since the employee was hired)
E3 I would like this to show the number of hours each employee has available to date.

I would greatly appreciate any help.

Thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,661
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202008b.xlsm
ABCDE
1NameHiredTodayYearsAccrual days
21-Jan-1713-Aug-20310
31-Aug-1013-Aug-201020
3a
Cell Formulas
RangeFormula
C2:C3C2=TODAY()
D2:D3D2=DATEDIF(B2,C2,"y")
E2:E3E2=LOOKUP(D2,{0,10;5,15;10,20})
 

Halosty

New Member
Joined
Aug 13, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
T202008b.xlsm
ABCDE
1NameHiredTodayYearsAccrual days
21-Jan-1713-Aug-20310
31-Aug-1013-Aug-201020
3a
Cell Formulas
RangeFormula
C2:C3C2=TODAY()
D2:D3D2=DATEDIF(B2,C2,"y")
E2:E3E2=LOOKUP(D2,{0,10;5,15;10,20})

Updating D2 =NETWORKDAYS.INTL(C2,D2,1) ( This is the Network Days)
E2 = I would like this to show the accrual amount up to the balance up to the current date the employee has worked. There is a accrual cap each year.

Updated everything. Hope this clarifies it more. Thanks for your help. Your formula didn't take into consideration that they only accrue a day for each 22 day worked then its capped until the work year as passed. Then it rolls over to the new year
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,661
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Provide a clear explanation of what you require.
Post a few examples with the expected results.
 

Halosty

New Member
Joined
Aug 13, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I need the accrued vacation rate to correctly multiple the net days worked to show Vacation Accrual.

Example.

Hire Date: 28/1/2013
Years of Service: 2 (So the Accrual Rate would be 1/22) (They Gain 1 day for every 22 days worked)
Separation Date: 28/2/2014

This would come to 285 Net Days Worked. Using the Accrual Formula it would be (220 * (1/22)) + ((285-262)+(1/22))

262 Is the Yearly Network days. So they only start to accrue vacation balance after a year of net work days so accrual gained in capped per year.

The formulas I have don't contain the functions to cap the accrual for each year so I am hoping someone can help.

1-4 Years: =IF((AND(E2<1310)),E2*$M$2,IF((OR(F2>=1&F2<5)),50,50))
5-9 Years: =IF((AND(E2>=1100,E2<2200)),(E2-1100)*$N$2,IF((OR(F2>5)),75,0))
10 Years: =IF((OR(E2>=2200)),(E2-2200)*$O$2,0)

This is what I have

Vacation Balance.xlsx
ABCDEFGHIJKLMN
1NAMESHire DateSeparation DateDaysYearsEligibleEntitlement Days1-4 Years5-9 Years10 YearsAccrual1 -4 years5- 9 Years10 Years2
2Employee 128/1/201328/2/20142851Yes1012.950.000.000.050.0454545450.0681818180.090909091
3Employee 228/1/201314/8/202019707Yes1550.0059.320.000.070.050.070.09
Sheet1
Cell Formulas
RangeFormula
D2:D3D2=NETWORKDAYS.INTL(B2,C2,1)
E2:E3E2=DATEDIF(B2,C2,"Y")
F2:F3F2=IF(D2>=110,"Yes","No")
G2:G3G2=10+LOOKUP(E2,{0,1,2,3,4,5,6,7,8,9,10},{-10,0,0,0,0,5,5,5,5,5,10,10})
H2:H3H2=IF((AND(D2<1310)),D2*$L$2,IF((OR(E2>=1&E2<5)),50,50))
I2:I3I2=IF((AND(D2>=1100,D2<2200)),(D2-1100)*$M$2,IF((OR(E2>5)),75,0))
J2:J3J2=IF((OR(D2>=2200)),(D2-2200)*$N$2,0)
K2:K3K2=IF((D2)/262<5,(1/22),IF((D2)/262<10,(1.5/22),IF((D2)/262>=10,(2/22),(2/22))))
L2:L3L2=10/220
M2:M3M2=15/220
N2:N3N2=20/220
C3C3=TODAY()


I hope I explained it well enough.

Thanks again.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,661
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The commentary on how the Vacation Accrual is calculated was not complete.
- If part days are not accrued , use Int and/or rounding as required.
- helper columns,Vlookup for the accumulated amounts, etc. could make the formula more concise
- You can name the lookup information; see I5

T202008b.xlsm
ABCDEFI
1NAMESHire DateAnnual DateSeparation DatePart Year/22YearsVacation Accrual
2Employee 128-Jan-1328-Jan-1428-Feb-141.09111.09
3Employee 228-Jan-1328-Jan-2016-Aug-206.55794.82
4Employee 31-Jan-001-Jan-2016-Aug-207.4120344.82
5Employee 41-Jan-001-Jan-2012-Nov-2010.3220350.00
6
3aa
Cell Formulas
RangeFormula
E2:E5E2=NETWORKDAYS(C2,D2,1)/22
F2:F5F2=DATEDIF(B2,D2,"Y")
I2:I4I2=(F2>0)*(MIN(F2,4)*10)+(F2>4)*((MIN(F2,10)-4)*15)+(F2>10)*(F2-10)*20+MIN(E2*VLOOKUP(F2,{0,1,10;5,1.5,15;11,2,20},2),VLOOKUP(F2,{0,1,10;5,1.5,15;11,2,20},3))
I5I5=(F5>0)*(MIN(F5,4)*10)+(F5>4)*((MIN(F5,10)-4)*15)+(F5>10)*(F5-10)*20+MIN(E5*VLOOKUP(F5,aRngL,2),VLOOKUP(F5,aRngL,3))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,661
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202008b.xlsm
ABCDEFGI
1NAMESHire DateAnnual DateSeparation DatePart Year/22YearsVacation Accrual
2Employee 128-Jan-1328-Jan-1428-Feb-141.09111.09
3Employee 228-Jan-1328-Jan-2016-Aug-206.55794.82
4Employee 31-Jan-001-Jan-2016-Aug-207.4120344.82
5Employee 41-Jan-001-Jan-2012-Nov-2010.3220350.00
3aa
Cell Formulas
RangeFormula
E2:E5E2=NETWORKDAYS(C2,D2,1)/22
F2:F5F2=DATEDIF(B2,D2,"Y")
I2:I5I2=SUMPRODUCT(--(F2>rB),F2-rB,rD)+MIN(E2*VLOOKUP(F2,aRngL,2),VLOOKUP(F2,aRngL,3))
Named Ranges
NameRefers ToCells
rB='3aa'!$J$2:$J$4I2:I5
rD='3aa'!$L$2:$L$4I2:I5
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

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