Identifying how many days in month between 2 dates

[sovereign]

Morning,

I have been using some formula i have found on here.

=MAX(0,1+MIN(EOMONTH(F$1,0),$C2)-MAX(F$1,$B2))

But when i put it into my table it adds the numbers from the previous month:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Ref[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]01/01/2017[/TD]
[TD]01/02/2017[/TD]
[/TR]
[TR]
[TD]548782[/TD]
[TD]04/01/2017[/TD]
[TD]05/01/2017[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]563762[/TD]
[TD]24/01/2017[/TD]
[TD]06/02/2017[/TD]
[TD]8[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]


in F :

=MAX(0,1+MIN(EOMONTH(F$1,0),$C2)-MAX(F$1,$B2))

in G:

=MAX(0,1+MIN(EOMONTH(G$1,0),$C3)-MAX(G$1,$B3))

Any help please

 

[VBA Geek]

you can use yesterday solution and just allow for months as well as per below

https://www.mrexcel.com/forum/excel-questions/1043065-formulas-help-work-out-number-days-year-our-holiday-homes-rented-out-problem-over-two-years-one-hire-s.html



<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=MAX(<font color="Blue">0,MIN(<font color="Red">1+$B2,1+EOMONTH(<font color="Green">C$1,0</font>)</font>)-MAX(<font color="Red">$A2,C$1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	start	end	1-Jan-12	1-Feb-12	1-Mar-12	1-Apr-12	1-May-12	1-Jun-12	1-Jul-12	1-Aug-12	1-Sep-12	1-Oct-12	1-Nov-12	1-Dec-12
2	1-Feb-12	15-Oct-12	0	29	31	30	31	30	31	31	30	15	0	0
3	24-Jan-12	18-Feb-12	8	18	0	0	0	0	0	0	0	0	0	0
Sheet7

 

[sovereign]

you can use yesterday solution and just allow for months as well as per below

When i put your formula into the table i still get the same result as before. for Jan it works perfect but for Feb it also seems to also count Jan Days.

 

[VBA Geek]

my formula is made for my table, for your table you need to adjust the references. also make sure that the dates are correct, and month and days are not inverted

 

[sovereign]

Got it!

was to do with date formats!