End date for recurring events which happens few times a week.

[Revmatcher]

Hello everyone!

I have tried Googling and searching this forum for this answer to no avail so I was hoping that someone could help me figure this one out since I can't seem to do so.

I am trying to figure out the following.

I have a start date for a few events happening during a week. Lets say each event happens individually each day through out the week excluding weekends. So Event 1 happens Monday, Event 2 happens Tuesday, Event 3 happens Wednesday, Event 4 happens Thursday and Event 5 happens Friday.

There are 14 Event 1
There are 45 Event 2
There are 17 Event 3
There are 3 Event 4
There are 60 Event 5

What I want to know is the due date for each one of those events.

So if "Event week" started Monday May5th, what date would Event 1 end considering it happens 14 times and on Mondays? and Event 2? 3?etc...

I have tried a couple of formulas but non of them seem to do what I need.

Any help would be appreciated.

Thank you!

Rev.

 

[Redwolfx]

Can you post what your initial data looks like then what output you are looking for? If Event 1 happens 14 Times does that mean the due day is 14 Mondays from now?

 

[Revmatcher]

Can you post what your initial data looks like then what output you are looking for? If Event 1 happens 14 Times does that mean the due day is 14 Mondays from now?

Hi redwolf,

So for example I have 52 "Event 1"s that I have to consume at a rate of once per week starting from 5/5/14. What date will Episode 52 fall on? Thanks again mate.

Program	Total amount of Episodes	X Per week excluding weekends Day	Contract Start Date	End Date
Event 1	52	1 Monday	5/5/14
Event 2	60	1 Tuesday	5/6/14
Event 3	14	1 Wednesday	5/7/14
Event 4	52	1 Thursday	5/8/14
Event 5	26	1 Friday	5/9/14

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Redwolfx]

Will there ever be times where it is 2x per week or more?

 

[Revmatcher]

Will there ever be times where it is 2x per week or more?

Actually, yes, in the near future I will have the same Event repeated twice or three times a week.

 

[Redwolfx]

Actually, yes, in the near future I will have the same Event repeated twice or three times a week.

And can you show what that will look like on your sheet?

 

[Revmatcher]

And can you show what that will look like on your sheet?

I noticed that it doesn't matter what day it falls on, just only how many times a week it happens since the day that the event happens changes every week.

I have simplified (I hope) the data.

Event 1 has a total of 52 episodes and happens once a week

Event 2 has a total of 60 episodes and it happens twice a week.

Each even will simply have a specific Start Date

Hope this helps.

Program	Total amount of Episodes	X Per week	Contract Start Date	End Date
Event 1	52	1	5/5/14
Event 2	60	2	5/6/14
Event 3	14	1	5/7/14
Event 4	52	3	5/8/14
Event 5	26	1	5/9/14

<tbody>
</tbody>

 

[Redwolfx]

How do we know what day the 2nd episode in a week falls on?

 

[Revmatcher]

How do we know what day the 2nd episode in a week falls on?

That is an excellent point. Lets just say that it's like this.

Event 1 Start date: 5/5/2014 Monday (Every week)

Event 2 Start date: 5/6/2014 Tuesday and Thursdays (Every week)

Event 3 Start date: 5/7/2014 Wednesday (Every week)

Event 4 Start date: 5/8/2014 Thursday and Fridays (Every week)

Event 5 Start date: 5/9/2014 Friday (Every week)

 

[Redwolfx]

See if this works for you:

The Formula in E2 can be dragged down

Program	Total Amount of Episodes	X Per Week excluding Weekend Days	Contract Start Date	End Date
Event 1	52	2 Monday, Wednesday	5/5/14	=WORKDAY.INTL(D2,B2-1,TEXT(1111111-IF(IFERROR(FIND("Monday",C2,1)>1,0),1000000)-IF(IFERROR(FIND("Tuesday",C2,1)>1,0),100000)-IF(IFERROR(FIND("Wednesday",C2,1)>1,0),10000)-IF(IFERROR(FIND("Thursday",C2,1)>1,0),1000)-IF(IFERROR(FIND("Friday",C2,1)>1,0),100),"0000000"))
Event 2	60	2 Tuesday, Thursday	5/6/14
Event 3	14	1 Wednesday	5/7/14
Event 4	52	1 Thursday	5/8/14
Event 5	26	1 Friday	5/9/14

<TBODY>
</TBODY>

The formula Searched C2 for the Words "Monday" "Tuesday" and so on, then inputs the correct sequence for the workday function.