How many days in each month

[daverubio]

Context:

We have a check in date and a check out date. In order to reconcile every month, we need to see how many days for each booking get accounted to every month. So if a booking starts on jan 15 and end on Feb 4, we need to be able to report the 17 days booked in Jan and the 3 nights if Feb.

So, if I have:

a cell with one date and a cell with another, how can I show how many days in each month within that range?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1
2	Start	End	total	Jan-19	Feb-19	Mar-19
3	15-Jan-19	04-Feb-19	21	17	4	0
4
5
6	D2 etc	1-Jan-19
7
3b

Cell Formulas
Range	Formula
C3	=B3-A3+1
D3	=MAX(0, MIN(EOMONTH(D$2, 0), $B3) + 1 - MAX(D$2, $A3))
E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))
F3	=MAX(0, MIN(EOMONTH(F$2, 0), $B3) + 1 - MAX(F$2, $A3))

 

[daverubio]

Thanks so much!!

Excel 2010

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]End[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]total[/TD]
[TD="align: center"]Jan-19[/TD]
[TD="align: center"]Feb-19[/TD]
[TD="align: center"]Mar-19[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15-Jan-19[/TD]
[TD="align: right"]04-Feb-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E5E5E5]#E5E5E5[/URL] , align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCCCFF]#CCCCFF[/URL] , align: right"]D2 etc[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCCCFF]#CCCCFF[/URL] , align: right"]1-Jan-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCCCFF]#CCCCFF[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

3b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=B3-A3+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=MAX(0, MIN(EOMONTH(D$2, 0), $B3) + 1 - MAX(D$2, $A3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=MAX(0, MIN(EOMONTH(F$2, 0), $B3) + 1 - MAX(F$2, $A3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[daverubio]

Thanks so much again!

I ended up going with this formula:

=iferror(if(IF(OR(MONTH($H841)>MONTH(AK$1),MONTH($M841)<MONTH(AK$1)),0,MIN($M841,DATE(YEAR(AK$1),MONTH(AK$1)+1,0))-MAX($H841,AK$1)+1)<0,"0",IF(OR(MONTH($H841)>MONTH(AK$1),MONTH($M841)<MONTH(AK$1)),0,MIN($M841,DATE(YEAR(AK$1),MONTH(AK$1)+1,0))-MAX($H841,AK$1)+1)),"")

H841 is the arrival date
M841 is the departure date
AK$1 is the date for reference of the month (12/1/2019)


All went very well and it works like a dream. UNITL... there was a booking that started on December 28th and ended on January 2. This means that the logic pulling for month breaks. For that particular booking, it is not pulling. I'm assuming because it sees the month "1" is less than the month of the AK$1 entry.

Make sense?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1
2	Start	End	Total	Dec-18	Jan-19	Feb-19	Mar-19
3	28-Dec-18	02-Jan-19	6	4	2	0
4	28-Dec-18	02-Jan-19	6	4	2	0
5	15-Jan-19	04-Feb-19	21	0	17	4
6
7	D2 etc	1-Dec-18
3b

Cell Formulas
Range	Formula
C3	=B3-A3+1
C4	=B4-A4+1
C5	=B5-A5+1
D3	=MAX(0, MIN(EOMONTH(D$2, 0), $B3) + 1 - MAX(D$2, $A3))
D4	=MAX(0, MIN(E$2-1, $B4) + 1 - MAX(D$2, $A4))
D5	=MAX(0, MIN(E$2-1, $B5) + 1 - MAX(D$2, $A5))
E3	=MAX(0, MIN(EOMONTH(E$2, 0), $B3) + 1 - MAX(E$2, $A3))
E4	=MAX(0, MIN(F$2-1, $B4) + 1 - MAX(E$2, $A4))
E5	=MAX(0, MIN(F$2-1, $B5) + 1 - MAX(E$2, $A5))
F3	=MAX(0, MIN(EOMONTH(F$2, 0), $B3) + 1 - MAX(F$2, $A3))
F4	=MAX(0, MIN(G$2-1, $B4) + 1 - MAX(F$2, $A4))
F5	=MAX(0, MIN(G$2-1, $B5) + 1 - MAX(F$2, $A5))
B7	=D2

 

[daverubio]

That's amazing. Thanks so much!

 

[Dave Patton]

Thanks for the feedback.
The suggestion is just 2 similar versions of post #2 .