+ Reply to Thread
Results 1 to 6 of 6

Finding work day lead time using networkdays function across multiple sheets

  1. #1
    Registered User
    Join Date
    04-24-2017
    Location
    Cincinnati, OH
    MS-Off Ver
    2010
    Posts
    6

    Finding work day lead time using networkdays function across multiple sheets

    I need help figuring out a formula that can find the work day lead time between two dates. My data consists of a date when a job is entered into the system, and an end date when the job is completed. I want to find the time it took to complete the job, excluding weekends and holidays. The first sheet is all my data, the second sheet is my results page, and my third sheet is my setup sheet where I figured out the network days formula.

    I just cant figure out how to use the network days formula in conjunction with my data to find my actual lead time.

    I tried this formula =AVERAGE(IF((Data!K3:K99960>=A3)*(Data!J3:J99960<=B3),Data!P3:P99960)) where K is my start date, J is the end date, and P is the total days to install including weekends/holidays, but it isnt working.
    Looking at it now I dont even think I'm close to the right formula for what I am trying to get.
    Thanks for any help.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Finding work day lead time using networkdays function across multiple sheets

    Hello artistictiger300,

    Of course you can use NETWORKDAYS to find the number of workdays between two dates.....but I'm a little confused about what you are doing here, your formula is giving an average in a specific period. Perhaps a small example with dummy data can illustrate your expected results
    Audere est facere

  3. #3
    Registered User
    Join Date
    04-24-2017
    Location
    Cincinnati, OH
    MS-Off Ver
    2010
    Posts
    6

    Re: Finding work day lead time using networkdays function across multiple sheets

    Date Complete Date Created Total Days to Install
    2/27/2017 2/17/2017 10
    2/27/2017 2/22/2017 5
    2/27/2017 2/21/2017 6
    2/27/2017 2/14/2017 13
    2/27/2017 2/17/2017 10
    2/27/2017 2/20/2017 7


    So this is the data I am working with. Currently I am using this formula to get the Total days to install figure, =DATEDIF(K14,J14,"d"). This gives me the total days in between the dates, where I am trying to just get the work/business days.

    Then I am taking the total days to install data and getting the average between a range of dates, a normal work week, using this formula =AVERAGE(IF(ISNUMBER(P1:P2),P1:P2))

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Finding work day lead time using networkdays function across multiple sheets

    Well to start off you can use this formula to get the work/business days

    =NETWORKDAYS(K14,J14)

    Note: that that counts all the days including start and end dates, so for a Monday date until the following day it will give you 2.....so you might want to subtract 1 if that should count as 1

  5. #5
    Registered User
    Join Date
    04-24-2017
    Location
    Cincinnati, OH
    MS-Off Ver
    2010
    Posts
    6

    Re: Finding work day lead time using networkdays function across multiple sheets

    OK, so that worked, but there are some times where I dont have a completion date, so that cell is empty. Using the network days function that is giving me these huge negative numbers, which is now throwing off my lead time average.
    This is the formula I was using, but now it is not accurate.
    =AVERAGE(IF(ISNUMBER(P3:P10),P3:10))
    I tried doing this
    =AVERAGE(IF(ISNUMBER,">=0"(P3:P10),P3:P10))
    but that is giving me an error.

    Would this be the best formula to use then?
    =AVERAGEIF(P3:P10,">=0")

    Thanks for all the help so far.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Finding work day lead time using networkdays function across multiple sheets

    Yes, averaging above zero looks good....or you can eliminate the negative numbers by using this formula for the business days

    =IF(COUNT(J14,K14)=2,NETWORKDAYS(K14,J14),"")

    That will give you a blank result unless there are dates in both cells

    Then you can use a simple AVERAGE function which will ignore the blanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. One lead page used for multiple sheets
    By allipops in forum Excel General
    Replies: 1
    Last Post: 08-06-2015, 12:16 PM
  2. Search function across multiple work sheets
    By evgn12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2014, 11:46 AM
  3. [SOLVED] Using Networkdays with the Now() function to work out lapsed time since job was logged.
    By MarkyP18 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-07-2014, 10:04 AM
  4. Finding item # match on multiple sheets
    By Crispyglock in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-10-2012, 04:21 AM
  5. [SOLVED] Using NETWORKDAYS function to calculate cycle time
    By tciocchetti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2012, 06:17 PM
  6. NETWORKDAYS Function doesn't work
    By Dean in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2006, 10:55 AM
  7. [SOLVED] Multiple large .NewSeries lead to Run-time error 1004
    By Dave Booker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-22-2005, 11:05 PM

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