Help Analysing Data using Excel

Hello All

I'm not brilliant with Excel so forgive me if I explain this badly or am asking for the impossible. I work in legal for a TV firm I'm trying to make some kind of template to help us keep track of some legal obligations we have around exploiting content. We are getting our systems developed to adapt but in the meantime I need some help.

So we have rules on some content where we can only have so many episodes from a series live on our system at any one time. However we also have a max number of days any one episode can be exploited for. So how content is scheduled really does dictate how long and how many episodes of a programme appear on our system at any one time.

Now the individual episodes go live on the tx date and are populated with the max number of days as the end date

I'm trying to use the individual episode titles, Tx Date and End Dates to create a template that I can drop that data into along with the "Max Number of episodes live at one time" and have it identify any breaches where we might need to amend the end dates to ensure we are in adherence to these rules.

I either need a date field I can put the current date in and have it tell me how many episodes are live OR Have something that looks at all the data a breach and which the episode that needs to be amended and potentially to what date if that's even possible.

I hope I explained that correctly? Any ideas are very very welcome it's driving me crazy having to work these things out week on week.

Here's some basic data there are different series with different rules so I need something adaptable if possible.

Max Number of Episodes at any one time = 5

Episode Number Tx Date End Date

Episode 1 01/01/2017 10/01/2017
Episode 2 02/01/2017 11/01/2017
Episode 3 03/01/2017 12/01/2017
Episode 4 03/01/2017 12/01/2017
Episode 5 04/01/2017 13/01/2017
Episode 6 04/01/2017 13/01/2017
Episode 7 05/01/2017 14/01/2017
Episode 8 06/01/2017 15/01/2017
Episode 9 07/01/2017 16/01/2017

=SUMPRODUCT((_t[Tx Date]<=I3)*(_t[End Date]>=I3))

Thank you for your help!

Can I ask what the dates in the column next to the overlap represent?

So ideally when the max number of episodes breaches we would like episode that has been up there longest to come down on that date. Is there a formua can work that out?

so when the overlap column hits a certain number 5 the Tx date for that episode is the date that the first episode in the series comes down, is that possible? I've put what I mean manually on the screen grab below.



Screen Shot 2017-09-27 at 14.17.22.png