Using NETWORKDAYS to find holiday date clashes

**Zyphon** · 02-01-2008, 09:54 AM

Originally Posted by daddylonglegs

You may need a different approach. The above allows you to check a single range against a list. Presumably to do what you suggest you might want to check multiple ranges (your holiday dates) against another set of multiple ranges (a colleagues holiday dates)?

Yes I was thinking along those lines. Maybe have say a master spreadsheet that would have all my colleagues holiday dates and to check against those ranges. Although at this moment in time I have no idea of how to achieve this as it sounds quite complex.

**Mark@Work** · 02-01-2008, 02:00 PM

=SUMPRODUCT((Z1:Z10>=A2)*(Z1:Z10<=B2)) works because TRUE is 1 and FALSE is 0.

Thus (Z1:Z10>=A2) gives an array of ten values which can be viewed either as TRUE or FALSE or as 1 or 0.
similarly for (Z1:Z10>=B2)

Now 1*1=1, 1*0=0, 0*1=0, 0*0=0.
So you only get a 1 when (Z1:Z10>=A2) AND (Z1:Z10>=B2) everywhere else you get 0.

Add them up and there's your answer.

PS
=SUMPRODUCT((Z1:Z10>=A2),(Z1:Z10<=B2))
should also work and can be readily extended :
=SUMPRODUCT((Z1:Z10>=A2),(Z1:Z10<=B2),(Z1:Z10<=C2)...

Mark.

**Zyphon** · 02-01-2008, 04:31 PM

Hi Mark,

Thanks for your help and explanation.

Using NETWORKDAYS to find holiday date clashes

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions