Count the number of days in a particular month

[khaledhamdy]

Hi guys,

how r u all.

Start Date End Date
4/30/2018 5/03/2018
5/1/2018 5/30/2018
4/28/2018 5/15/2018

as you can see I've two columns start date and end date and the values of each one of them.

what I need is to let the excel count the number of days that fall within a certain month for example April.

I already using this formula to count the days between every single start date and end date

=MAX(0,MIN(EOMONTH($A35,0),C$3)-MAX($A35,B$3)+1)

but I need to include the whole column of the start date and the whole column of the end date in this formula instead of repeat it for every single start date and end date.

I tried this but it didn't work: =MAX(0,MIN(EOMONTH($A35,0),$C$3:$C$28)-MAX($A35,$B$3:$B$28)+1)

I hope that my question is clear.

thanks in advance guys.

 

[VBA Geek]

try:

Book1
	A	B	C	D	E	F	G
1	Start DatE	END DATE
2	4/30/2018	5/3/2018		days count		start	1-Apr-18
3	5/1/2018	5/30/2018		34		end	30-Apr-18
4	4/28/2018	5/15/2018
5	3/2/2018	5/15/2018
6	5/8/2018	6/8/2018
Sheet1

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(SUBTOTAL(5,OFFSET(B2,ROW(A2:A6)-ROW(A2),0),G3)-SUBTOTAL(4,OFFSET(A2,ROW(A2:A6)-ROW(A2),0),G2)+1,(A2:A6<=G3)*(B2:B6>=G2))

 

[khaledhamdy]

thank you , it's working.

now because of the offset reference is not a range I can't fill it down to work in May & June, etc.... ;(

I've tried to replace the single cells to ranges but it didn't work

your valuable help, please.

 

[VBA Geek]

you can just set up your tab like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Start DatE	END DATE						April 2018	May 2018	June 2018
2	4/30/2018	5/3/2018		days count			start	1-Apr-18	1-May-18	1-Jun-18
3	5/1/2018	5/30/2018		34	April		end	30-Apr-18	31-May-18	30-Jun-18
4	4/28/2018	5/15/2018		87	May
5	3/2/2018	5/15/2018		30	June
6	5/8/2018	7/8/2018
Sheet1

Cell Formulas
Range	Formula
H3	=EOMONTH(H2,0)
I3	=EOMONTH(I2,0)
J3	=EOMONTH(J2,0)
D3	=SUMPRODUCT(SUBTOTAL(5,OFFSET($B$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$3:J$3,ROWS(D$3:D3)))-SUBTOTAL(4,OFFSET($A$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$2:J$2,ROWS(D$3:D3)))+1,($A$2:$A$6<=INDEX(H$3:J$3,ROWS(D$3:D3)))*($B$2:$B$6>=INDEX(H$2:J$2,ROWS(D$3:D3))))
D4	=SUMPRODUCT(SUBTOTAL(5,OFFSET($B$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$3:J$3,ROWS(D$3:D4)))-SUBTOTAL(4,OFFSET($A$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$2:J$2,ROWS(D$3:D4)))+1,($A$2:$A$6<=INDEX(H$3:J$3,ROWS(D$3:D4)))*($B$2:$B$6>=INDEX(H$2:J$2,ROWS(D$3:D4))))
D5	=SUMPRODUCT(SUBTOTAL(5,OFFSET($B$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$3:J$3,ROWS(D$3:D5)))-SUBTOTAL(4,OFFSET($A$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$2:J$2,ROWS(D$3:D5)))+1,($A$2:$A$6<=INDEX(H$3:J$3,ROWS(D$3:D5)))*($B$2:$B$6>=INDEX(H$2:J$2,ROWS(D$3:D5))))

 

[khaledhamdy]

thank you bro for your valuable support, the above formula has worked fine but it was too complicated, I tried to work the first one which you've provided and I changed only G3 & G2 to different cells based on each month start and end, and it worked fine.

again thanks a lot for the great support.