How many days in each month

daverubio

New Member
Joined
Mar 21, 2019
Messages
4
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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Excel 2010
ABCDEF
1
2StartEndtotalJan-19Feb-19Mar-19
315-Jan-1904-Feb-19211740
4
5
6D2 etc1-Jan-19
7
3b
Cell Formulas
RangeFormula
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))
 
Upvote 0
Thanks so much!!




Excel 2010
ABCDEF
1
2StartEndtotalJan-19Feb-19Mar-19
315-Jan-1904-Feb-19211740
4
5
6D2 etc1-Jan-19
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
3b

Worksheet Formulas
CellFormula
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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?
 
Upvote 0

Excel 2010
ABCDEFG
1
2StartEndTotalDec-18Jan-19Feb-19Mar-19
328-Dec-1802-Jan-196420
428-Dec-1802-Jan-196420
515-Jan-1904-Feb-19210174
6
7D2 etc1-Dec-18
3b
Cell Formulas
RangeFormula
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
 
Upvote 0
Thanks for the feedback.
The suggestion is just 2 similar versions of post #2 .
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top