Listing number of days in each month from two dates

[Jim8089]

I've just gotten a new job. My boss has given me some accrual sheets to work out. I am given a start date and an end date. I have to populate the spreadsheet to show how many days were in each month between the two dates, like so:
​

Col A
Start Date
14/4/18

Col B
End Date
28/8/18

Col C
March
0

Col D
April
17

Col E
May
31

Col F
June
30

Col G
July
28

I am having to manually input the start date, end date (the dates come from a string of text detailing the transaction) and then work out the number of days in each month. I have trawled youtube for several hours now and after playing around with Row and Indirect functions, I'm not too sure I'm barking up the right tree. Hopefully there is an easy method for the month columns to automatically self-populate the number of days in their month, between the two dates. There are over 500 transactions on some sheets and it's very time consuming and mind numbing. I would be greatly appreciative if some clever person could point me in the right direction? Thanks​

 

[Kamolga]

Hi,

In C1, I entered 01/03/2018, went in format custom and put

mmmm

. So it shows March but I can use the date in my calculation. For april (D1), I simply put

[LEFT][COLOR=#222222][FONT=Verdana]=edate(C1,1)[/FONT][/COLOR][/LEFT]

which adds a month and then dragged it to the right. then copy pasted the format of C1 to those cells (which means I have 1/4/2018 and not April in text eventhough we can read April).

In C2, you can put the formula

=IF(EDATE(C$1,1)<$A2,0,IF(MONTH($A2)=MONTH(C$1),DAYS($A2,C$1),IF(MONTH($B2)=MONTH(C$1),DAYS(EDATE(C$1,1),$B2),IF(AND(C$1>$A2,C$1<$B2),DAYS(EDATE(C$1,1),C$1),0))))

and drag it to the right.

It also work for lines bellows if you add dates thanks to the "$" in the formula.

Workbook is here https://1drv.ms/x/s!AvmGsNl7aaaAgtRc4nCIT64L7jdW-w and can be downloaded from 3 dots on top right of the screen

 

[Kamolga]

I just realised that you could have an issue if you extend it to more than a year. You should then adapt

if(month(A2)=month(C1)

to

if(and(month(A2)=month(C1),Year(A2)=Year(C1)

with same $ and same process with month(B2) and C1 later I formula

 

[Jim8089]

Thank you for the help, I appreciate it. The formula you provided is very nearly there. The only issue with it is the start month and end month. It adds the amount of days in the month before the first date e.g. 4/14/2018 gives the figure 13 (the number of days in April up to the 14th) instead of 18 (the number of days til the end of April). The last month counts up to the end instead of the days up to the end day e.g. for 8/28/2018 is gives 4 (the number til the end of August) instead of 28.

 

[Biz]

Hi Jim,

Try the formula below on c2 and onwards.

=IFERROR(DATEDIF(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0)),"d")+1,0)

Does it help?

Kind Regards

Biz

 

[Kamolga]

My bad
This one also consider the same year

=IF(EDATE(C$1,1)<$A2,0,IF(AND(MONTH($A2)=MONTH(C$1),YEAR($A2)=YEAR(C$1)),DAYS(EDATE(C$1,1),$A2),IF(AND(MONTH($B2)=MONTH(C$1),YEAR($B2)=YEAR(C$1)),DAYS($B2,C$1)+1,IF(AND(C$1>$A2,C$1<$B2),DAYS(EDATE(C$1,1),C$1),0))))

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	Start	End	total	Mar-18	Apr-18	May-18	Jun-18
2	14-Apr-18	28-Aug-18	137	0	17	31	30
3	or
2d

Cell Formulas
Range	Formula
C2	=B2-A2+1
D2	=MAX(0, MIN(EOMONTH(D$1, 0), $B2) + 1 - MAX(D$1, $A2))

Excel 2010
	A	B	C	D
4	Start	End	Mar-18	Apr-18
5	14-Apr-18	28-Aug-18	0	17
6
7	D1	1-Mar-18
2d

Cell Formulas
Range	Formula
C5	=MAX(0, MIN(EOMONTH(C$4, 0), $B5) + 1 - MAX(C$4, $A5))