Counting number of Mon, Tue, Wed, etc. worked within time-frame

[JamesonMH]

Hi all,

I need to calculate how many Mondays, Tuesday's etc. an employee worked over a given time frame. It's not as simple as "tell me the # of Monday's between January and May.'

See my condensed example using Thursday (i.e. how many Thursday's did this employee work between January and May? The answer I'm looking for via formula is 3 since the other Thursdays are duplicates.

Also note, I won't have the helper column #2 below in my report (Day of week) - I just put that in for clarity.

Many thanks for your time!

Date	Day of week (info only)	Count
Jan 11, 2018	Thursday	1
Jan 11, 2018	Thursday
Feb 7, 2018	Wednesday
Feb 8, 2018	Thursday	1
Feb 8, 2018	Thursday
Feb 22, 2018	Thursday	1
Feb 22, 2018	Thursday

<tbody>
</tbody>

James

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I
1	Date	Day of week (info only)	Count		Start	End	DOW	Count
2	11-Jan-18	Thursday	1		1/1/2018	5/31/2018	Thursday	3
3	11-Jan-18	Thursday
4	7-Feb-18	Wednesday
5	8-Feb-18	Thursday	1
6	8-Feb-18	Thursday
7	22-Feb-18	Thursday	1
8	22-Feb-18	Thursday
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Ce ll Formula H2 {=SUM(SIGN(FREQUENCY(IF($A$2:$A$20>=E2,IF($A$2:$A$20<=F2,IF(TEXT($A$2:$A$20,"ddd")=LEFT(G2,3),$A$2:$A$20))),$A$2:$A$20)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[JamesonMH]

Try:

	A	B	C	D	E	F	G	H	I
1	Date	Day of week (info only)	Count		Start	End	DOW	Count
2	11-Jan-18	Thursday	1		1/1/2018	5/31/2018	Thursday	3
3	11-Jan-18	Thursday
4	7-Feb-18	Wednesday
5	8-Feb-18	Thursday	1
6	8-Feb-18	Thursday
7	22-Feb-18	Thursday	1
8	22-Feb-18	Thursday
9

<tbody>
</tbody>

Sheet4​

Array Formulas Ce ll Formula H2 {=SUM(SIGN(FREQUENCY(IF($A$2:$A$20>=E2,IF($A$2:$A$20<=F2,IF(TEXT($A$2:$A$20,"ddd")=LEFT(G2,3),$A$2:$A$20))),$A$2:$A$20)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks Eric, this works good! I wouldn't need to test the date range though with an IF as all of the data dumped in my spreadsheet will already be filtered with the relevant date range. How would the formula look if modified to remove those IF conditions? Thanks again.

James

 

[Eric W]

Try this:

=SUM(SIGN(FREQUENCY(IF(TEXT($A$2:$A$20,"ddd")=LEFT(G2,3),$A$2:$A$20),$A$2:$A$20)))

 

[JamesonMH]

Try this:

=SUM(SIGN(FREQUENCY(IF(TEXT($A$2:$A$20,"ddd")=LEFT(G2,3),$A$2:$A$20),$A$2:$A$20)))

Works perfectly Eric - thank you again! It's impressive how you not only solved my problem using a couple of functions that aren't overly common, but that you also came up with the solution so quickly.

 

[Eric W]

You're very welcome! I've done this a while, and I've learned a few tricks, but I'm constantly learning new things about Excel. I like puzzles, and this kind of questions are right up my alley!