Counting Specific Days in Specific Month between Two Dates

[samlions]

Hi,

I'm trying to determine a formula that could be used to calculate the the number of specific days (i.e Mondays, Tuesdays) in each month (i.e January, February) using a date range. The date range will always be within the same year. The table that I have been trying to code (without macros) is shown below.

Would appreciate any assistance.

Many thanks,
Sam

Start Date	End Date
20/01/2014	20/04/2014
	Monday	Tuesday	Wednesday	Thursday	Friday
January	=? i.e Count the number of Mondays in January within the date range above
February
March
April
May
June
July
August
September
October
November
December

<tbody>
</tbody>

 

[mfexcel]

would you consider to construct a helper table and do your summary by using Pivot Table?

 

[mfexcel]

Assume
A3 = Start Date
B3 = End Data

In the first cell that inter-crosses January and Monday,
=IF(AND(ROWS($A$1:$A1)<=MONTH($B$3),ROWS($A$1:$A1)>=MONTH($A$3)),SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(MAX(VALUE($A$3),DATE(YEAR($A$3),ROWS($A$1:$A1),1))&":"&MIN(VALUE($B$3),EOMONTH(DATE(YEAR($A$3),ROWS($A$1:$A1),1),0)))),2)=COLUMNS($A$1:A$1))),"")

Copy the formula down and across to fill up the table.

This works as long as start and end dates fall into same year.

 

[Aladin Akyurek]

Hi,

I'm trying to determine a formula that could be used to calculate the the number of specific days (i.e Mondays, Tuesdays) in each month (i.e January, February) using a date range. The date range will always be within the same year. The table that I have been trying to code (without macros) is shown below.

Would appreciate any assistance.

Many thanks,
Sam

Start Date	End Date
20/01/2014	20/04/2014
	Monday	Tuesday	Wednesday	Thursday	Friday
January	=? i.e Count the number of Mondays in January within the date range above
February
March
April
May
June
July
August
September
October
November
December

<tbody>
</tbody>

A2: 20-Jan-14
B2: 20-Apr-14

B5, copied across and down:

=SUMPRODUCT((TEXT(ROW(INDIRECT($A$2&":"&$B$2)),"ddddmmmm")=B$4&$A5)+0)

 

[mfexcel]

You are genius! Impressive!ray:ray:

A2: 20-Jan-14
B2: 20-Apr-14

B5, copied across and down:

=SUMPRODUCT((TEXT(ROW(INDIRECT($A$2&":"&$B$2)),"ddddmmmm")=B$4&$A5)+0)

 

[Aladin Akyurek]

You are genius! Impressive!ray:ray:

Thank you.

 

[samlions]

A big thanks to mfexcel and Aladin Akyurek for your replies. Both answers were very helpful and much appreciated. Cheers!

 

[mfexcel]

@Aladin, I really like your solution. I write a post to explain how it works. Pls feel free to comment.

@Samlions, hope you have a better understanding of how the formula works.

Calculate number of a specific day between two dates | wmfexcel

A big thanks to mfexcel and Aladin Akyurek for your replies. Both answers were very helpful and much appreciated. Cheers!