Existence of a TV Show over time???

east3rd

New Member
Joined
Sep 8, 2008
Messages
48
I have a tough one here! I am trying to calculate how many TV shows we had on our platform for each month of the year. My data consists of a title, license window start, license window end, and the content provider. For example:

titlelicensing_window_startlicensing_window_endprovider_id
Show #1
1/1/2020​
7/1/2020​
TV Creator 1

If I just create a pivot, the resulting table will only show me that I have 1 title in Jan 2020 and/or 1 title in July 2020. But what I need to know is how many titles I had for any given month for all the months in between. In the case above I want to know that this title "existed" in Jan, Feb, Mar, Apr... Solving this is beyond my skills so any thoughts or suggestions would be great.
Thank you

PS - here's some sample data to work with

titlelicensing_window_startlicensing_window_endprovider_id
Show #1
9/5/2020​
7/1/2021​
TV Creator 1
Show #2
8/29/2020​
7/1/2021​
TV Creator 2
Show #3
8/22/2020​
7/1/2021​
TV Creator 3
Show #4
8/15/2020​
7/1/2021​
TV Creator 1
Show #5
8/8/2020​
7/1/2021​
TV Creator 2
Show #6
8/1/2020​
7/1/2021​
TV Creator 3
Show #7
7/25/2020​
7/1/2021​
TV Creator 1
Show #8
7/18/2020​
7/1/2021​
TV Creator 2
Show #9
7/11/2020​
7/1/2021​
TV Creator 3
Show #10
5/23/2020​
4/2/2021​
TV Creator 1
Show #11
5/16/2020​
4/2/2021​
TV Creator 2
Show #12
5/9/2020​
4/2/2021​
TV Creator 3
Show #13
5/2/2020​
4/2/2021​
TV Creator 1
Show #14
4/25/2020​
4/2/2021​
TV Creator 2
Show #15
4/18/2020​
4/2/2021​
TV Creator 3
Show #16
4/11/2020​
4/2/2021​
TV Creator 1
Show #17
4/5/2020​
1/1/2021​
TV Creator 2
Show #18
4/5/2020​
9/1/2020​
TV Creator 3
Show #19
3/29/2020​
9/1/2020​
TV Creator 1
Show #20
3/29/2020​
1/1/2021​
TV Creator 2
Show #21
3/22/2020​
9/1/2020​
TV Creator 3
Show #22
3/22/2020​
1/1/2021​
TV Creator 1
Show #23
3/15/2020​
1/1/2021​
TV Creator 2
Show #24
3/15/2020​
1/1/2021​
TV Creator 3
 
@Eric W Your solution seems to do the trick with the exception of partial months. I do need to know if a title appeared in a given month even if just for 1 day. Please let me know what tweak is needed. Thanks!
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
With the same layout as before, put this in H2 and drag down:

=COUNTIFS(B$2:B$25,"<="&EOMONTH(G2,0),C$2:C$25,">="&G2)
 
Upvote 0
How about something like this:

Book1
ABCDEFGHIJKL
1titlelicensing_window_startlicensing_window_endprovider_idMonthTV Creator 1TV Creator 2TV Creator 3Total
2Show #19/5/20207/1/2021TV Creator 1January-2020    
3Show #28/29/20207/1/2021TV Creator 2February-2020    
4Show #38/22/20207/1/2021TV Creator 3March-20202226
5Show #48/15/20207/1/2021TV Creator 1April-202034411
6Show #58/8/20207/1/2021TV Creator 2May-202055515
7Show #68/1/20207/1/2021TV Creator 3June-202055515
8Show #77/25/20207/1/2021TV Creator 1July-202066618
9Show #87/18/20207/1/2021TV Creator 2August-202078823
10Show #97/11/20207/1/2021TV Creator 3September-202088824
11Show #105/23/20204/2/2021TV Creator 1October-202078621
12Show #115/16/20204/2/2021TV Creator 2November-202078621
13Show #125/9/20204/2/2021TV Creator 3December-202078621
14Show #135/2/20204/2/2021TV Creator 1January-202178621
15Show #144/25/20204/2/2021TV Creator 2February-202165516
16Show #154/18/20204/2/2021TV Creator 3March-202165516
17Show #164/11/20204/2/2021TV Creator 1April-202165516
18Show #174/5/20201/1/2021TV Creator 2May-20213339
19Show #184/5/20209/1/2020TV Creator 3June-20213339
20Show #193/29/20209/1/2020TV Creator 1July-20213339
21Show #203/29/20201/1/2021TV Creator 2August-2021    
22Show #213/22/20209/1/2020TV Creator 3September-2021    
23Show #223/22/20201/1/2021TV Creator 1October-2021    
24Show #233/15/20201/1/2021TV Creator 2November-2021    
25Show #243/15/20201/1/2021TV Creator 3December-2021    
Sheet17
Cell Formulas
RangeFormula
H2:J25H2=COUNTIFS($B$2:$B$25,"<="&EOMONTH($G2,0),$C$2:$C$25,">="&$G2,$D$2:$D$25,H$1)
L2:L25L2=SUM(H2:J2)


I played with the formatting a bit. The months in column G are still the same, the 1st of each month, I just formatted them different. I also changed a sheet setting to not show 0 values.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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