calculate end date using start date and number of specific weekdays

Welcome to the Board.

No-one minds here if you x-post on another forum but we would ask you provide a link to it so we can see if you have a solution already somewhere else either now or at a later date.

Thanks

http://www.excelforum.com/excel-wor...art-date-and-number-of-specific-weekdays.html

Hello freekystyley, welcome to MrExcel,

I assume E1 should be C1-D1

Then assuming you have already taken into account all classes attended up to and including today you could use this formula in F1 for end date

=SMALL(IF(ABS(WEEKDAY(B1+ROW(INDIRECT("1:"&E1*5)))-3)=1,ROW(INDIRECT("1:"&E1*5))),E1)+B1

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

First - thanks for the heads up on crossposting.. I apologize for the lack of including the link..
http://www.excelforum.com/excel-wor...art-date-and-number-of-specific-weekdays.html

Secondly, thank you SO much Barry Houdini - this is exactly what I needed. I very much appreciate your help

One more simple question, I hope - I'm attempting to understand this formula as well as I can - what data in the formula would need to change if, say, the class were moved from Monday/Wednesday to Tues/Thurs?

Thanks again

barry houdini said:

Hello freekystyley, welcome to MrExcel,

I assume E1 should be C1-D1

Then assuming you have already taken into account all classes attended up to and including today you could use this formula in F1 for end date

=SMALL(IF(ABS(WEEKDAY(B1+ROW(INDIRECT("1:"&E1*5)))-3)=1,ROW(INDIRECT("1:"&E1*5))),E1)+B1

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

Click to expand...

I needlessly made the formula a lttle more complex than it should be, and in the process also made it less transparent. You can use this version

=SMALL(IF(WEEKDAY(B1+ROW(INDIRECT("1:"&E1*5)))={2,4},ROW(INDIRECT("1:"&E1*5))),E1)+B1

where the {2,4} represent the days on which there are classes, i.e. Mondays and Wednesdays, so if you wanted to change to Tuesdays and Thursdays that would become {3,5}

Note: the *5 part represents the maximum number of days between the dates (Wednesday to Monday is 5) so if that changes, for instance if you have classes on Tuesday and wednesday change the two instances of *5 to *6.....or *7 should accommodate any combination of days, or single days

Thank you again, so much... I appreciate your help!

This is exactly what I was looking for