Get count of weekdays in range

[MrChiliCheese]

Hello,

I have a list with several days and also a start and end date.

Day
2019/01/09
2019/01/18
2019/02/05
2019/02/20
2019/02/27
2019/05/01
2019/07/14
2019/07/15
2019/08/09
2019/08/10
2019/09/14
2019/12/30

I have to retrieve the count per weekday from the list between start and end date and this without using VBA.

Begin	2020/02/06
End	2020/08/10
Day	Count
Monday	1
Tuesday	-
Wednesday	3
Thursday	-
Friday	1
Saturday	1
Sunday	1

Not really sure how to start solving this issue. Help is appreciated - thanks a lot.

 

[DRSteele]

Welcome to the forum.

I think this ought to help.

MrExcel posts18.xlsx
	B	C
2	Day
3	Wed, Jan 09, 2019
4	Fri, Jan 18, 2019
5	Tue, Feb 05, 2019
6	Wed, Feb 20, 2019
7	Wed, Feb 27, 2019
8	Wed, May 01, 2019
9	Sun, Jul 14, 2019
10	Mon, Jul 15, 2019
11	Fri, Aug 09, 2019
12	Sat, Aug 10, 2019
13	Sat, Sep 14, 2019
14	Mon, Dec 30, 2019
15
16
17	Begin	2/6/2019
18	End	8/10/2019
19
20	Day	Count
21	Monday	1
22	Tuesday	0
23	Wednesday	3
24	Thursday	0
25	Friday	1
26	Saturday	1
27	Sunday	1
Sheet3

Cell Formulas
Range	Formula
C21:C27	C21	=SUMPRODUCT(($B$3:$B$14>=$C$17)*($B$3:$B$14<=$C$18)*(WEEKDAY($B$3:$B$14,2)=ROWS($B$21:B21)))

 

[Eric W]

Welcome to the MrExcel forum!

Try this:

Book2
	A	B	C	D
1	Day		Begin	2019/02/06
2	2019/01/09		End	2020/08/10
3	2019/01/18
4	2019/02/05		Day	Count
5	2019/02/20		Monday	3
6	2019/02/27		Tuesday	0
7	2019/05/01		Wednesday	3
8	2019/07/14		Thursday	0
9	2019/07/15		Friday	0
10	2019/08/19		Saturday	2
11	2019/08/10		Sunday	1
12	2019/09/14
13	2019/12/30
14
Sheet9

Cell Formulas
Range	Formula
D5:D11	D5	=SUMPRODUCT(--($A$2:$A$13>=$D$1),--($A$2:$A$13<=$D$2),--(TEXT($A$2:$A$13,"dddd")=C5))