# vacation accrual

#### sciencell

##### New Member
please help me, Mr. excel. Ok, I have been tasked with creating our PTO or vacation spreadsheet. our pay periods are twice per month the 15th and the end of the month. PTO accrues based on one-year of employment from January to December of a given calendar year. If employment commences mid-year, PTO will be prorated in accordance with the first year of employment and continue at the appropriate rate thereafter. For example, an employee who commences employment on April 1st 2020 will receive a proration of 40 vacation hours for his/her first nine months of employment in the 2017 calendar year (i.e. *30 hours prorated hours earned PTO for calendar year 2020). In 2021, the employee would receive the first three months (January, February, and March) at the 40 hour-per-year rate (*10 hours), and the remaining nine months (April, May, June, July, August, September, October, November, and December) at the 80 hour-per-year rate (*60 hours). you start accruing vacation the day you start. So how would my be for
below:
Length of Service Monthly accrual rate Annual PTO
First Year 3.33 hours 5 days (40 hours)
2nd year – 5th year 6.67 hours 10 days (80 hours)
6th year – 10th year 10 hours 15 days (120 hours)
11th year 10.67 hours 16 days (128 hours)
12th year 11.33 hours 17 days (136 hours)
13th year 12 hours 18 days (144 hours)
14th year 12.67 hours 19 days (152 hours)
15th year and beyond 13.33 hours 20 days (160 hours)

 Employee's Name: Christine DOH: April 1st 2020 Maximum Annual Hours 30 Used Hours = sum (January 1st to December 31st)0 Accrued Hours = ??? what does the formula look like here Roll Over Hours (from 2019) Unpaid Hours (deducted) = Net Available Hours = accrued hours minus used hours plus roll over

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
how do I find the right accrual formula

I created a Vacation sheet a few years ago you can adapt to your needs
I am working on removing personal data and I will post it shortly

VAC TEST.xlsx
ABCDEFGHIJKLM
1NameShiftEmp #Hire Date5 year Increase date10 year Increase date15 year Increase dateCurrent Vacation AccrualCurrent # Vac Hrs As of Date:# Additional Vac Hrs Earned by Anniversary# Overage Hours that Must be Used(=Approx # Days of Vacation)Must Use by (Payroll Date)
2Amount of Hours#######45.547.088.89-Oct-2001 Jan 20
3Location 1last month increase rate should reflect new rate51015
4Employee 1Night9001-Jan-151-Jan-201-Jan-251-Jan-305.54111.5933.2444.834.51-Jan-21
5Employee 2Night9011-Jan-161-Jan-211-Jan-261-Jan-31480.5724.004.570.51-Jan-21
6Employee 3Day9021-Jan-171-Jan-221-Jan-271-Jan-32480.5724.004.570.61-Jan-21
7Employee 4Day9031-Jan-181-Jan-231-Jan-281-Jan-33480.5724.004.570.61-Jan-21
8Employee 5Day9041-Jan-191-Jan-241-Jan-291-Jan-34480.5724.004.570.61-Jan-21
9Employee 6Day9051-Jan-201-Jan-251-Jan-301-Jan-35480.5724.004.570.61-Jan-21
10Employee 7Day9065-May-055-May-105-May-155-May-208.8177.26132.00199.2624.97-May-21
11Employee 8Day9076-May-056-May-106-May-156-May-208.8177.26132.00199.2624.97-May-21
12Employee 9Night9087-May-057-May-107-May-157-May-208.8177.26132.00199.2619.97-May-21
13Employee 10Night9096-Jun-086-Jun-136-Jun-186-Jun-237.08142.61127.44170.0517.018-Jun-21
14Employee 11Night9106-Jun-096-Jun-146-Jun-196-Jun-247.08142.61127.44170.0517.018-Jun-21
15Employee 12Night9116-Jun-106-Jun-156-Jun-206-Jun-257.08142.61127.44170.0517.018-Jun-21
16Employee 13Day9126-Jun-116-Jun-166-Jun-216-Jun-265.54111.5999.72111.3113.918-Jun-21
17Employee 14Night9136-Jun-126-Jun-176-Jun-226-Jun-275.54111.5999.72111.3111.118-Jun-21
18Employee 15Day9146-Jun-136-Jun-186-Jun-236-Jun-285.54111.5999.72111.3113.918-Jun-21
19Employee 16Day9156-Jun-146-Jun-196-Jun-246-Jun-295.54111.5999.72111.3113.918-Jun-21
20Employee 17Night9166-Jun-156-Jun-206-Jun-256-Jun-305.54111.5999.72111.3111.118-Jun-21
21Employee 18day9176-Jun-166-Jun-216-Jun-266-Jun-31480.5772.0052.576.618-Jun-21
22Employee 19Day9186-Jun-176-Jun-226-Jun-276-Jun-32480.5772.0052.576.618-Jun-21
23Location 2Daycurrent month increase rate should be updated
24Employee 20Day91923-Jul-1823-Jul-2323-Jul-2823-Jul-33480.5784.0064.578.130-Jul-21
25Employee 21Day92014-Aug-1714-Aug-2214-Aug-2714-Aug-32480.5792.0072.579.127-Aug-21
26Employee 22Day9215-Sep-165-Sep-215-Sep-265-Sep-31480.5796.0076.579.610-Sep-21
27Employee 23Day92228-Sep-1528-Sep-2028-Sep-2528-Sep-305.54111.59 11.591.49-Oct-20
28Employee 24Day92320-Oct-1420-Oct-1920-Oct-2420-Oct-295.54111.595.5417.132.123-Oct-20
29Employee 25Day92414-Nov-0414-Nov-0914-Nov-1414-Nov-198.8177.2626.4093.6611.720-Nov-20
30Location 3Daynext month and is projected to be updated.
31Employee 26Day92522-Feb-1122-Feb-1622-Feb-2122-Feb-265.54111.5955.4066.998.426-Feb-21
32Employee 27Day9266-Mar-066-Mar-116-Mar-166-Mar-217.08142.6177.88120.4915.112-Mar-21
33Employee 28Day9277-Mar-067-Mar-117-Mar-167-Mar-217.08142.6177.88120.4915.112-Mar-21
34Employee 29Day9285-Nov-115-Nov-165-Nov-215-Nov-265.54111.5911.0822.672.86-Nov-20
35Employee 30Day9296-Nov-116-Nov-166-Nov-216-Nov-265.54111.5911.0822.672.86-Nov-20
36Employee 31Day9307-Nov-117-Nov-167-Nov-217-Nov-265.54111.5916.6228.213.520-Nov-20
37Employee 32Day93120-Nov-1920-Nov-2420-Nov-2920-Nov-34480.5712.00  20-Nov-20
Use or Lose
Cell Formulas
RangeFormula
C2C2=TODAY()
I2I2=VLOOKUP(TODAY()+13,O4:O452,1,TRUE)
J2J2=DATE(YEAR(TODAY()),1,1)
E31:E37,E24:E29,E4:E22E4=DATE(YEAR(D4)+\$H\$3,MONTH(D4),DAY(D4))
F31:F37,F24:F29,F4:F22F4=DATE(YEAR(D4)+\$I\$3,MONTH(D4),DAY(D4))
G31:G37,G24:G29,G4:G22G4=DATE(YEAR(D4)+\$J\$3,MONTH(D4),DAY(D4))
H31:H37,H24:H29,H4:H22H4=IF(\$C\$2<E4,\$D\$2,IF(\$C\$2<F4,\$E\$2,IF(\$C\$2<G4,\$F\$2,\$G\$2)))
I31:I37,I24:I29,I4:I22I4='Start Check'!B4-'Vacation Taken'!N4+'Start Check'!C4
J31:J37,J24:J29,J4:J22J4=H4/14*(M4-\$I\$2)
K31:K37,K24:K29,K4:K22K4=IF(IF(H4>8,IF(I4+J4>110,I4+J4-110,0),I4+J4-100)<0,"",IF(H4>8,IF(I4+J4>110,I4+J4-110,0),I4+J4-100))
L31:L37,L24:L29,L4:L22L4=IFERROR(IF(B4="Day",K4/8,IF(B4="Night",K4/10,"Shift is Empty")),"")
M31:M37,M24:M29,M4:M22M4=IF(VLOOKUP(DATE(YEAR(\$J\$2),MONTH(D4),DAY(D4)+13),\$O\$4:\$O\$456,1)<\$I\$2,VLOOKUP(DATE(YEAR(\$J\$2)+1,MONTH(D4),DAY(D4)+13),\$O\$4:\$O\$456,1),(VLOOKUP(DATE(YEAR(\$J\$2),MONTH(D4),DAY(D4)+13),\$O\$4:\$O\$456,1)))
A31:A37,A24:A29,A4:A22A4='Start Check'!A4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M4:M22,M24:M29,M31:M37Dates Occurringthis monthtextNO
M4:M22,M31:M37,M24:M29Dates Occurringnext monthtextNO
E22:G22Dates Occurringlast monthtextNO
E22:G22Dates Occurringthis monthtextNO
E22:G22Dates Occurringnext monthtextNO
E32:G32Dates Occurringlast monthtextNO
E32:G32Dates Occurringthis monthtextNO
E32:G32Dates Occurringnext monthtextNO
L4:L22,L31:L37,L24:L29Expression=B4="Night"textNO
E4:G21,E33:G37,E31:G31,E24:G29Dates Occurringlast monthtextNO
E4:G21,E33:G37,E31:G31,E24:G29Dates Occurringthis monthtextNO
E4:G21,E33:G37,E31:G31,E24:G29Dates Occurringnext monthtextNO

VAC TEST.xlsx here is a link to the file on google drive. HTH

Ohmygoish! I canNOT thank you enough! Really, thank you so very much for taking the time to do this. I'm speechless..or typeless!

This is something I created for the company several years ago the accrual rates across the top are for the way our company did it you'll have to change those do what you talked about and as far as the years Irish were set up every 5 years you'll have to make your own changes hopefully though you can follow it there's no VBA it's all formulas it's definitely a start and you're welcome no problem

Ohmygoish! I canNOT thank you enough! Really, thank you so very much for taking the time to do this. I'm speechless..or typeless!
I made some changes to the sheet for you, see if it works for you.
Don

Replies
0
Views
310
Replies
1
Views
2K
Replies
2
Views
1K
Replies
11
Views
1K
Replies
1
Views
499

Threads
1,216,099
Messages
6,128,822
Members
449,469
Latest member
Kingwi11y

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

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