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.


[TABLE="width: 1147"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Program[/TD]
[TD]Total amount of Episodes[/TD]
[TD]X Per week
excluding weekends Day[/TD]
[TD]Contract Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Event 1[/TD]
[TD]52[/TD]
[TD]1 Monday[/TD]
[TD]5/5/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 2[/TD]
[TD]60[/TD]
[TD]1 Tuesday[/TD]
[TD]5/6/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 3[/TD]
[TD]14[/TD]
[TD]1 Wednesday[/TD]
[TD]5/7/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 4[/TD]
[TD]52[/TD]
[TD]1 Thursday[/TD]
[TD]5/8/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 5[/TD]
[TD]26[/TD]
[TD]1 Friday[/TD]
[TD]5/9/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.


[TABLE="class: cms_table, width: 1147"]
<tbody>[TR]
[TD]Program[/TD]
[TD]Total amount of Episodes[/TD]
[TD]X Per week

[/TD]
[TD]Contract Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Event 1[/TD]
[TD]52[/TD]
[TD]1 [/TD]
[TD]5/5/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 2[/TD]
[TD]60[/TD]
[TD]2 [/TD]
[TD]5/6/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 3[/TD]
[TD]14[/TD]
[TD]1 [/TD]
[TD]5/7/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 4[/TD]
[TD]52[/TD]
[TD]3[/TD]
[TD]5/8/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 5[/TD]
[TD]26[/TD]
[TD]1 [/TD]
[TD]5/9/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Program
[/TD]
[TD]Total Amount of Episodes
[/TD]
[TD]X Per Week excluding Weekend Days
[/TD]
[TD]Contract Start Date
[/TD]
[TD]End Date
[/TD]
[/TR]
[TR]
[TD]Event 1
[/TD]
[TD]52
[/TD]
[TD]2 Monday, Wednesday
[/TD]
[TD]5/5/14
[/TD]
[TD]=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"))
[/TD]
[/TR]
[TR]
[TD]Event 2
[/TD]
[TD]60
[/TD]
[TD]2 Tuesday, Thursday
[/TD]
[TD]5/6/14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 3
[/TD]
[TD]14
[/TD]
[TD]1 Wednesday
[/TD]
[TD]5/7/14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 4
[/TD]
[TD]52
[/TD]
[TD]1 Thursday
[/TD]
[TD]5/8/14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event 5
[/TD]
[TD]26
[/TD]
[TD]1 Friday
[/TD]
[TD]5/9/14
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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