Excel - count days in specific month between two dates

Question

I have a home rental business, I wish to calculate the occupancy rate. For that, I need to calculate the number of nights in a specific month between 2 dates (check-in and check-out).

Here's an example:

          A          B          C         D         E        F 

1  |     IN     |   OUT    |  NIGHTS | 01/01/18 | 01/02/18 |...
   ------------------------------------------------------------
2  |  15/01/18  | 25/01/18 |   10    |  10      |  0       |...
   ------------------------------------------------------------
3  |  28/01/18  | 04/02/18 |   7     |  3       |  3       |...

Here are the formulas for:

C3 = B3-A3

D3 = MAX(0; MIN(EOMONTH(D$1;0); $B3) - MAX(D$1; $A3))

I think I'm not too far but the formula is still incorrect. As you can see, D2 is correct, but D3 is wrong. This is the tricky part, when the stays overlaps on 2 months, Jan and Feb in thisi case. D3 should show 4 nights and not 3.

Anyone can help please? Thanks in advance!

PS: sorry for the formatting but I am not able to add a screenshot..

Answer 1

I was thinking that you just add a day to the end of the month unconditionally - I need to try this with one or two test cases though.

MAX(0; MIN(EOMONTH(D$1;0)+1; $B3) - MAX(D$1; $A3))

Just to spell out the four possible scenarios - given start date d1/m1/y1 (A3), end date d2/m2/y2 (B3) ,first day of current month 01/mm/yy (D1) and last day of current month dd/mm/yy (last day of month in D1)

(1) d1/m1/y1>=01/mm/yy and d2/m2/y2 <= dd/mm/yy -> d2/m2/y2 - d1/m1/y1
(Both days in current month - all nights between the two dates)

(2) d1/m1/y1 < 01/mm/yy and d2/m2/y2 <= dd/mm/yy -> d2/m2/y2 - 01/mm/yy
(Start before current month, end in current month - all nights from 1st of month up to end date)

(3) d1/m1/y1 >= 01/mm/yy and d2/m2/y2 > dd/mm/yy -> 01/(mm+1)/yy - d1/m1/y1
(Start in current month, end after current month - all nights from start date up to 1st of following month)

(4) d1/m1/y1 < 01/mm/yy and d2/m2/y2 > dd/mm/yy -> 01/(mm+1)/yy - 01/mm/yy
(Start before current month, end after current month - all nights in month).

In other words, it counts each night following a day in the range, but not the night preceding a day in the range.

Answer 2

You could conditionally add a day when the stay period stretches across month end. This would make that period inclusive as opposed to a simple subtraction.

=MAX(0, MIN(EOMONTH(D$1, 0), $B4)-MAX(D$1, $A4))+(EOMONTH(D$1, 0)<$B4)