# How many days worked (Not a NETWORKDAYS question!)

1. ## 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. ## 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. ## Re: How many days worked (Not a NETWORKDAYS question!)

ATTACH]297132[/ATTACH]

Hi chinraj

I need the answer highlighted on sheet 2

4. ## 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. ## 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))

6. ## 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.

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

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

8. ## 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. ## 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. ## Re: How many days worked (Not a NETWORKDAYS question!)

Got it thank you very much daddylonglegs!!

11. ## 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).

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