Calculating number of days between two dates dependent on month

Question

I'm trying to create a formula so that I can calculate the number of nights each customers hotel stay was for. Easy enough.

However In my spreadsheet I have 12 columns for months of the year (columns C onwards) with Column A having the arrival date and Column B the departure date.

If the arrival date was the 10th of June 2014 Departure date in Column B was the 13th of July 2014 I need formulas in Column C onwards to calculate the number of nights/days that are relevant for that month i.e. Junes (column H) would be 21 days and July would be 12 (as they depart on the 13th day this isn't counted). Other month columns would return no result.

I'm not sure I understand your question. To calculate the number of dates between 2 dates columns, just create a formula that rest A-B and it will calculate the number of dates, independent of the month. — ericpap

user3616725 user3616725 · Accepted Answer · 2014-10-03T14:45:18

for simplicitys' sake instead of "january" "february" etc in C1:N1 I have put just numbers (1-12)

I assume here the YEAR of BOTH the start and finish dates is the same (more coding if not, but you only mentioned 12 month columns...)

I also assume that we are working on start date in A2 and finish date in B2.

Formula for C2:

=IF(AND(MONTH($A2)=C1,MONTH($B2)=C1),$B2-$A2, IF(MONTH($A2)=C1,1+EOMONTH($A2,0)-$A2, IF(MONTH($B2)=C1,$B2-DATE(YEAR($B2),C1,1), IF(AND(MONTH($A2)<C1,MONTH($B2)>C1),DAYSINMONTH(DATE(YEAR($A2),C1,1)) ,0))))

copy / drag to D2:N2

explanation:

if the start and finish dates are in the same month, just calculate the difference, use that in the column where month number matches.
now we know start and finish months are not the same. if the start month = reference month, then number of days will be calculated by last_day_of_month - start date (+1 for the first night)
now we know the start and finish months are not the same, and reference month is not the start month. If the reference month is the final month, than the number of days will be final_date - first_day_of_month
if none of the above then the reference month is either wholly within our date range or outside of it. If start_month < reference month < final_month then days = all days in that month (of the correct year)

Calculating number of days between two dates dependent on month

1 Answers