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]

What version of Excel do you have? The best answer often depends on what functions are available. Please update your profile.

If you have Excel 365, try:

Book1
	A	B	C
1	Guests	Date
2	Guest 1	1/6/2022	Thu
3	Guest 2	1/11/2022	Tue
4	Guest 3	1/13/2022	Thu
5	Guest 4	1/18/2022	Tue
6	Guest 5	1/20/2022	Thu
7	Guest 6	1/25/2022	Tue
8	Guest 7	1/27/2022	Thu
9	Guest 8	2/1/2022	Tue
10	Guest 9	2/3/2022	Thu
11	Guest 10	2/8/2022	Tue
12	Guest 11	2/10/2022	Thu
13	Guest 12	2/15/2022	Tue
14	Guest 13	2/17/2022	Thu
15	Guest 14	2/22/2022	Tue
16	Guest 15	2/24/2022	Thu
17	Guest 16	3/1/2022	Tue
18	Guest 17	3/3/2022	Thu
19	Guest 18	3/8/2022	Tue
20	Guest 19	3/10/2022	Thu
21	Guest 20	3/15/2022	Tue
22	Guest 21	3/17/2022	Thu
23	Guest 22	3/22/2022	Tue
24	Guest 23	3/24/2022	Thu
25	Guest 24	3/29/2022	Tue
26	Guest 25	3/31/2022	Thu
27	Guest 26	4/5/2022	Tue
28	Guest 27	4/7/2022	Thu
29	Guest 28	4/12/2022	Tue
30	Guest 29	4/14/2022	Thu
31	Guest 30	4/19/2022	Tue
32	Guest 31	4/21/2022	Thu
33	Guest 32	4/26/2022	Tue
34	Guest 33	4/28/2022	Thu
35	Guest 34	5/3/2022	Tue
36	Guest 35	5/5/2022	Thu
37	Guest 36	5/10/2022	Tue
38	Guest 37	5/12/2022	Thu
39	Guest 38	5/17/2022	Tue
40	Guest 39	5/19/2022	Thu
41	Guest 40	5/24/2022	Tue
42	Guest 41	5/26/2022	Thu
43	Guest 42	5/31/2022	Tue
44	Guest 43	6/2/2022	Thu
45	Guest 44	6/7/2022	Tue
46	Guest 45	6/9/2022	Thu
47	Guest 46	6/14/2022	Tue
48	Guest 47	6/16/2022	Thu
49	Guest 48	6/21/2022	Tue
50	Guest 49	6/23/2022	Thu
51	Guest 50	6/28/2022	Tue
52	Guest 51	6/30/2022	Thu
53	Guest 52	7/5/2022	Tue
54	Guest 53	7/7/2022	Thu
55	Guest 54	7/12/2022	Tue
56	Guest 55	7/14/2022	Thu
57	Guest 56	7/19/2022	Tue
58	Guest 57	7/21/2022	Thu
59	Guest 58	7/26/2022	Tue
60	Guest 59	7/28/2022	Thu
61	Guest 60	8/2/2022	Tue
62	Guest 61	8/4/2022	Thu
63	Guest 62	8/9/2022	Tue
64	Guest 63	8/11/2022	Thu
65	Guest 64	8/16/2022	Tue
66	Guest 65	8/18/2022	Thu
67	Guest 66	8/23/2022	Tue
68	Guest 67	8/25/2022	Thu
69	Guest 68	9/1/2022	Thu
70	Guest 69	9/8/2022	Thu
71	Guest 70	9/15/2022	Thu
72	Guest 71	9/22/2022	Thu
73	Guest 72	9/29/2022	Thu
74	Guest 73	10/6/2022	Thu
75	Guest 74	10/13/2022	Thu
76	Guest 75	10/20/2022	Thu
77	Guest 76	10/27/2022	Thu
78	Guest 77	11/3/2022	Thu
79	Guest 78	11/10/2022	Thu
80	Guest 79	11/17/2022	Thu
81	Guest 80	11/24/2022	Thu
82	Guest 81	12/1/2022	Thu
83	Guest 82	12/8/2022	Thu
84	Guest 83	12/15/2022	Thu
85	Guest 84	12/22/2022	Thu
86	Guest 85	12/29/2022	Thu
Sheet1

Cell Formulas
Range	Formula
B2:B86	B2	=LET(ft,WORKDAY.INTL(DATE(2022,1,1),1,"1110111"),s,SEQUENCE(COUNTA(A2:A105),,ft,7),SORT(IF(YEAR(s)=2022,s,s-359)))
C2:C86	C2	=TEXT(B2#,"ddd")
Dynamic array formulas.

This just fills out all the Thursdays first, then fills in the Tuesdays in order. If you want to spread out the Tuesdays, it would be tougher. If you don't have Excel 365, it would be tougher.

 

[JEF13]

What version of Excel do you have? The best answer often depends on what functions are available. Please update your profile.

Hi Eric, I have Office Professional Plus 2019. If you have a formula that works in this version of Excel please let me know.

 

[Eric W]

This works in Excel 2019. Put your starting date in B2. Then put the formula in B3 and drag down:

Book1
	A	B
1	Guests	Date
2	Guest 1	1/6/2022
3	Guest 2	1/11/2022
4	Guest 3	1/13/2022
5	Guest 4	1/18/2022
6	Guest 5	1/20/2022
7	Guest 6	1/25/2022
8	Guest 7	1/27/2022
9	Guest 8	2/1/2022
10	Guest 9	2/3/2022
11	Guest 10	2/8/2022
12	Guest 11	2/10/2022
13	Guest 12	2/15/2022
14	Guest 13	2/17/2022
15	Guest 14	2/22/2022
16	Guest 15	2/24/2022
17	Guest 16	3/1/2022
18	Guest 17	3/3/2022
19	Guest 18	3/8/2022
20	Guest 19	3/10/2022
21	Guest 20	3/15/2022
22	Guest 21	3/17/2022
23	Guest 22	3/22/2022
24	Guest 23	3/24/2022
25	Guest 24	3/29/2022
26	Guest 25	3/31/2022
27	Guest 26	4/5/2022
28	Guest 27	4/7/2022
29	Guest 28	4/12/2022
30	Guest 29	4/14/2022
31	Guest 30	4/19/2022
32	Guest 31	4/21/2022
33	Guest 32	4/26/2022
34	Guest 33	4/28/2022
35	Guest 34	5/3/2022
36	Guest 35	5/5/2022
37	Guest 36	5/10/2022
38	Guest 37	5/12/2022
39	Guest 38	5/17/2022
40	Guest 39	5/19/2022
41	Guest 40	5/24/2022
42	Guest 41	5/26/2022
43	Guest 42	5/31/2022
44	Guest 43	6/2/2022
45	Guest 44	6/7/2022
46	Guest 45	6/9/2022
47	Guest 46	6/14/2022
48	Guest 47	6/16/2022
49	Guest 48	6/21/2022
50	Guest 49	6/23/2022
51	Guest 50	6/28/2022
52	Guest 51	6/30/2022
53	Guest 52	7/5/2022
54	Guest 53	7/7/2022
55	Guest 54	7/12/2022
56	Guest 55	7/14/2022
57	Guest 56	7/19/2022
58	Guest 57	7/21/2022
59	Guest 58	7/26/2022
60	Guest 59	7/28/2022
61	Guest 60	8/2/2022
62	Guest 61	8/4/2022
63	Guest 62	8/9/2022
64	Guest 63	8/11/2022
65	Guest 64	8/16/2022
66	Guest 65	8/18/2022
67	Guest 66	8/23/2022
68	Guest 67	8/25/2022
69	Guest 68	9/1/2022
70	Guest 69	9/8/2022
71	Guest 70	9/15/2022
72	Guest 71	9/22/2022
73	Guest 72	9/29/2022
74	Guest 73	10/6/2022
75	Guest 74	10/13/2022
76	Guest 75	10/20/2022
77	Guest 76	10/27/2022
78	Guest 77	11/3/2022
79	Guest 78	11/10/2022
80	Guest 79	11/17/2022
81	Guest 80	11/24/2022
82	Guest 81	12/1/2022
83	Guest 82	12/8/2022
84	Guest 83	12/15/2022
85	Guest 84	12/22/2022
86	Guest 85	12/29/2022
87
Sheet1

Cell Formulas
Range	Formula
B3:B86	B3	=WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA(A$2:A$103)-52),"1110111","1010111"))

 

[JEF13]

What version of Excel do you have? The best answer often depends on what functions are available. Please update your profile.

If you have Excel 365, try:

Book1
	A	B	C
1	Guests	Date
2	Guest 1	1/6/2022	Thu
3	Guest 2	1/11/2022	Tue
4	Guest 3	1/13/2022	Thu
5	Guest 4	1/18/2022	Tue
6	Guest 5	1/20/2022	Thu
7	Guest 6	1/25/2022	Tue
8	Guest 7	1/27/2022	Thu
9	Guest 8	2/1/2022	Tue
10	Guest 9	2/3/2022	Thu
11	Guest 10	2/8/2022	Tue
12	Guest 11	2/10/2022	Thu
13	Guest 12	2/15/2022	Tue
14	Guest 13	2/17/2022	Thu
15	Guest 14	2/22/2022	Tue
16	Guest 15	2/24/2022	Thu
17	Guest 16	3/1/2022	Tue
18	Guest 17	3/3/2022	Thu
19	Guest 18	3/8/2022	Tue
20	Guest 19	3/10/2022	Thu
21	Guest 20	3/15/2022	Tue
22	Guest 21	3/17/2022	Thu
23	Guest 22	3/22/2022	Tue
24	Guest 23	3/24/2022	Thu
25	Guest 24	3/29/2022	Tue
26	Guest 25	3/31/2022	Thu
27	Guest 26	4/5/2022	Tue
28	Guest 27	4/7/2022	Thu
29	Guest 28	4/12/2022	Tue
30	Guest 29	4/14/2022	Thu
31	Guest 30	4/19/2022	Tue
32	Guest 31	4/21/2022	Thu
33	Guest 32	4/26/2022	Tue
34	Guest 33	4/28/2022	Thu
35	Guest 34	5/3/2022	Tue
36	Guest 35	5/5/2022	Thu
37	Guest 36	5/10/2022	Tue
38	Guest 37	5/12/2022	Thu
39	Guest 38	5/17/2022	Tue
40	Guest 39	5/19/2022	Thu
41	Guest 40	5/24/2022	Tue
42	Guest 41	5/26/2022	Thu
43	Guest 42	5/31/2022	Tue
44	Guest 43	6/2/2022	Thu
45	Guest 44	6/7/2022	Tue
46	Guest 45	6/9/2022	Thu
47	Guest 46	6/14/2022	Tue
48	Guest 47	6/16/2022	Thu
49	Guest 48	6/21/2022	Tue
50	Guest 49	6/23/2022	Thu
51	Guest 50	6/28/2022	Tue
52	Guest 51	6/30/2022	Thu
53	Guest 52	7/5/2022	Tue
54	Guest 53	7/7/2022	Thu
55	Guest 54	7/12/2022	Tue
56	Guest 55	7/14/2022	Thu
57	Guest 56	7/19/2022	Tue
58	Guest 57	7/21/2022	Thu
59	Guest 58	7/26/2022	Tue
60	Guest 59	7/28/2022	Thu
61	Guest 60	8/2/2022	Tue
62	Guest 61	8/4/2022	Thu
63	Guest 62	8/9/2022	Tue
64	Guest 63	8/11/2022	Thu
65	Guest 64	8/16/2022	Tue
66	Guest 65	8/18/2022	Thu
67	Guest 66	8/23/2022	Tue
68	Guest 67	8/25/2022	Thu
69	Guest 68	9/1/2022	Thu
70	Guest 69	9/8/2022	Thu
71	Guest 70	9/15/2022	Thu
72	Guest 71	9/22/2022	Thu
73	Guest 72	9/29/2022	Thu
74	Guest 73	10/6/2022	Thu
75	Guest 74	10/13/2022	Thu
76	Guest 75	10/20/2022	Thu
77	Guest 76	10/27/2022	Thu
78	Guest 77	11/3/2022	Thu
79	Guest 78	11/10/2022	Thu
80	Guest 79	11/17/2022	Thu
81	Guest 80	11/24/2022	Thu
82	Guest 81	12/1/2022	Thu
83	Guest 82	12/8/2022	Thu
84	Guest 83	12/15/2022	Thu
85	Guest 84	12/22/2022	Thu
86	Guest 85	12/29/2022	Thu
Sheet1

Cell Formulas
Range	Formula
B2:B86	B2	=LET(ft,WORKDAY.INTL(DATE(2022,1,1),1,"1110111"),s,SEQUENCE(COUNTA(A2:A105),,ft,7),SORT(IF(YEAR(s)=2022,s,s-359)))
C2:C86	C2	=TEXT(B2#,"ddd")
Dynamic array formulas.

This just fills out all the Thursdays first, then fills in the Tuesdays in order. If you want to spread out the Tuesdays, it would be tougher. If you don't have Excel 365, it would be tougher.

 

[JEF13]

Thanks for the updates. It appears to be working. I will test more and report back. By the way, instead of have column C, I changed the formatting to display the date and the day. The column takes up more room as the width is longer, but the one column shows the day and dates. I appreciate your continued help.

 

[Eric W]

Happy to help! Thanks for the update. Let us know if you have any followup questions.

 

[JEF13]

Eric, I have a question for you.

In the "Guest" column I have cells refer to another sheet. As an example, cell A63 would have the formula "=Guests!A63." The reason I do this is that as guests are added the list of names in column A updates automatically. There are times when new guests book an interview which takes place earlier that previously scheduled guests. The list automatically updates based on the interview date.

Suppose cell A63 is blank because there is not a guest. The formula still thinks that there is a guest in A63 because it sees the formula. Is there any way around this so that the formula would skip an "empty" cell with no name in it?

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Guests	Date
2	Guest 1	Thu, 1/06/2022		Guest 1
3	Guest 2	Tue, 1/11/2022		Guest 2
4	Guest 3	Thu, 1/13/2022		Guest 3
5	Guest 4	Tue, 1/18/2022		Guest 4
6	Guest 5	Thu, 1/20/2022		Guest 5
7	Guest 6	Tue, 1/25/2022		Guest 6
8	Guest 7	Thu, 1/27/2022		Guest 7
9	Guest 8	Tue, 2/01/2022		Guest 8
10	Guest 9	Thu, 2/03/2022		Guest 9
11	Guest 10	Tue, 2/08/2022		Guest 10
12	Guest 11	Thu, 2/10/2022		Guest 11
13	Guest 12	Tue, 2/15/2022		Guest 12
14	Guest 13	Thu, 2/17/2022		Guest 13
15	Guest 14	Tue, 2/22/2022		Guest 14
16	Guest 15	Thu, 2/24/2022		Guest 15
17	Guest 16	Tue, 3/01/2022		Guest 16
18	Guest 17	Thu, 3/03/2022		Guest 17
19	Guest 18	Tue, 3/08/2022		Guest 18
20	Guest 19	Thu, 3/10/2022		Guest 19
21	Guest 20	Tue, 3/15/2022		Guest 20
22	Guest 21	Thu, 3/17/2022		Guest 21
23	Guest 22	Tue, 3/22/2022		Guest 22
24	Guest 23	Thu, 3/24/2022		Guest 23
25	Guest 24	Tue, 3/29/2022		Guest 24
26	Guest 25	Thu, 3/31/2022		Guest 25
27	Guest 26	Tue, 4/05/2022		Guest 26
28	Guest 27	Thu, 4/07/2022		Guest 27
29	Guest 28	Tue, 4/12/2022		Guest 28
30	Guest 29	Thu, 4/14/2022		Guest 29
31	Guest 30	Tue, 4/19/2022		Guest 30
32	Guest 31	Thu, 4/21/2022		Guest 31
33	Guest 32	Tue, 4/26/2022		Guest 32
34	Guest 33	Thu, 4/28/2022		Guest 33
35	Guest 34	Tue, 5/03/2022		Guest 34
36	Guest 35	Thu, 5/05/2022		Guest 35
37	Guest 36	Tue, 5/10/2022		Guest 36
38	Guest 37	Thu, 5/12/2022		Guest 37
39	Guest 38	Tue, 5/17/2022		Guest 38
40	Guest 39	Thu, 5/19/2022		Guest 39
41	Guest 40	Tue, 5/24/2022		Guest 40
42	Guest 41	Thu, 5/26/2022		Guest 41
43	Guest 42	Tue, 5/31/2022		Guest 42
44	Guest 43	Thu, 6/02/2022		Guest 43
45	Guest 44	Tue, 6/07/2022		Guest 44
46	Guest 45	Thu, 6/09/2022		Guest 45
47	Guest 46	Tue, 6/14/2022		Guest 46
48	Guest 47	Thu, 6/16/2022		Guest 47
49	Guest 48	Tue, 6/21/2022		Guest 48
50	Guest 49	Thu, 6/23/2022		Guest 49
51	Guest 50	Tue, 6/28/2022		Guest 50
52	Guest 51	Thu, 6/30/2022		Guest 51
53	Guest 52	Tue, 7/05/2022		Guest 52
54	Guest 53	Thu, 7/07/2022		Guest 53
55	Guest 54	Tue, 7/12/2022		Guest 54
56	Guest 55	Thu, 7/14/2022		Guest 55
57	Guest 56	Thu, 7/21/2022		Guest 56
58	Guest 57	Thu, 7/28/2022		Guest 57
59	Guest 58	Thu, 8/04/2022		Guest 58
60	Guest 59	Thu, 8/11/2022		Guest 59
61	Guest 60	Thu, 8/18/2022		Guest 60
62	Guest 61	Thu, 8/25/2022		Guest 61
63	Guest 62	Thu, 9/01/2022		Guest 62
64	Guest 63	Thu, 9/08/2022		Guest 63
65	Guest 64	Thu, 9/15/2022		Guest 64
66	Guest 65	Thu, 9/22/2022		Guest 65
67	Guest 66	Thu, 9/29/2022		Guest 66
68	Guest 67	Thu, 10/06/2022		Guest 67
69	Guest 68	Thu, 10/13/2022		Guest 68
70	Guest 69	Thu, 10/20/2022		Guest 69
71	Guest 70	Thu, 10/27/2022		Guest 70
72	Guest 71	Thu, 11/03/2022		Guest 71
73	Guest 72	Thu, 11/10/2022		Guest 72
74	Guest 73	Thu, 11/17/2022		Guest 73
75	Guest 74	Thu, 11/24/2022		Guest 74
76	Guest 75	Thu, 12/01/2022		Guest 75
77	Guest 76	Thu, 12/08/2022		Guest 76
78	Guest 77	Thu, 12/15/2022		Guest 77
79	Guest 78	Thu, 12/22/2022		Guest 78
80	Guest 79	Thu, 12/29/2022		Guest 79
81
82
83
84
Sheet1

Cell Formulas
Range	Formula
A2:A84	A2	=D2&""
B3:B84	B3	=IF(LEN(A3)>1,WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(SUMPRODUCT(--(LEN(A$2:A$105)>1))-52),"1110111","1010111")),"")

 

[JEF13]

Brilliant! Works Great!