Excel Formula For Calculating Variable Dates

J

JEF13

New Member
Joined
Jun 30, 2018
Messages
49
Office Version
  1. 2019
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
 
Sort by date Sort by votes
Eric, it works beautifully. THANK YOU.

One last question for you.

Down the road if I ever wanted to change the internal shows instead of being every 5th episode to every nth episode (including 0), where is this done in the formula?
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
That actually turns out to be pretty easy, an unexpected benefit of my latest approach. In the G2 formula, change the 4 to be n-1,

Rich (BB code): 
=INT((COUNTA(D2:D105)-1)/4)

In the A2 formula, just change the 5 to n:

Rich (BB code): 
=IF(B2="","",IF(AND(MOD(ROW()-1,5)=0,SUMPRODUCT(SIGN(COUNTIF($E$2:$E$105,$A$1:$A1)))<COUNTA($E$2:$E$105)),INDEX($E$2:$E$105,SUMPRODUCT(SIGN(COUNTIF($E$2:$E$105,$A$1:$A1)))+1),INDEX($D$2:$D$105,SUMPRODUCT(SIGN(COUNTIF($D$2:$D$105,$A$1:$A1)))+1)))

The B3 formula doesn't need to be changed at all. You can easily put your n in some cell, and just reference that cell in the formulas. And if you want 0 internal shows, you can just leave the E column empty, regardless of what n you use.


Anyway, I'm glad it works for you! ?
 
Upvote 0
Eric, thanks for the suggestion. I don't want to over step my bounds. The one quick heads up is when I change the formula to =INT((COUNTA(D2:D105)-1)/(N1-1)) and the value for cell N changes from 5 to 10 or 2 the placing of the internal guests does not change. Please let me know if I have done something incorrectly.

Thanks.

Jeffrey
 
Upvote 0
Did you change both formulas?

Book1
ABCDEFGMN
1ShowDateGuestsInternal# of internal shows neededInternal every:10
2Guest 1Thu, 1/06/2022Guest 1Internal Show 16
3Guest 2Tue, 1/11/2022Guest 2Internal Show 2
4Guest 3Thu, 1/13/2022Guest 3Internal Show 3Notes
5Guest 1Tue, 1/18/2022Guest 1Internal Show 4 
6Guest 5Thu, 1/20/2022Guest 5Internal Show 5
7Guest 6Tue, 1/25/2022Guest 6Internal Show 6
8Guest 7Thu, 1/27/2022Guest 7Internal Show 7
9Guest 2Tue, 2/01/2022Guest 2Internal Show 8
10Guest 9Thu, 2/03/2022Guest 9Internal Show 9
11Internal Show 1Tue, 2/08/2022Guest 10Internal Show 10
12Guest 10Thu, 2/10/2022Guest 11Internal Show 11
13Guest 11Tue, 2/15/2022Guest 12Internal Show 12
14Guest 12Thu, 2/17/2022Guest 13Internal Show 13
15Guest 13Tue, 2/22/2022Guest 14Internal Show 14
16Guest 14Thu, 2/24/2022Guest 15Internal Show 15
17Guest 15Tue, 3/01/2022Guest 16Internal Show 16
18Guest 16Thu, 3/03/2022Guest 17
19Guest 17Tue, 3/08/2022Guest 18
20Guest 18Thu, 3/10/2022Guest 19
21Internal Show 2Tue, 3/15/2022Guest 20
22Guest 19Thu, 3/17/2022Guest 21
23Guest 20Tue, 3/22/2022Guest 22
24Guest 21Thu, 3/24/2022Guest 23
25Guest 22Tue, 3/29/2022Guest 24
26Guest 23Thu, 3/31/2022Guest 25
27Guest 24Tue, 4/05/2022Guest 26
28Guest 25Thu, 4/07/2022Guest 27
29Guest 26Tue, 4/12/2022Guest 28
30Guest 27Thu, 4/14/2022Guest 29
31Internal Show 3Thu, 4/21/2022Guest 30
32Guest 28Thu, 4/28/2022Guest 31
Sheet5
Cell Formulas
RangeFormula
A2:A32A2=IF(B2="","",IF(AND(MOD(ROW()-1,$N$1)=0,SUMPRODUCT(SIGN(COUNTIF($E$2:$E$105,$A$1:$A1)))<COUNTA($E$2:$E$105)),INDEX($E$2:$E$105,SUMPRODUCT(SIGN(COUNTIF($E$2:$E$105,$A$1:$A1)))+1),INDEX($D$2:$D$105,SUMPRODUCT(SIGN(COUNTIF($D$2:$D$105,$A$1:$A1)))+1)))
G2G2=INT((COUNTA(D2:D105)-1)/(N1-1))
B3:B32B3=IF(ROWS(B$2:B3)>COUNTA($D$2:$D$105)+MIN(COUNTA($E$2:$E$105),$G$2),"",WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA($D$2:$D$105)+MIN(COUNTA($E$2:$E$105),$G$2)-52),"1110111","1010111")))
G5G5=IF(COUNTA(D2:E105)>104,"Too many shows scheduled for 1 year",IF(COUNTA(E2:E105)<G2,"Not enough internal shows - filling with guests",""))
 
Upvote 0
Thanks, Eric, for your help. Your formulas save a tremendous amount of time!
 
Upvote 0
Hi Eric and a Happy July 4 to all.

Eric, your formulas have been a game-changer and are working great! I have taken the original spreadsheet and put it onto Google Sheets so that the team can easily access the information.

As we prepare for 2023 I created a new podcast spreadsheet and noticed two things aren't happening:

1. The name of the guest does not appear in Column A
2. The date for the guest does not appear even when the guest's name appears in Column A

I have enclosed two mini Sheets which has the data.

I'm wondering what needs to be changed so that the data displays as intended.

As always, your help and insights are appreciated.

Thanks.

Jeffrey

Below is the first part of the spreadsheet which displays the Guest name, date of episode and episode number. You'll notice that Guest 6 and Guest 7 are missing the date. Guest 2 onwards is missing the Episode numnber.

Podcast Tracker 2022 (1).xlsx
ABC
1GuestDateEpisode
2Guest 1Tue 01/03/20230192
3Guest 2Thu 01/05/2023 
4Guest 3Thu 01/12/2023 
5Guest 4Thu 01/19/2023 
6Episode 192Thu 01/26/2023 
7Guest 5Thu 02/02/2023 
8Guest 6  
9Guest 7  
IAP 2023 Podcast
Cell Formulas
RangeFormula
A2:A7A2=IF(B2="","",IF(AND(MOD(ROW()-1,$S$113)=0,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A1)))<COUNTA($N$114:$N$199)),INDEX($N$114:$N$199,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A1)))+1),INDEX($M$114:$M$199,SUMPRODUCT(SIGN(COUNTIF($M$114:$M$199,$A$1:$A1)))+1)))
B3:B9B3=IF(ROWS(B$2:B3)>COUNTA($M$114:$M$199)+MIN(COUNTA($N$114:$N$199),$P$114),"",WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA($M$114:$M$199)+MIN(COUNTA($N$114:$N$199),$P$114)-52),"1110111","1010111")))
C3:C9C3=IF(J3>0,C2+1,"")
A8A8=IF(B7="","",IF(AND(MOD(ROW()-1,$S$113)=0,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A7)))<COUNTA($N$114:$N$199)),INDEX($N$114:$N$199,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A7)))+1),INDEX($M$114:$M$199,SUMPRODUCT(SIGN(COUNTIF($M$114:$M$199,$A$1:$A7)))+1)))
A9A9=IF(B7="","",IF(AND(MOD(ROW()-1,$S$113)=0,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A8)))<COUNTA($N$114:$N$199)),INDEX($N$114:$N$199,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A8)))+1),INDEX($M$114:$M$199,SUMPRODUCT(SIGN(COUNTIF($M$114:$M$199,$A$1:$A8)))+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A1000Cell Valuecontains "Episode"textNO


Below is the part of the spreadsheet where the guest information is entered as well as some of the automated calculations that were created:

Podcast Tracker 2022 (1).xlsx
KLMNOPQRS
113DateEmailGuestsInternal# of Internal Shows NeededNumber of Internal Shows Every N Weeks5
1149/28/2022email@email.comGuest 1Episode 192-1
11510/5/2022email@email.comGuest 2Episode 197
11610/12/2022email@email.comGuest 3Episode 202
11710/12/2022email@email.comGuest 4Episode 207 
11810/20/2022email@email.comGuest 5Episode 212
11911/2/2022email@email.comGuest 6Episode 217
12011/3/2022email@email.comGuest 7Episode 222
121Episode 227
122Episode 232
123Episode 237
124Episode 242
125Episode 247
126Episode 252
127Episode 257
128Episode 262
129Episode 267
130Episode 272
131Episode 277
132Episode 282
133Episode 287
IAP 2023 Podcast
Cell Formulas
RangeFormula
P114P114=INT((COUNTA(J200:J209)-1)/(S113-1))
P117P117=IF(COUNTA(J200:K209)>104,"Too many shows scheduled for 1 year",IF(COUNTA(K200:K209)<P114,"Not enough internal shows - filling with guests",""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M114:M133,M135,M139Cell Valuecontains "JeffreyEpisode"textNO
N116,N119,N122,N125,N128,N131Cell Valuecontains "JeffreyEpisode"textNO
N115,N118,N121,N124,N127,N130,N133Cell Valuecontains "JeffreyEpisode"textNO


As always, thanks for your help.

Jeffrey
 
Upvote 0
The formulas SEEM to be working OK, but it looks like you've rearranged the sheet and not updated the formulas to point to the right ranges. For example, the P114 formula references the J column and there's nothing in the J column (at least not that you included in your mini-sheet). Then the B formula references the P114 formula and runs into trouble. I updated the references in the formulas, and they seem to be working right now, but my memory is foggy, I don't remember exactly what the J was supposed to hold. The C formula also referenced column J, and I updated that, but I'm not sure that it's right.

Book1
ABCDEFGHIJKLMNOPQRS
1GuestDateEpisode
2Guest 11/3/2023192
3Guest 21/5/2023193
4Guest 31/12/2023194
5Guest 41/19/2023195
6Episode 1921/26/2023196
7Guest 52/2/2023197
8Guest 62/9/2023198
9Guest 72/16/2023199
10Guest 82/23/2023200
11Episode 1973/2/2023201
12Guest 93/9/2023202
111 
112 
113 DateEmailGuestsInternal# of Internal Shows NeededNumber of Internal Shows Every N Weeks5
1149/28/2022email@email.comGuest 1Episode 1922
11510/5/2022email@email.comGuest 2Episode 197
11610/12/2022email@email.comGuest 3Episode 202
11710/12/2022email@email.comGuest 4Episode 207 
11810/20/2022email@email.comGuest 5Episode 212
11911/2/2022email@email.comGuest 6Episode 217
12011/3/2022email@email.comGuest 7Episode 222
1211/1/2011xxxGuest 8Episode 227
1222/2/2022yyyGuest 9Episode 232
123Episode 237
124Episode 242
125Episode 247
Sheet7
Cell Formulas
RangeFormula
C3:C12C3=IF(B3>0,C2+1,"")
A2:A12A2=IF(B2="","",IF(AND(MOD(ROW()-1,$S$113)=0,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A1)))<COUNTA($N$114:$N$199)),INDEX($N$114:$N$199,SUMPRODUCT(SIGN(COUNTIF($N$114:$N$199,$A$1:$A1)))+1),INDEX($M$114:$M$199,SUMPRODUCT(SIGN(COUNTIF($M$114:$M$199,$A$1:$A1)))+1)))
B3:B12,B111:B113B3=IF(ROWS(B$2:B3)>COUNTA($M$114:$M$199)+MIN(COUNTA($N$114:$N$199),$P$114),"",WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA($M$114:$M$199)+MIN(COUNTA($N$114:$N$199),$P$114)-52),"1110111","1010111")))
P114P114=INT((COUNTA(M114:M209)-1)/(S113-1))
P117P117=IF(COUNTA(J114:K209)>104,"Too many shows scheduled for 1 year",IF(COUNTA(K114:K209)<P114,"Not enough internal shows - filling with guests",""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Eric, thanks for the corrections, much appreciated.

I looked into P114 by going back to the original spreadsheet which is below. Looking back at the original I'm not quite sure what this did as well. If you're able to recall what the purpose is based on the information below I'd be both grateful and appreciative.

Podcast Master Formula For Schedule Automation.xlsx
ABCDEFGHIJKLMN
1ShowDateGuestsInternal# of Internal ShowsN Internal Shows Per Episode20
2Guest 1Thu 01/06/2022Guest 1Internal 13
3Guest 2Tue 01/11/2022Guest 2Internal 2
4Guest 3Thu 01/13/2022Guest 3Internal 3
5Guest 4Tue 01/18/2022Guest 4Internal 4 
6Guest 5Thu 01/20/2022Guest 5Internal 5
7Guest 6Tue 01/25/2022Guest 6Internal 6
8Guest 7Thu 01/27/2022Guest 7Internal 7
9Guest 8Tue 02/01/2022Guest 8Internal 8
10Guest 9Thu 02/03/2022Guest 9Internal 9
11Guest 10Tue 02/08/2022Guest 10Internal 10
12Guest 11Thu 02/10/2022Guest 11Internal 11
13Guest 12Tue 02/15/2022Guest 12Internal 12
14Guest 13Thu 02/17/2022Guest 13Internal 13
Sheet1
Cell Formulas
RangeFormula
A2:A14A2=IF(B2="","",IF(AND(MOD(ROW()-1,$N$1)=0,SUMPRODUCT(SIGN(COUNTIF($E$2:$E$105,$A$1:$A1)))<COUNTA($E$2:$E$105)),INDEX($E$2:$E$105,SUMPRODUCT(SIGN(COUNTIF($E$2:$E$105,$A$1:$A1)))+1),INDEX($D$2:$D$105,SUMPRODUCT(SIGN(COUNTIF($D$2:$D$105,$A$1:$A1)))+1)))
G2G2=INT((COUNTA(D2:D105)-1)/(N1-1))
B3:B14B3=IF(ROWS(B$2:B3)>COUNTA($D$2:$D$105)+MIN(COUNTA($E$2:$E$105),$G$2),"",WORKDAY.INTL(B2,1,IF(ROWS(B$2:B3)>2*(COUNTA($D$2:$D$105)+MIN(COUNTA($E$2:$E$105),$G$2)-52),"1110111","1010111")))
G5G5=IF(COUNTA(D2:E105)>104,"Too many shows scheduled for 1 year",IF(COUNTA(E2:E105)<G2,"Not enough internal shows - filling with guests",""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A
J
  • Question
Excel Date Formula
Replies
4
Views
300
Dave Patton
D
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
P
  • Solved
Calculate based on date sum of hours based on date
2
Replies
15
Views
356
psycoperl
P
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,214,653
Messages
6,120,756
Members
448,990
Latest member
Buzzlightyear

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top