+ Reply to Thread
Results 1 to 7 of 7

Using NETWORKDAYS to find holiday date clashes

  1. #1
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile Using NETWORKDAYS to find holiday date clashes

    Hi Guys,

    I have a spreadsheet that I use to work out my Annual leave at work. the function NETWORKDAYS works great and I have a seperate sheet that I have all the bank holidays on so that it calculates my days off correctly. This works fine.

    What I wanted to know in my office we have certain days that are compulsory. Is it possible to use the NETWORKDAYS function to check to see if your dates clash with the compulsory dates?

    For example. If I wanted to book off the 4th Feb to 8th Feb but the 6th & 7th of Feb are compulsary. Is it possible for my spreadsheet to me let know that 2 days in my range are compulsory?

    Thanks in advance.
    Best Regards.

    Michael
    -----------------------------------
    Windows Vista, Microsoft Office 2007

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If your leave dates are in A2 and B2 (A2=4th feb 2008, B2 = 8th Feb 2008)

    then this formula in C2 will give the number of days in that range which coincide with your compulsory days, assuming those days are listed in Z1:Z10

    =SUMPRODUCT((Z1:Z10>=A2)*(Z1:Z10<=B2))

  3. #3
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Lightbulb

    @daddylonglegs

    Thank you very much for this formula, it works perfect!!

    I don't understand how SUMPRODUCT works even after pressing F1 for help but it does the job.

    Could I use this formula if I had a master spreadsheet to see if my holiday dates clashed with a colleague?

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by Zyphon
    Could I use this formula if I had a master spreadsheet to see if my holiday dates clashed with a colleague?
    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)?

  5. #5
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Lightbulb

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

  6. #6
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517
    =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.

  7. #7
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Lightbulb

    Hi Mark,

    Thanks for your help and explanation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1