vacation accrual

[sciencell]

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

 

[sciencell]

how do I find the right accrual formula

 

[Drrellik]

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]

VAC TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Shift	Emp #	Hire Date	5 year Increase date	10 year Increase date	15 year Increase date	Current Vacation Accrual	Current # 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)
2	Amount of Hours		#######	4	5.54	7.08	8.8		9-Oct-20	01 Jan 20
3	Location 1		last month increase rate should reflect new rate					5	10	15
4	Employee 1	Night	900	1-Jan-15	1-Jan-20	1-Jan-25	1-Jan-30	5.54	111.59	33.24	44.83	4.5	1-Jan-21
5	Employee 2	Night	901	1-Jan-16	1-Jan-21	1-Jan-26	1-Jan-31	4	80.57	24.00	4.57	0.5	1-Jan-21
6	Employee 3	Day	902	1-Jan-17	1-Jan-22	1-Jan-27	1-Jan-32	4	80.57	24.00	4.57	0.6	1-Jan-21
7	Employee 4	Day	903	1-Jan-18	1-Jan-23	1-Jan-28	1-Jan-33	4	80.57	24.00	4.57	0.6	1-Jan-21
8	Employee 5	Day	904	1-Jan-19	1-Jan-24	1-Jan-29	1-Jan-34	4	80.57	24.00	4.57	0.6	1-Jan-21
9	Employee 6	Day	905	1-Jan-20	1-Jan-25	1-Jan-30	1-Jan-35	4	80.57	24.00	4.57	0.6	1-Jan-21
10	Employee 7	Day	906	5-May-05	5-May-10	5-May-15	5-May-20	8.8	177.26	132.00	199.26	24.9	7-May-21
11	Employee 8	Day	907	6-May-05	6-May-10	6-May-15	6-May-20	8.8	177.26	132.00	199.26	24.9	7-May-21
12	Employee 9	Night	908	7-May-05	7-May-10	7-May-15	7-May-20	8.8	177.26	132.00	199.26	19.9	7-May-21
13	Employee 10	Night	909	6-Jun-08	6-Jun-13	6-Jun-18	6-Jun-23	7.08	142.61	127.44	170.05	17.0	18-Jun-21
14	Employee 11	Night	910	6-Jun-09	6-Jun-14	6-Jun-19	6-Jun-24	7.08	142.61	127.44	170.05	17.0	18-Jun-21
15	Employee 12	Night	911	6-Jun-10	6-Jun-15	6-Jun-20	6-Jun-25	7.08	142.61	127.44	170.05	17.0	18-Jun-21
16	Employee 13	Day	912	6-Jun-11	6-Jun-16	6-Jun-21	6-Jun-26	5.54	111.59	99.72	111.31	13.9	18-Jun-21
17	Employee 14	Night	913	6-Jun-12	6-Jun-17	6-Jun-22	6-Jun-27	5.54	111.59	99.72	111.31	11.1	18-Jun-21
18	Employee 15	Day	914	6-Jun-13	6-Jun-18	6-Jun-23	6-Jun-28	5.54	111.59	99.72	111.31	13.9	18-Jun-21
19	Employee 16	Day	915	6-Jun-14	6-Jun-19	6-Jun-24	6-Jun-29	5.54	111.59	99.72	111.31	13.9	18-Jun-21
20	Employee 17	Night	916	6-Jun-15	6-Jun-20	6-Jun-25	6-Jun-30	5.54	111.59	99.72	111.31	11.1	18-Jun-21
21	Employee 18	day	917	6-Jun-16	6-Jun-21	6-Jun-26	6-Jun-31	4	80.57	72.00	52.57	6.6	18-Jun-21
22	Employee 19	Day	918	6-Jun-17	6-Jun-22	6-Jun-27	6-Jun-32	4	80.57	72.00	52.57	6.6	18-Jun-21
23	Location 2	Day	current month increase rate should be updated
24	Employee 20	Day	919	23-Jul-18	23-Jul-23	23-Jul-28	23-Jul-33	4	80.57	84.00	64.57	8.1	30-Jul-21
25	Employee 21	Day	920	14-Aug-17	14-Aug-22	14-Aug-27	14-Aug-32	4	80.57	92.00	72.57	9.1	27-Aug-21
26	Employee 22	Day	921	5-Sep-16	5-Sep-21	5-Sep-26	5-Sep-31	4	80.57	96.00	76.57	9.6	10-Sep-21
27	Employee 23	Day	922	28-Sep-15	28-Sep-20	28-Sep-25	28-Sep-30	5.54	111.59		11.59	1.4	9-Oct-20
28	Employee 24	Day	923	20-Oct-14	20-Oct-19	20-Oct-24	20-Oct-29	5.54	111.59	5.54	17.13	2.1	23-Oct-20
29	Employee 25	Day	924	14-Nov-04	14-Nov-09	14-Nov-14	14-Nov-19	8.8	177.26	26.40	93.66	11.7	20-Nov-20
30	Location 3	Day	next month and is projected to be updated.
31	Employee 26	Day	925	22-Feb-11	22-Feb-16	22-Feb-21	22-Feb-26	5.54	111.59	55.40	66.99	8.4	26-Feb-21
32	Employee 27	Day	926	6-Mar-06	6-Mar-11	6-Mar-16	6-Mar-21	7.08	142.61	77.88	120.49	15.1	12-Mar-21
33	Employee 28	Day	927	7-Mar-06	7-Mar-11	7-Mar-16	7-Mar-21	7.08	142.61	77.88	120.49	15.1	12-Mar-21
34	Employee 29	Day	928	5-Nov-11	5-Nov-16	5-Nov-21	5-Nov-26	5.54	111.59	11.08	22.67	2.8	6-Nov-20
35	Employee 30	Day	929	6-Nov-11	6-Nov-16	6-Nov-21	6-Nov-26	5.54	111.59	11.08	22.67	2.8	6-Nov-20
36	Employee 31	Day	930	7-Nov-11	7-Nov-16	7-Nov-21	7-Nov-26	5.54	111.59	16.62	28.21	3.5	20-Nov-20
37	Employee 32	Day	931	20-Nov-19	20-Nov-24	20-Nov-29	20-Nov-34	4	80.57	12.00			20-Nov-20
Use or Lose

Cell Formulas
Range	Formula
C2	C2	=TODAY()
I2	I2	=VLOOKUP(TODAY()+13,O4:O452,1,TRUE)
J2	J2	=DATE(YEAR(TODAY()),1,1)
E31:E37,E24:E29,E4:E22	E4	=DATE(YEAR(D4)+$H$3,MONTH(D4),DAY(D4))
F31:F37,F24:F29,F4:F22	F4	=DATE(YEAR(D4)+$I$3,MONTH(D4),DAY(D4))
G31:G37,G24:G29,G4:G22	G4	=DATE(YEAR(D4)+$J$3,MONTH(D4),DAY(D4))
H31:H37,H24:H29,H4:H22	H4	=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:I22	I4	='Start Check'!B4-'Vacation Taken'!N4+'Start Check'!C4
J31:J37,J24:J29,J4:J22	J4	=H4/14*(M4-$I$2)
K31:K37,K24:K29,K4:K22	K4	=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:L22	L4	=IFERROR(IF(B4="Day",K4/8,IF(B4="Night",K4/10,"Shift is Empty")),"")
M31:M37,M24:M29,M4:M22	M4	=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:A22	A4	='Start Check'!A4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M4:M22,M24:M29,M31:M37	Dates Occurring	this month	text	NO
M4:M22,M31:M37,M24:M29	Dates Occurring	next month	text	NO
E22:G22	Dates Occurring	last month	text	NO
E22:G22	Dates Occurring	this month	text	NO
E22:G22	Dates Occurring	next month	text	NO
E32:G32	Dates Occurring	last month	text	NO
E32:G32	Dates Occurring	this month	text	NO
E32:G32	Dates Occurring	next month	text	NO
L4:L22,L31:L37,L24:L29	Expression	=B4="Night"	text	NO
E4:G21,E33:G37,E31:G31,E24:G29	Dates Occurring	last month	text	NO
E4:G21,E33:G37,E31:G31,E24:G29	Dates Occurring	this month	text	NO
E4:G21,E33:G37,E31:G31,E24:G29	Dates Occurring	next month	text	NO

 

[Drrellik]

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

 

[sciencell]

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]

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]

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