Count number of days within certain month within a date range

[Marmasek]

I am looking for assistance to create a formula that counts the number of days within a certain month within a date range. The numbers found in C2, D2, E2 and F2 are where I would like to input a formula to auto populate after entering the start and end dates.

For example:

Start Date	End Date	Jan	Feb	Mar	Apr
30 Jan 2020	5 Apr 2020	2	29	30	5

 

[CatrionaG]

Following on from the post above, I have a similar scenario that I would be grateful for some assistance with. Instead of counting total days each month, I need to calculate business days each month I.e. Monday to Friday and if possible excluding bank holidays.

 

[jtakw]

Hi,

Do you mean this?

Book3.xlsx
	A	B	C	D
1	Start	End		Total Workdays
2	1/3/2021	3/31/2021		63
Sheet884

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS(A2,B2)

Go here to learn how to set up your Holiday list:

NETWORKDAYS function - Microsoft Support

Syntax: NETWORKDAYS(start_date, end_date, [holidays])

support.microsoft.com

 

[Tetra201]

... I have a similar scenario... Instead of counting total days each month, I need to calculate business days each month I.e. Monday to Friday and if possible excluding bank holidays.

See if the following formula works for you:

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0)),Holidays))

where Holidays is a named range containing bank holiday dates.

 

[Doctor789]

I'll assume the 4 May 2020 entry should show 28 days remaining in May.

I need the year of the heading month so it doesn't count 2019 or 2021 dates so I've entered the actual 1st of the month for each column and just formatted it a mmm-yy.

View attachment 342

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start date	End date	1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020
2	1/30/2020	4/5/2020	2	29	31	5	0	0	0	0	0	0	0	0
3	5/4/2020	6/3/2020	0	0	0	0	28	3	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
D1:N1	D1	=EOMONTH(C1,0)+1
C2:N3	C2	=IF(EOMONTH($A2,0)=EOMONTH(C$1,0),EOMONTH($A2,0)-$A2+1,IF(EOMONTH($B2,0)=EOMONTH(C$1,0),$B2-EOMONTH($B2,-1),IF(AND(C$1>$A2,C$1<$B2),EOMONTH(C$1,0)-EOMONTH(C$1,-1),0)))

I noticed an error in this formula in instances when the start date and end date are on the same month, currently working on a fix, but otherwise very useful!

 

[Doctor789]

I noticed an error in this formula in instances when the start date and end date are on the same month, currently working on a fix, but otherwise very useful!

New Modified formula, seems to be working thus far, added a new IF function in the beginning

=IF(AND(EOMONTH($A2,0)=EOMONTH(C$1,0),EOMONTH($B2,0)=EOMONTH(C$1,0)),$B2-$A2+1,
IF(EOMONTH($A2,0)=EOMONTH(C$1,0),EOMONTH($A2,0)-$A2+1,IF(EOMONTH($B2,0)=EOMONTH(C$1,0),$B2-EOMONTH($B2,-1),IF(AND(C$1>$A2,C$1<$B2),EOMONTH(C$1,0)-EOMONTH(C$1,-1),0))))