+ Reply to Thread
Results 1 to 11 of 11

How many days worked (Not a NETWORKDAYS question!)

  1. #1
    Registered User
    Join Date
    02-13-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    5

    How many days worked (Not a NETWORKDAYS question!)

    Hi,

    I'm tring to get a count of how many days a particular agent worked in a particular (but changing) date range.

    I have an excel document (Sheet 1) that contains circa 40 agents (Sheet 1, AP:AP) and the documents they worked each day (Sheet 1, C:C) since the start of the year. I have a information dash (Sheet 2) that runs from it and two cells (Sheet 2, K2 and K4) will be changed to show particular date ranges. At present I am trying to get how many days Mr. Smith (Sheet 2, K10) worked between (and including) 04/Feb/2014 and 10/Feb/2014 (Sheet 2, K2 and K4) based on the info below;

    C AP
    1 10/02/2014 Mr Smith
    2 10/02/2014 Mr Smith
    3 10/02/2014 Mr Smith
    4 10/02/2014 Mr Smith
    5 10/02/2014 Mr Smith
    6 10/02/2014 Mr Smith
    7 10/02/2014 Mr Smith
    8 10/02/2014 Mr Smith
    9 10/02/2014 Mr Smith
    10 10/02/2014 Mr Smith
    11 10/02/2014 Mr Smith
    12 10/02/2014 Mr Smith
    13 10/02/2014 Mr Smith
    14 08/02/2014 Mr Smith
    15 08/02/2014 Mr Smith
    16 08/02/2014 Mr Smith
    17 08/02/2014 Mr Smith
    18 08/02/2014 Mr Smith
    19 08/02/2014 Mr Smith
    20 08/02/2014 Mr Smith
    21 07/02/2014 Mr Smith
    22 04/02/2014 Mr Smith
    23 03/02/2014 Mr Smith
    24 31/01/2014 Mr Smith
    25 31/01/2014 Mr Smith
    26 31/01/2014 Mr Smith
    27 31/01/2014 Mr Smith
    28 31/01/2014 Mr Smith
    29 31/01/2014 Mr Smith
    30 30/01/2014 Mr Smith
    31 30/01/2014 Mr Smith
    32 30/01/2014 Mr Smith
    33 30/01/2014 Mr Smith
    34 30/01/2014 Mr Smith
    35 30/01/2014 Mr Smith
    36 30/01/2014 Mr Smith
    37 30/01/2014 Mr Smith
    38 29/01/2014 Mr Smith
    39 29/01/2014 Mr Smith
    40 29/01/2014 Mr Smith
    41 29/01/2014 Mr Smith
    42 29/01/2014 Mr Smith
    43 29/01/2014 Mr Smith
    44 29/01/2014 Mr Smith

    Hopefully this makes sense!

  2. #2
    Registered User
    Join Date
    02-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: How many days worked (Not a NETWORKDAYS question!)

    Please attach workbook with sample data and highlight cells that what you want to get.

  3. #3
    Registered User
    Join Date
    02-13-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How many days worked (Not a NETWORKDAYS question!)

    ATTACH]297132[/ATTACH]

    Hi chinraj

    I need the answer highlighted on sheet 2
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-22-2014
    Location
    genoa, italy
    MS-Off Ver
    Excel 2007
    Posts
    134

    Re: How many days worked (Not a NETWORKDAYS question!)

    agent.xlsx

    hi,
    here attached is the solution I'm proposing, even if I'm sure someone will suggest you something better :-)
    AL
    PS:the formula of the yellow cell is an array formula, so CTRL+SHIFT+ENTER to commit it (not just ENTER)

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How many days worked (Not a NETWORKDAYS question!)

    Despite your thread title the simplest way to solve this is probably to use NETWORKDAYS.INTL function, i.e. this "array formula" in C10 confirmed with CTRL+SHIFT+ENTER and copied down to C13

    =B$4-B$2+1-NETWORKDAYS.INTL(B$2,B$4,"0000000",IF(Sheet1!A$2:A$500=B10,Sheet1!B$2:B$500,0))

    Note: this doesn't exclude Saturday or Sunday as per the regular NETWORKDAYS function

    [untested because I don't have access to Excel 2010 at my current location]

    Excel 2010 or later is required for NETWORKDAYS.INTL, in earlier excel versions you can use this formula (also an array formula) for the same results

    =SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$500>=B$2,IF(Sheet1!A$2:A$500<=B$4,IF(Sheet1!B$2:B$500=B10,Sheet1!A$2:A$500))),Sheet1!A$2:A$500),1))
    Last edited by daddylonglegs; 02-13-2014 at 09:40 AM.
    Audere est facere

  6. #6
    Registered User
    Join Date
    02-13-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How many days worked (Not a NETWORKDAYS question!)

    @ AL1976

    Unfortunately I wont be able to use your solution as lots of people willbe accessing this and there is a risk they will not filter the dates etc.

    @daddylonglegs

    I've tried both your formulas and neither seem to work for me?

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How many days worked (Not a NETWORKDAYS question!)

    Did you use CTRL+SHIFT+ENTER?

    I can't give you a working example of the first suggestion because I can't currently access Excel 2010, as I said, but see attachment for demo of the FREQUENCY version
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-13-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How many days worked (Not a NETWORKDAYS question!)

    I did use CTRL+SHIFT+ENTER on the first cell and when i add the $ into the formula it returns #VALUE, any suggestions?

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How many days worked (Not a NETWORKDAYS question!)

    If you edit the formula you need to confirm with CTRL+SHIFT+ENTER again

    To do that you need to select the cell with the formula, press F2 key to select formula (or place cursor somewhere within formula in formula bar) then hold down CTRl and SHIFT keys while pressing ENTER.

    If done correctly you will see curly braces like { and } around the formula. Now you can copy the formula down the whole range

  10. #10
    Registered User
    Join Date
    02-13-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How many days worked (Not a NETWORKDAYS question!)

    Got it thank you very much daddylonglegs!!

  11. #11
    Registered User
    Join Date
    01-27-2014
    Location
    DFW, Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: How many days worked (Not a NETWORKDAYS question!)

    the simplest way is to add a formual in column D to count the items for that person for that day.
    =COUNTIFS($B$2:B2,B2,$A$2:A2,A2) in B2 and fill down. this will count instances of the combinations of the Agent and the Day.

    Then put this formula in Sheet 2, C10 and fill down.
    =COUNTIFS(Sheet1!$B:$B,$B10,Sheet1!$D:$D,1,Sheet1!$A:$A,">="&$B$2,Sheet1!$A:$A,"<="&$B$4)
    This will count the days between and including the given dates, but will only count the first instance of that combination ("1" in sheet 1D:D).

+ 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. Networkdays: Can this be worked out 'backwards'?
    By Laurence Craig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2012, 08:20 PM
  2. [SOLVED] Average hours worked based on days worked during a pay cycle
    By jaycee66 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-14-2012, 06:27 PM
  3. add days using networkdays
    By prasjohn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2007, 11:57 PM
  4. Replies: 9
    Last Post: 09-03-2007, 08:26 PM
  5. how to use networkdays returning fractions of days (4.3 days)
    By Chris in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-06-2005, 11:05 AM

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