In Excel (or Numbers) what formula can calculate the amount of days in a certain month across 2 dates

If a guest arrived on 24th of August and left on 4th of September what is the formula(s) to calculate how many nights are spent in August, and how many nights are spent in September?

tom

Posted 2015-08-17T11:57:13.970

Reputation: 43

Do you want to include Sept 4 or exclude it in the count of nights?? – Gary's Student – 2015-08-17T12:09:56.770

Can the stay ever extend into three calendar months? – fixer1234 – 2015-08-19T07:19:18.030

Answers

Below is a table to split by month number of nights regardless of how many months are included:

Note that even if only month names appear in first row those are actually dates: first days of the months formatted to show only month name ("mmmm")

Formula in the table is:
=NOT(OR(D$1>$B2,EOMONTH(D$1,0)<$A2))*(MIN(EOMONTH(D$1,0)+1,$B2)-MAX(D$1,$A2))

NOT(OR(D$1>$B2,EOMONTH(D$1,0)<$A2)) is to decide whether current stay and month overlap
MIN(EOMONTH(D$1,0)+1,$B2)-MAX(D$1,$A2) is the length of stay

Calculations are done as I found it in hotels: night is counted to previous day (row 4 is one night in august) and one day stay is not counted as a night (see row 5)

Máté Juhász

Posted 2015-08-17T11:57:13.970

Reputation: 16 807

2Suggestion; Move complete formula after in the table to a single line, it makes it easier to read. – Hannu – 2015-08-17T14:40:50.790

This is working well for me, Thank you so much! – tom – 2015-08-19T06:59:03.430

Supposing the arrival date is in A1 and the check out date is in B1 then on C1 you must put:

=IF(EOMONTH(A1,0)<B1,CONCATENATE((EOMONTH(A1,0)-A1)," and ",B1-A1-(EOMONTH(A1,0)-A1)-1),B1-A1)

explanation:

IF ( 
    EOMONTH(A1,0)<B1 //If last day of month of arrival is before the checkout date then
        CONCATENATE( //concatenate days of first month "and" days of next month
                     (EOMONTH(A1,0)-A1),
                     " and ",
                     B1-A1-(EOMONTH(A1,0)-A1)-1
                    )
        B1-A1 //Else, simply substract checkout date from arrival date.

jcbermu

Posted 2015-08-17T11:57:13.970

Reputation: 15 868