# How many days in each month

#### daverubio

##### New Member
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Dave Patton

##### Well-known Member

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

#### daverubio

##### New Member
Thanks so much!!

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

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

</tbody>

<tbody>
</tbody>

#### daverubio

##### New Member
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

##### Well-known Member

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

#### daverubio

##### New Member
That's amazing. Thanks so much!

#### Dave Patton

##### Well-known Member
Thanks for the feedback.
The suggestion is just 2 similar versions of post #2 .

Replies
1
Views
103
Replies
5
Views
184
Replies
4
Views
202
Replies
2
Views
138
Replies
11
Views
193

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,892
Messages
5,834,268
Members
430,273
Latest member
Windrunner

### 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.

### Which adblocker are you using?

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

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