Vacation Time Allocation

[polska2180]

Hi, I'm trying to create a calendar with a PTO allowance per day per week based on the number of weeks we have to cover. The difficulty is that I want to be able to flex any one day/week/month to increase or decrease once the distribution of days is figured out and then recalculate the remaining days evenly through the rest of the year. So let's say on average I need to have 9.6 people off per day per week to cover all the PTO (always round so basically 10.) Now during the months of June and July, I want to increase to 12 people per day per week, and in Feb during one week, I want to decrease to 5 people off per day per week. Once I make that adjustment I would want all the other days that are not changed to be recalculated to ensure that I have enough allowance to cover the remaining time off so in the example that now may be 8.3 people and it would round up to 9. Below is where I got to and I'm getting into circular equations and just not getting to what I need. Thanks for the help. My layout may not be the best and I'm not stuck to it so if there is a better way to do this I'm game. All I need to have is to be able to put in the variables and make specific adjustments during any particular day/week/month.

PTO Allocation Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	Total Employees	100					HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC	HC Adj	Day	Date	PTO HC w/o adj	PTO HC
2	Number of Days per week	5					`	Mon	1/2/2023	5.8	6	`	Mon	2/13/2023	5.8	6		Mon	3/27/2023	5.8	6		Mon	5/8/2023	5.8	6		Mon	6/19/2023	5.8	6		Mon	7/31/2023	5.8	6		Mon	9/11/2023	5.8	6		Mon	10/23/2023	5.8	6		Mon	12/4/2023	5.8	6
3	Number of Weeks	52	223.0769					Tue	1/3/2023	5.8	6		Tue	2/14/2023	5.8	6		Tue	3/28/2023	5.8	6		Tue	5/9/2023	5.8	6		Tue	6/20/2023	5.8	6		Tue	8/1/2023	5.8	6		Tue	9/12/2023	5.8	6		Tue	10/24/2023	5.8	6		Tue	12/5/2023	5.8	6
4	Number of Work Days / Yr	260	44.61538					Wed	1/4/2023	5.8	6		Wed	2/15/2023	5.8	6		Wed	3/29/2023	5.8	6		Wed	5/10/2023	5.8	6		Wed	6/21/2023	5.8	6		Wed	8/2/2023	5.8	6		Wed	9/13/2023	5.8	6		Wed	10/25/2023	5.8	6		Wed	12/6/2023	5.8	6
5	Hours per day	8						Thu	1/5/2023	5.8	6		Thu	2/16/2023	5.8	6		Thu	3/30/2023	5.8	6		Thu	5/11/2023	5.8	6		Thu	6/22/2023	5.8	6		Thu	8/3/2023	5.8	6		Thu	9/14/2023	5.8	6		Thu	10/26/2023	5.8	6		Thu	12/7/2023	5.8	6
6	Hours per week	40						Fri	1/6/2023	5.8	6		Fri	2/17/2023	5.8	6		Fri	3/31/2023	5.8	6		Fri	5/12/2023	5.8	6		Fri	6/23/2023	5.8	6		Fri	8/4/2023	5.8	6		Fri	9/15/2023	5.8	6		Fri	10/27/2023	5.8	6		Fri	12/8/2023	5.8	6
7	Start Date	1/2/2023						Mon	1/9/2023	5.8	6		Mon	2/20/2023	5.8	6		Mon	4/3/2023	5.8	6		Mon	5/15/2023	5.8	6		Mon	6/26/2023	5.8	6		Mon	8/7/2023	5.8	6		Mon	9/18/2023	5.8	6		Mon	10/30/2023	5.8	6		Mon	12/11/2023	5.8	6
8	PTO allowed no rounding	5.5769231						Tue	1/10/2023	5.8	6		Tue	2/21/2023	5.8	6		Tue	4/4/2023	5.8	6		Tue	5/16/2023	5.8	6		Tue	6/27/2023	5.8	6		Tue	8/8/2023	5.8	6		Tue	9/19/2023	5.8	6		Tue	10/31/2023	5.8	6		Tue	12/12/2023	5.8	6
9	PTO allowed per week	6						Wed	1/11/2023	5.8	6		Wed	2/22/2023	5.8	6		Wed	4/5/2023	5.8	6		Wed	5/17/2023	5.8	6		Wed	6/28/2023	5.8	6		Wed	8/9/2023	5.8	6		Wed	9/20/2023	5.8	6		Wed	11/1/2023	5.8	6		Wed	12/13/2023	5.8	6
10								Thu	1/12/2023	5.8	6		Thu	2/23/2023	5.8	6		Thu	4/6/2023	5.8	6		Thu	5/18/2023	5.8	6		Thu	6/29/2023	5.8	6		Thu	8/10/2023	5.8	6		Thu	9/21/2023	5.8	6		Thu	11/2/2023	5.8	6		Thu	12/14/2023	5.8	6
11	1 Week	10	400					Fri	1/13/2023	5.8	6		Fri	2/24/2023	5.8	6		Fri	4/7/2023	5.8	6		Fri	5/19/2023	5.8	6		Fri	6/30/2023	5.8	6		Fri	8/11/2023	5.8	6		Fri	9/22/2023	5.8	6		Fri	11/3/2023	5.8	6		Fri	12/15/2023	5.8	6
12	2 Weeks	40	3200					Mon	1/16/2023	5.8	6		Mon	2/27/2023	5.8	6		Mon	4/10/2023	5.8	6		Mon	5/22/2023	5.8	6		Mon	7/3/2023	5.8	6		Mon	8/14/2023	5.8	6		Mon	9/25/2023	5.8	6		Mon	11/6/2023	5.8	6		Mon	12/18/2023	5.8	6
13	3 Weeks	20	2400					Tue	1/17/2023	5.8	6		Tue	2/28/2023	5.8	6		Tue	4/11/2023	5.8	6		Tue	5/23/2023	5.8	6		Tue	7/4/2023	5.8	6		Tue	8/15/2023	5.8	6		Tue	9/26/2023	5.8	6		Tue	11/7/2023	5.8	6		Tue	12/19/2023	5.8	6
14	4 Weeks	10	1600					Wed	1/18/2023	5.8	6		Wed	3/1/2023	5.8	6		Wed	4/12/2023	5.8	6		Wed	5/24/2023	5.8	6		Wed	7/5/2023	5.8	6		Wed	8/16/2023	5.8	6		Wed	9/27/2023	5.8	6		Wed	11/8/2023	5.8	6		Wed	12/20/2023	5.8	6
15	5 Weeks	20	4000					Thu	1/19/2023	5.8	6		Thu	3/2/2023	5.8	6		Thu	4/13/2023	5.8	6		Thu	5/25/2023	5.8	6		Thu	7/6/2023	5.8	6		Thu	8/17/2023	5.8	6		Thu	9/28/2023	5.8	6		Thu	11/9/2023	5.8	6		Thu	12/21/2023	5.8	6
16	6 Weeks		0					Fri	1/20/2023	5.8	6		Fri	3/3/2023	5.8	6		Fri	4/14/2023	5.8	6		Fri	5/26/2023	5.8	6		Fri	7/7/2023	5.8	6		Fri	8/18/2023	5.8	6		Fri	9/29/2023	5.8	6		Fri	11/10/2023	5.8	6		Fri	12/22/2023	5.8	6
17	Total	100	11600					Mon	1/23/2023	5.8	6		Mon	3/6/2023	5.8	6		Mon	4/17/2023	5.8	6		Mon	5/29/2023	5.8	6		Mon	7/10/2023	5.8	6		Mon	8/21/2023	5.8	6		Mon	10/2/2023	5.8	6		Mon	11/13/2023	5.8	6		Mon	12/25/2023	5.8	6
18	People off per week							Tue	1/24/2023	5.8	6		Tue	3/7/2023	5.8	6		Tue	4/18/2023	5.8	6		Tue	5/30/2023	5.8	6		Tue	7/11/2023	5.8	6		Tue	8/22/2023	5.8	6		Tue	10/3/2023	5.8	6		Tue	11/14/2023	5.8	6		Tue	12/26/2023	5.8	6
19								Wed	1/25/2023	5.8	6		Wed	3/8/2023	5.8	6	2	Wed	4/19/2023	5.8	6		Wed	5/31/2023	5.8	6		Wed	7/12/2023	5.8	6		Wed	8/23/2023	5.8	6		Wed	10/4/2023	5.8	6		Wed	11/15/2023	5.8	6		Wed	12/27/2023	5.8	6
20								Thu	1/26/2023	5.8	6		Thu	3/9/2023	5.8	6	2	Thu	4/20/2023	5.8	6		Thu	6/1/2023	5.8	6		Thu	7/13/2023	5.8	6		Thu	8/24/2023	5.8	6		Thu	10/5/2023	5.8	6		Thu	11/16/2023	5.8	6		Thu	12/28/2023	5.8	6
21	PTO Pure	1450	5.576923					Fri	1/27/2023	5.8	6		Fri	3/10/2023	5.8	6	2	Fri	4/21/2023	5.8	6		Fri	6/2/2023	5.8	6		Fri	7/14/2023	5.8	6		Fri	8/25/2023	5.8	6		Fri	10/6/2023	5.8	6		Fri	11/17/2023	5.8	6		Fri	12/29/2023	5.8	6
22	PTO Adj	1392	5.353846				3	Mon	1/30/2023	5.8	6		Mon	3/13/2023	5.8	6	2	Mon	4/24/2023	5.8	6	2	Mon	6/5/2023	5.8	6		Mon	7/17/2023	5.8	6		Mon	8/28/2023	5.8	6		Mon	10/9/2023	5.8	6		Mon	11/20/2023	5.8	6
23							3	Tue	1/31/2023	5.8	6		Tue	3/14/2023	5.8	6	2	Tue	4/25/2023	5.8	6	2	Tue	6/6/2023	5.8	6		Tue	7/18/2023	5.8	6		Tue	8/29/2023	5.8	6		Tue	10/10/2023	5.8	6		Tue	11/21/2023	5.8	6
24	Rolled up	1560					3	Wed	2/1/2023	5.8	6		Wed	3/15/2023	5.8	6	2	Wed	4/26/2023	5.8	6	2	Wed	6/7/2023	5.8	6		Wed	7/19/2023	5.8	6		Wed	8/30/2023	5.8	6		Wed	10/11/2023	5.8	6		Wed	11/22/2023	5.8	6
25	Not allowed	58					3	Thu	2/2/2023	5.8	6		Thu	3/16/2023	5.8	6	2	Thu	4/27/2023	5.8	6	2	Thu	6/8/2023	5.8	6		Thu	7/20/2023	5.8	6		Thu	8/31/2023	5.8	6		Thu	10/12/2023	5.8	6		Thu	11/23/2023	5.8	6
26		1508	5.8				3	Fri	2/3/2023	5.8	6		Fri	3/17/2023	5.8	6	2	Fri	4/28/2023	5.8	6	2	Fri	6/9/2023	5.8	6		Fri	7/21/2023	5.8	6		Fri	9/1/2023	5.8	6		Fri	10/13/2023	5.8	6		Fri	11/24/2023	5.8	6
27							3	Mon	2/6/2023	5.8	6		Mon	3/20/2023	5.8	6	2	Mon	5/1/2023	5.8	6		Mon	6/12/2023	5.8	6		Mon	7/24/2023	5.8	6		Mon	9/4/2023	5.8	6		Mon	10/16/2023	5.8	6		Mon	11/27/2023	5.8	6
28							3	Tue	2/7/2023	5.8	6		Tue	3/21/2023	5.8	6		Tue	5/2/2023	5.8	6		Tue	6/13/2023	5.8	6		Tue	7/25/2023	5.8	6		Tue	9/5/2023	5.8	6		Tue	10/17/2023	5.8	6		Tue	11/28/2023	5.8	6
29							3	Wed	2/8/2023	5.8	6		Wed	3/22/2023	5.8	6		Wed	5/3/2023	5.8	6		Wed	6/14/2023	5.8	6		Wed	7/26/2023	5.8	6		Wed	9/6/2023	5.8	6		Wed	10/18/2023	5.8	6		Wed	11/29/2023	5.8	6
30							3	Thu	2/9/2023	5.8	6		Thu	3/23/2023	5.8	6		Thu	5/4/2023	5.8	6		Thu	6/15/2023	5.8	6		Thu	7/27/2023	5.8	6		Thu	9/7/2023	5.8	6		Thu	10/19/2023	5.8	6		Thu	11/30/2023	5.8	6
31							3	Fri	2/10/2023	5.8	6		Fri	3/24/2023	5.8	6		Fri	5/5/2023	5.8	6		Fri	6/16/2023	5.8	6		Fri	7/28/2023	5.8	6		Fri	9/8/2023	5.8	6		Fri	10/20/2023	5.8	6		Fri	12/1/2023	5.8	6
32							30			174	180	0			174	180	18			174	180	10			174	180	0			174	180	0			174	180	0			174	180	0			174	180	0			116	120
Orig

Cell Formulas
Range	Formula
H2:H31,AV2:AV21,AQ2:AQ31,AL2:AL31,AG2:AG31,AB2:AB31,W2:W31,R2:R31,M2:M31	H2	=TEXT(I2,"DDd")
I2	I2	=B7
J2:J31,AX2:AX21,AS2:AS31,AN2:AN31,AI2:AI31,AD2:AD31,Y2:Y31,T2:T31,O2:O31	J2	=$C$26
K2:K31,AY2:AY21,AT2:AT31,AO2:AO31,AJ2:AJ31,AE2:AE31,Z2:Z31,U2:U31,P2:P31	K2	=ROUNDUP(J2,0)
L2	L2	=IF(G2="",ROUNDUP(K2,1),G2)
N2:N6,AW2:AW6,AR2:AR6,AM2:AM6,AH2:AH6,AC2:AC6,X2:X6,S2:S6	N2	=I27+7
N7:N31,I7:I31,AW7:AW21,AR7:AR31,AM7:AM31,AH7:AH31,AC7:AC31,X7:X31,S7:S31	N7	=N2+7
I3:I6	I3	=I2+1
C3:C4	C3	=$C$17/B3
B8	B8	=C3/B6
B9	B9	=ROUNDUP(B8,0)
C11	C11	=IFERROR(B11*$B$6,"")
C12	C12	=IFERROR(B12*$B$6*2,"")
C13	C13	=IFERROR(B13*$B$6*3,"")
C14	C14	=IFERROR(B14*$B$6*4,"")
C15	C15	=IFERROR(B15*$B$6*5,"")
C16	C16	=IFERROR(B16*$B$6*6,"")
B17:C17	B17	=SUM(B11:B16)
B21	B21	=B8*B4
C21	C21	=B21/B4
B22	B22	=B21-B25
C22	C22	=B22/B4
B24	B24	=K32+P32+U32+AE32++AJ32+AO32+AT32+AY32+Z32
B25	B25	=G32+L32+Q32+V32+AA32+AK32+AP32++AU32+AF32
B26	B26	=B21+B25
C26	C26	=B26/B4
G32,AX32:AY32,AS32:AU32,AN32:AP32,AI32:AK32,AD32:AF32,Y32:AA32,T32:V32,O32:Q32,J32:L32	G32	=SUM(G2:G31)