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

 

[Toadstool]

March has 31 days, doesn't it?

Book1
	A	B	C	D	E	F
1	Start Date	End Date	Jan	Feb	Mar	Apr
2	1/30/2020	4/5/2020	2	29	31	5
Sheet1

Cell Formulas
Range	Formula
C2	C2	=EOMONTH((A2),0)-A2+1
D2	D2	=DAYS(EOMONTH(A2,1),EOMONTH(A2,0))
E2	E2	=DAYS(EOMONTH(A2,2),EOMONTH(A2,1))
F2	F2	=B2-(EOMONTH(B2,-1))

 

[Marmasek]

Yes I made a mistake inputting it manually (which is why I want the formula to do it for me!)

 

[Marmasek]

Looking at your response, I'm not sure if I made myself clear. I'll try explaining further:

If input the start dates and end dates manually, I would like a table with all the months in the columns to autofill how many days in each month have been covered.

For example if I entered the following data into the table:

Start date

End date

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

30 Jan 2020

5 Apr 2020

4 May 2020

3 Jun 2020

I would like the result in from C2:N3 to auto populate to:

Start date

End date

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

30 Jan 2020

5 Apr 2020

2

29

31

5

4 May 2020

3 Jun 2020

26

3

 

[Marmasek]

or better yet:

Start date	End date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
30 Jan 2020	5 Apr 2020	2	29	31	5	0	0	0	0	0	0	0	0
4 May 2020	3 Jun 2020	0	0	0	0	26	3	0	0	0	0	0	0

 

[Toadstool]

If 30 Jan 2020 is 2 days on January then why isn't 4 May 2020 28 days of May?

 

[Toadstool]

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.

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)))

 

[Marmasek]

Thank you Toadstool. I wish I had skills to create formulas rather than just copy paste and try and decipher how you derived them!

 

[Toadstool]

You're welcome!

I'm sure you could figure it out. It's just practice and the list of available functions:
Excel functions (alphabetical)

Excel holds dates as an integer since 1 Jan 1900. If I take today's date and add 1 I'll get tomorrow's date.

EOMONTH (end of month) has two parameters, a start date and a number of months to offset. It gives the end of the specified month (e.g. =EOMONTH(TODAY(),-1)+1 takes the current date, gives the last day of the previous month (31-Oct-2019) and the +1 is plus 1 day so the result is 1-Nov-2019)

IF has three parameters, IF(condition, result if true, result if false).

Your cells for counting days have 4 options:

1. This month is the same as Start Date so count days from Start Date to end of month.
2. This month is the same as End Date so count days from start of previous month to End Date.
3. This month is between Start Date and End Date so count number of days in this month.
4. This month is not a part of or between either Start Date or End Date so return zero.

So to deconstruct =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)))

(1). IF(EOMONTH($A2,0)=EOMONTH(C$1,0),EOMONTH($A2,0)-$A2+1 says if the last day of the month of the Start Date is the same as the heading month then get the date of the end month of the Start Date, subtract the Start Date and add 1.

(2). IF(EOMONTH($B2,0)=EOMONTH(C$1,0),$B2-EOMONTH($B2,-1) says if the last day of the month of the End Date is the same as the heading month then subtract the last day of the previous month of the End Date from the End Date.

(3). IF(AND(C$1>$A2,C$1<$B2),EOMONTH(C$1,0)-EOMONTH(C$1,-1) says if the heading date is greater than the Start Date AND the heading date is less than the End Date then return the end date of the current month minus the end date of the previous month of the heading (i.e. number of days in that month).

(4). ,0) says if none of the previous nested IF statements is true then return zero.

 

[Tetra201]

Here is a shorter formula for you to try:

=MAX(0,MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1)+1)