Vacation Schedule

[polska2180]

Hi, I'm looking to make or find a spreadsheet that will provide allotment of people that can have PTO by week based on the number of hours of PTO available and 260 days. Additionally, looking to be able to flex certain parts of the year and reduce number of people for a period of time and the rest of the year adjusts based on what I reduced. For example

Assuming that the number of hours available for PTO equals to 40 per day and 200 per week effectively 5 people can have off per week. The distribution would then be 5 people per week all year long. Now lets for mothers day and fathers day weeks I cannot have any folks off (it doesn't have to be all or nothing just using this as example). Then those 10 people (5 each of the week) or 400 hours would get redistributed equally throughout the rest of the year and because I cannot have part of the person off it would round up. Thanks for the help.

 

[ExceLoki]

you can expand for the entire year with this:

Book1
	A	B	C	D	E	F
1	PTO Hours per Day	40	people per day	5	per week	200
2	per year	10400	Work Days	250
3	Date	Week Number	Weekend or Work	PTO allowed?	Daily PTO	Weekly PTO
4	1/1/2022	1	Weekend	yes	0	0
5	1/2/2022	2	Weekend	yes	0	0
6	1/3/2022	2	Work	yes	42	208
7	1/4/2022	2	Work	yes	42	208
8	1/5/2022	2	Work	yes	42	208
9	1/6/2022	2	Work	yes	42	208
10	1/7/2022	2	Work	yes	42	208
11	1/8/2022	2	Weekend	yes	0	0
12	1/9/2022	3	Weekend	yes	0	0
13	1/10/2022	3	Work	yes	42	208
14	1/11/2022	3	Work	yes	42	208
15	1/12/2022	3	Work	yes	42	208
16	1/13/2022	3	Work	yes	42	208
17	1/14/2022	3	Work	yes	42	208
18	1/15/2022	3	Weekend	yes	0	0
19	1/16/2022	4	Weekend	yes	0	0
20	1/17/2022	4	Work	yes	42	208
21	1/18/2022	4	Work	yes	42	208
22	1/19/2022	4	Work	yes	42	208
23	1/20/2022	4	Work	yes	42	208
24	1/21/2022	4	Work	yes	42	208
25	1/22/2022	4	Weekend	yes	0	0
26	1/23/2022	5	Weekend	yes	0	0
27	1/24/2022	5	Work	yes	42	208
28	1/25/2022	5	Work	yes	42	208
29	1/26/2022	5	Work	yes	42	208
30	1/27/2022	5	Work	yes	42	208
31	1/28/2022	5	Work	yes	42	208
32	1/29/2022	5	Weekend	yes	0	0
33	1/30/2022	6	Weekend	yes	0	0
34	1/31/2022	6	Work	yes	42	208
35	2/1/2022	6	Work	yes	42	208
36	2/2/2022	6	Work	yes	42	208
37	2/3/2022	6	Work	yes	42	208
38	2/4/2022	6	Work	yes	42	208
39	2/5/2022	6	Weekend	yes	0	0
40	2/6/2022	7	Weekend	yes	0	0
41	2/7/2022	7	Work	yes	42	208
42	2/8/2022	7	Work	yes	42	208
43	2/9/2022	7	Work	yes	42	208
44	2/10/2022	7	Work	yes	42	208
45	2/11/2022	7	Work	yes	42	208
46	2/12/2022	7	Weekend	yes	0	0
47	2/13/2022	8	Weekend	yes	0	0
48	2/14/2022	8	Work	yes	42	208
49	2/15/2022	8	Work	yes	42	208
50	2/16/2022	8	Work	yes	42	208
51	2/17/2022	8	Work	yes	42	208
52	2/18/2022	8	Work	yes	42	208
53	2/19/2022	8	Weekend	yes	0	0
54	2/20/2022	9	Weekend	yes	0	0
55	2/21/2022	9	Work	yes	42	208
56	2/22/2022	9	Work	yes	42	208
57	2/23/2022	9	Work	yes	42	208
58	2/24/2022	9	Work	yes	42	208
59	2/25/2022	9	Work	yes	42	208
60	2/26/2022	9	Weekend	yes	0	0
61	2/27/2022	10	Weekend	yes	0	0
62	2/28/2022	10	Work	yes	42	208
63	3/1/2022	10	Work	yes	42	208
64	3/2/2022	10	Work	yes	42	208
65	3/3/2022	10	Work	yes	42	208
66	3/4/2022	10	Work	yes	42	208
67	3/5/2022	10	Weekend	yes	0	0
68	3/6/2022	11	Weekend	yes	0	0
69	3/7/2022	11	Work	yes	42	208
70	3/8/2022	11	Work	yes	42	208
71	3/9/2022	11	Work	yes	42	208
72	3/10/2022	11	Work	yes	42	208
73	3/11/2022	11	Work	yes	42	208
74	3/12/2022	11	Weekend	yes	0	0
75	3/13/2022	12	Weekend	yes	0	0
76	3/14/2022	12	Work	yes	42	208
77	3/15/2022	12	Work	yes	42	208
78	3/16/2022	12	Work	yes	42	208
79	3/17/2022	12	Work	yes	42	208
80	3/18/2022	12	Work	yes	42	208
81	3/19/2022	12	Weekend	yes	0	0
82	3/20/2022	13	Weekend	yes	0	0
83	3/21/2022	13	Work	yes	42	208
84	3/22/2022	13	Work	yes	42	208
85	3/23/2022	13	Work	yes	42	208
86	3/24/2022	13	Work	yes	42	208
87	3/25/2022	13	Work	yes	42	208
88	3/26/2022	13	Weekend	yes	0	0
89	3/27/2022	14	Weekend	yes	0	0
90	3/28/2022	14	Work	yes	42	208
91	3/29/2022	14	Work	yes	42	208
92	3/30/2022	14	Work	yes	42	208
93	3/31/2022	14	Work	yes	42	208
94	4/1/2022	14	Work	yes	42	208
95	4/2/2022	14	Weekend	yes	0	0
96	4/3/2022	15	Weekend	yes	0	0
97	4/4/2022	15	Work	yes	42	208
98	4/5/2022	15	Work	yes	42	208
99	4/6/2022	15	Work	yes	42	208
100	4/7/2022	15	Work	yes	42	208
101	4/8/2022	15	Work	yes	42	208
102	4/9/2022	15	Weekend	yes	0	0
103	4/10/2022	16	Weekend	yes	0	0
104	4/11/2022	16	Work	yes	42	208
105	4/12/2022	16	Work	yes	42	208
106	4/13/2022	16	Work	yes	42	208
107	4/14/2022	16	Work	yes	42	208
108	4/15/2022	16	Work	yes	42	208
109	4/16/2022	16	Weekend	yes	0	0
110	4/17/2022	17	Weekend	yes	0	0
111	4/18/2022	17	Work	yes	42	208
112	4/19/2022	17	Work	yes	42	208
113	4/20/2022	17	Work	yes	42	208
114	4/21/2022	17	Work	yes	42	208
115	4/22/2022	17	Work	yes	42	208
116	4/23/2022	17	Weekend	yes	0	0
117	4/24/2022	18	Weekend	yes	0	0
118	4/25/2022	18	Work	yes	42	208
119	4/26/2022	18	Work	yes	42	208
120	4/27/2022	18	Work	yes	42	208
121	4/28/2022	18	Work	yes	42	208
122	4/29/2022	18	Work	yes	42	208
123	4/30/2022	18	Weekend	yes	0	0
124	5/1/2022	19	Weekend	yes	0	0
125	5/2/2022	19	Work	yes	42	208
126	5/3/2022	19	Work	yes	42	208
127	5/4/2022	19	Work	yes	42	208
128	5/5/2022	19	Work	yes	42	208
129	5/6/2022	19	Work	yes	42	208
130	5/7/2022	19	Weekend	yes	0	0
131	5/8/2022	20	Weekend	yes	0	0
132	5/9/2022	20	Work	yes	42	208
133	5/10/2022	20	Work	yes	42	208
134	5/11/2022	20	Work	yes	42	208
135	5/12/2022	20	Work	yes	42	208
136	5/13/2022	20	Work	yes	42	208
137	5/14/2022	20	Weekend	no	0	0
138	5/15/2022	21	Weekend	no	0	0
139	5/16/2022	21	Work	no	0	0
140	5/17/2022	21	Work	no	0	0
141	5/18/2022	21	Work	no	0	0
142	5/19/2022	21	Work	no	0	0
143	5/20/2022	21	Work	no	0	0
144	5/21/2022	21	Weekend	yes	0	0
145	5/22/2022	22	Weekend	yes	0	0
basic

Cell Formulas
Range	Formula
F1	F1	=B1*D1
B2	B2	=F1*(COUNTIF(C:C,"work")/5)
D2	D2	=COUNTIFS(C:C,"Work",D:D,"yes")
B4:B145	B4	=WEEKNUM(A4)
C4,C6:C145	C4	=IF(OR(WEEKDAY(A4)={1,7}),"Weekend","Work")
C5	C5	=IF(OR(WEEKDAY(A5)=1),"Weekend","Work")
E4:E145	E4	=IF(AND(C4="Work",D4="yes"),$B$2/$D$2,0)
F4:F145	F4	=IF([@[Daily PTO]]>0,SUMIF(B:E,[@[Week Number]],E:E),0)

Cells with Data Validation
Cell	Allow	Criteria
D4:D145	List	yes,no,holiday

 

[ExceLoki]

i also came up with a possible solution to allow for 1-5 people off per certain days and reallocating the unused hours to the rest of the normal days of the year with no restrictions.

 

[polska2180]

i also came up with a possible solution to allow for 1-5 people off per certain days and reallocating the unused hours to the rest of the normal days of the year with no restrictions.

Ok working on putting this into a spreadsheet and will ask follow ups I'm sure. I guess first TY wasn't sure how to go out this at first. I'm getting syntax error if f4?

 

[polska2180]

i also came up with a possible solution to allow for 1-5 people off per certain days and reallocating the unused hours to the rest of the normal days of the year with no restrictions.

ok so this would be something needed and the range would depend on number of people on staff. For example assuming 100 people on staff and varied tenure and ranging in vacation time from 1-6 weeks depending on tenure it may land at 7 people that need to be off per day and thus the range now would be 1-7.

 

[ExceLoki]

Ok working on putting this into a spreadsheet and will ask follow ups I'm sure. I guess first TY wasn't sure how to go out this at first. I'm getting syntax error if f4?

are you using the same named ranges i have in the table? if not then you would need to adjust the formula.