List of Monday's excluding Holidays

[dawnwong]

I am trying to prepare a list of Monday's which excludes any holidays lists but I can't seem to get it to work. I tried the WORKDAY function but then it skips the Monday and returns the Tuesday.

Example Cell C4 is the previous Monday 10/02/17
And holidays are listed in cells J5:J30 which includes 10/09/17 which is the next Monday I need the formula to then pull
10/16/2017 which would be the next non-holiday Monday. There are times when 2 Monday's in a row would also be holidays.

Any help is appreciated.

 

[Jonmo1]

Which version of Excel do you have?

XL2013+, try

=WORKDAY.INTL(C4,1,"0111111",$J$5:$J$30)

 

[wideboydixon]

Try this:

=MIN(IF(ISERROR(MATCH($C4+ROW($A$1:$A$10)*7,$J$5:$J$30,0)),$C4+ROW($A$1:$A$10)*7))

It's an array formula so enter with [Ctrl]+[Shift]+[Enter]

WBD

 

[Rick Rothstein]

Which version of Excel do you have?

XL2013+, try

=WORKDAY.INTL(C4,1,"0111111",$J$5:$J$30)

I have the WORKDAY.INTL function available to me in my copy of XL2010 also.

 

[Jonmo1]

I have the WORKDAY.INTL function available to me in my copy of XL2010 also.

My Mistake, thanks. I thought it was 13 for some reason..

 

[dawnwong]

Which version of Excel do you have?

XL2013+, try

=WORKDAY.INTL(C4,1,"0111111",$J$5:$J$30)

Amazing that seems to work but now how do I change it for a differ day eg Tues?

 

[dawnwong]

Got it thanks

 

[Jonmo1]

You're welcome..

The string "0111111" represents the 7 day week beginning with Monday.
MTWTFSS

Set it to 0 = Working day
Set it to 1 = Weekend