+ Reply to Thread
Results 1 to 4 of 4

Capturing work days on average from an array of start/end times, other criteria

  1. #1
    Registered User
    Join Date
    12-20-2011
    Location
    U.S.A.
    MS-Off Ver
    Excel 2010
    Posts
    17

    Question Capturing work days on average from an array of start/end times, other criteria

    I have an array with multiple columns, one of which are start/end times for tasks being completed. I currently use a formula for each line to capture the amount of work hours (factoring in length of work day, holidays, weekends). I then average those results. What I want to do is convert this to a formula in a single cell that will do the same thing, but allow me to do it through a single cell rather than all those lines. I have attached an example spreadsheet to explain this better. This is the current formula.

    (NETWORKDAYS(A2,B2,$G$2:$G$11)-1)*(18/24-8/24)+IF(NETWORKDAYS(B2,B2,$G$2:$G$11),MEDIAN(MOD(B2,1),8/24,18/24),18/24)-MEDIAN(NETWORKDAYS(A2,A2,$G$2:$G$11)*MOD(A2,1),8/24,18/24)

    Where A2 is the start time/date and B2 is the end time/date. I also need it to factor in other criteria, in the example there are two other columns and I have selected P3 and Marketing. So I would want the average work days, for only a certain priority and a certain team.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Capturing work days on average from an array of start/end times, other criteria

    Can you explain what the numbers in L represent. You seem to be calculating a number that modifies the working days into working hours

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Capturing work days on average from an array of start/end times, other criteria

    The N column (N2:N16) can be calculated by the array formula
    Please Login or Register  to view this content.
    which set the name Start as
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and the End name as
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    $ N $ 1 = "Marketing" and $O$1="P3" are the filters.
    I am not being able to calculate the average of the values of this array (N2:N16).
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,398

    Re: Capturing work days on average from an array of start/end times, other criteria

    With a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. [SOLVED] Adding work hours based on particular start or finish times
    By RobertM01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2015, 10:28 AM
  2. [SOLVED] Trying to extract multiple start and end times during the course of a few days.
    By kspeese in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-20-2014, 02:05 PM
  3. Replies: 6
    Last Post: 08-23-2013, 07:56 AM
  4. Replies: 3
    Last Post: 11-12-2012, 04:03 PM
  5. [SOLVED] Work hours calculating start and end times
    By hiddenupnorth in forum Excel General
    Replies: 6
    Last Post: 04-20-2012, 01:54 PM

Tags for this Thread

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