vacation accrual

sciencell

New Member
Joined
Sep 28, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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
 

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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
 

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows

ADVERTISEMENT

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

sciencell

New Member
Joined
Sep 28, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Ohmygoish! I canNOT thank you enough! Really, thank you so very much for taking the time to do this. I'm speechless..or typeless!
 

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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
 

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,842
Messages
5,627,203
Members
416,229
Latest member
mohammadmihdi

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