Excel Formula For Calculating Variable Dates

[JEF13]

I am looking for an Excel formula that will create dates of when a podcast show airs. Here is the situation:

1. The podcast will publish an episode either ONCE a week on Thursday or TWICE a week on Tuesday or Thursday depending on the number of guests who do interviews
2. Assume 52 weeks in the year for either 52 podcasts or a maximum of 104 podcasts
3. The number of guests continually changes as new guests book interviews

Sheet 1 contains the podcast information including guest name, date podcast is published, title, etc.
Sheet 2 contains the names of the guests

I am looking for help in which an Excel Formula can populate the podcast publication dates for a calendar year. This may mean that some weeks only have one podcast episode and other weeks have two podcast episodes. Assuming there are a minimum of 52 guests we would have on episode a week on Thursday. As an example, suppose there are 85 guests lined up. The formula would ensure that every week had a minimum of one episode and some weeks had two episodes.

Any and all suggestions are welcome.

Thanks.

JEffrey

 

[Eric W]

In your new layout, the P114 formula obviously is the same as the G2 formula in the old layout. So when I changed it to look at the list of guests, that's what it should be. Column C in the new layout doesn't seem to exist in the old layout, so I don't know what you want. In short, it appears the new adjusted formulas are working for you. Try out some scenarios and let me know.

 

[JEF13]

Eric, thanks. Perhaps the unknown column was from one of the earlier iterations that was inadvertently left it.
Thanks again for your help.

Jeffrey

 

[JEF13]

Hi everyone. Something has changed with the behavior of the spreadsheet, and I'm unsure how to fix it. What's going on is that unless there information of guests go up to cell 217 the dates and guest names aren't automatically filled in. So, if the guest list goes up to 84, the system doesn't fill in all 84 guest names. Only when the guest names go up to 102 does the system fully work. There's been a slight change in the formulas as I put the spreadsheet in Google Sheets for easy team access. Any insights as to how to correct the issues is appreciated. I've pasted the relevant parts of the spread sheet below. I've pasted a partial list of the cells in the spreadsheet. Thank you in advance for your help.

Podcast Tracker 2023.xlsx
	A	B	C
1	Guest	Date	Episode
2	Guest 1	Tue 01/03/2023	0191
3	Guest 2	Thu 01/05/2023	0192
4	Guest 3	Tue 01/10/2023	0193
5	Guest 4	Thu 01/12/2023	0194
6	Internal Show 1	Tue 01/17/2023	0195
7	Guest 5	Thu 01/19/2023	0196
8	Guest 6	Tue 01/24/2023	0197
9	Guest 7	Thu 01/26/2023	0198
10	Guest 8	Tue 01/31/2023	0199
11	Internal Show 2	Thu 02/02/2023	0200
12	Guest 9	Tue 02/07/2023	0201
13	Guest 10	Thu 02/09/2023	0202
14	Guest 11	Tue 02/14/2023	0203
15	Guest 12	Thu 02/16/2023	0204
16	Internal Show 3	Tue 02/21/2023	0205
17	Guest 13	Thu 02/23/2023	0206
Master Spreadsheet For Podcast

Cell Formulas
Range	Formula
A2	A2	=IF(B2="","",IF(AND(MOD(ROW()-1,$Q$113)=0,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$199,$A$1:$A1)))<COUNTA($L$114:$L$199)),INDEX($L$114:$L$199,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$199,$A$1:$A1)))+1),INDEX($J$114:$J$199,SUMPRODUCT(SIGN(COUNTIF($J$114:$J$199,$A$1:$A1)))+1)))
A3:A17	A3	=IF(B3="","",IF(AND(MOD(ROW()-1,$Q$113)=0,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$217,$A$1:$A2)))<COUNTA($L$114:$L$217)),INDEX($L$114:$L$217,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$217,$A$1:$A2)))+1),INDEX($J$114:$J$217,SUMPRODUCT(SIGN(COUNTIF($J$114:$J$217,$A$1:$A2)))+1)))
B3:B17	B3	=IF(ROWS(B$2:B3)>COUNTA($J$114:$J$217)+MIN(COUNTA($K$127:$K$217),$M$114),"",WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA($J$114:$J$217)+MIN(COUNTA($K$127:$K$217),$M$114)-52),"1110111","1010111")))
C3:C17	C3	=IF(LEN(A3)>1,C2+1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A112,E113,A200:A1000	Cell Value	contains "Internal"	text	NO

Podcast Tracker 2023.xlsx
	J	K	L	M	N	O	P	Q
113	Guests	Email	Internal	# of Internal Shows Needed			Number of Internal Shows Every N Weeks	5
114	Guest 1		Internal Show 1	21
115	Guest 2		Internal Show 2
116	Guest 3		Internal Show 3
117	Guest 4		Internal Show 4	Not enough internal shows - filling with guests
118	Guest 5		Internal Show 5
119	Guest 6		Internal Show 6
120	Guest 7		Internal Show 7
121	Guest 8		Internal Show 8
122	Guest 9		Internal Show 9
123	Guest 10		Internal Show 10
124	Guest 11		Internal Show 11
125	Guest 12		Internal Show 12
126	Guest 13		Internal Show 13
127	Guest 14		Internal Show 14
128	Guest 15		Internal Show 15
129	Guest 16		Internal Show 16
130	Guest 17		Internal Show 17
131	Guest 18		Internal Show 18
132	Guest 19		Internal Show 19
133	Guest 20		Internal Show 20
134	Guest 21		Internal Show 21
135	Guest 22
136	Guest 23
137	Guest 24
138	Guest 25
139	Guest 26
140	Guest 27
141	Guest 28
142	Guest 29
143	Guest 30
144	Guest 31
145	Guest 32
146	Guest 33
147	Guest 34
148	Guest 35
149	Guest 36
150	Guest 37
151	Guest 38
152	Guest 39
Master Spreadsheet For Podcast

Cell Formulas
Range	Formula
M114	M114	=INT((COUNTA(J114:K199)-1)/(Q113-1))
M117	M117	=IF(COUNTA(J114:K199)>104,"Too many shows scheduled for 1 year",IF(COUNTA(K127:K199)<M114,"Not enough internal shows - filling with guests",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M113	Expression	=$K113="Y"	text	NO
J117:J127,J130:J139,J143:J152,J156:J165,J169:J172,J176:J186,J189:J224,J228:J231,J235:J245,J248:J257,J261:J270,J274:J283,J287:J290,J294:J304,J307:J316,J320:J329,J333:J342,J346:J349,J353:J363,J366:J373	Cell Value	contains "JeffreyEpisode"	text	NO
L116	Cell Value	contains "JeffreyEpisode"	text	NO
L115,L117,L119,L121,L123,L125,K127,K129,K131,K133,K135,K137,K139,K141,K143,K145	Cell Value	contains "JeffreyEpisode"	text	NO
J135:J139,J194:J215,J253:J257,J312:J316,J371:J373	Cell Value	contains "JeffreyEpisode"	text	NO
J147:J152,J206:J211,J265:J270,J324:J329	Cell Value	contains "JeffreyEpisode"	text	NO
J143:J148,J202:J215,J261:J266,J320:J325	Cell Value	contains "JeffreyEpisode"	text	NO
J140:J144,J199:J203,J258:J262,J317:J321	Cell Value	contains "JeffreyEpisode"	text	NO

 

[Eric W]

It looks like the formulas are working ok, BUT, it looks like you altered the range references somehow when you rearranged your sheet. In particular, the reference to column L114:L217 was messed up in most of the formulas. See below:

Book1
	A	B	C
1	Guest	Date	Episode
2	Guest 1	1/3/2023	191
3	Guest 2	1/5/2023	192
4	Guest 3	1/12/2023	193
5	Guest 4	1/19/2023	194
6	Internal Show 1	1/26/2023	195
7	Guest 5	2/2/2023	196
8	Guest 6	2/9/2023	197
9	Guest 7	2/16/2023	198
10	Guest 8	2/23/2023	199
11	Internal Show 2	3/2/2023	200
12	Guest 9	3/9/2023	201
13	Guest 10	3/16/2023	202
14	Guest 11	3/23/2023	203
15	Guest 12	3/30/2023	204
16	Internal Show 3	4/6/2023	205
17	Guest 13	4/13/2023	206
18	Guest 14	4/20/2023	207
19	Guest 15	4/27/2023	208
20	Guest 16	5/4/2023	209
21	Internal Show 4	5/11/2023	210
22	Guest 17	5/18/2023	211
23	Guest 18	5/25/2023	212
24	Guest 19	6/1/2023	213
25	Guest 20	6/8/2023	214
26	Internal Show 5	6/15/2023	215
27	Guest 21	6/22/2023	216
28	Guest 22	6/29/2023	217
29	Guest 23	7/6/2023	218
30	Guest 24	7/13/2023	219
31	Internal Show 6	7/20/2023	220
32	Guest 25	7/27/2023	221
33	Guest 26	8/3/2023	222
34	Guest 27	8/10/2023	223
35	Guest 28	8/17/2023	224
36	Internal Show 7	8/24/2023	225
37	Guest 29	8/31/2023	226
38	Guest 30	9/7/2023	227
39	Guest 31	9/14/2023	228
40	Guest 32	9/21/2023	229
41	Internal Show 8	9/28/2023	230
42	Guest 33	10/5/2023	231
43	Guest 34	10/12/2023	232
44	Guest 35	10/19/2023	233
45	Guest 36	10/26/2023	234
46	Internal Show 9	11/2/2023	235
47	Guest 37	11/9/2023	236
48	Guest 38	11/16/2023	237
49	Guest 39	11/23/2023	238
50
51
Sheet3

Cell Formulas
Range	Formula
A2	A2	=IF(B2="","",IF(AND(MOD(ROW()-1,$Q$113)=0,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$199,$A$1:$A1)))<COUNTA($L$114:$L$199)),INDEX($L$114:$L$199,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$199,$A$1:$A1)))+1),INDEX($J$114:$J$199,SUMPRODUCT(SIGN(COUNTIF($J$114:$J$199,$A$1:$A1)))+1)))
A3:A51	A3	=IF(B3="","",IF(AND(MOD(ROW()-1,$Q$113)=0,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$217,$A$1:$A2)))<COUNTA($L$114:$L$217)),INDEX($L$114:$L$217,SUMPRODUCT(SIGN(COUNTIF($L$114:$L$217,$A$1:$A2)))+1),INDEX($J$114:$J$217,SUMPRODUCT(SIGN(COUNTIF($J$114:$J$217,$A$1:$A2)))+1)))
B3:B51	B3	=IF(ROWS(B$2:B3)>COUNTA($J$114:$J$217)+MIN(COUNTA($L$114:$L$217),$M$114),"",WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA($J$114:$J$217)+MIN(COUNTA($L$114:$L$217),$M$114)-52),"1110111","1010111")))
C3:C51	C3	=IF(LEN(A3)>1,C2+1,"")

Book1
	J	K	L	M	N	O	P	Q
113	Guests	Email	Internal	# of Internal Shows Needed			Number of Internal Shows Every N Weeks	5
114	Guest 1		Internal Show 1	9
115	Guest 2		Internal Show 2
116	Guest 3		Internal Show 3
117	Guest 4		Internal Show 4
118	Guest 5		Internal Show 5
119	Guest 6		Internal Show 6
120	Guest 7		Internal Show 7
121	Guest 8		Internal Show 8
122	Guest 9		Internal Show 9
123	Guest 10		Internal Show 10
124	Guest 11		Internal Show 11
125	Guest 12		Internal Show 12
126	Guest 13		Internal Show 13
127	Guest 14		Internal Show 14
128	Guest 15		Internal Show 15
129	Guest 16		Internal Show 16
130	Guest 17		Internal Show 17
131	Guest 18		Internal Show 18
132	Guest 19		Internal Show 19
133	Guest 20		Internal Show 20
134	Guest 21		Internal Show 21
135	Guest 22
136	Guest 23
137	Guest 24
138	Guest 25
139	Guest 26
140	Guest 27
141	Guest 28
142	Guest 29
143	Guest 30
144	Guest 31
145	Guest 32
146	Guest 33
147	Guest 34
148	Guest 35
149	Guest 36
150	Guest 37
151	Guest 38
152	Guest 39
153
Sheet3

Cell Formulas
Range	Formula
M114	M114	=INT((COUNTA(J114:K199)-1)/(Q113-1))
M117	M117	=IF(COUNTA(J114:J199)>104,"Too many shows scheduled for 1 year",IF(COUNTA(L114:L199)<M114,"Not enough internal shows - filling with guests",""))

I can't help much with Google Sheets. Most of the time the formulas can be transferred directly, but not always. If it doesn't work there, I don't have a way to test. Good luck!

 

[JEF13]

Eric, as always, thanks for your help. Everything is once again working!