How to calculate number of days between two dates by month

Hello,

I'm trying to create a formula I need urgently.

We are trying to extract details on customer hotel bookings to show how many their booking dates fall over calendar months. e.g. customer A has arrived on 24th June and departed on the 11th of July.

The spreadsheet is set up so that arrival date is in column B, departure date in column C and then calendar months Jan-Dec in column D onwards.

the formula would need to work so that it returns "0" in for example column D (January through to May) because the customer didn't arrive until June. The formula would then pick up that in June the customer was in the hotel from the 24th to the 30th so returning a result of "7". It would also then show a result in the Column for July of "10" as the departure date wouldn't be counted as a booked date.

I can understand how to calculate the number of days between two dates but not how to correctly use an IF formula or similar to get the results needed as above.

Matt

put this formula into d2 and fill down and across.
D2 to o2 must have the numbers 1 to 12. Otherwise the formula gets interesting.

Please Login or Register  to view this content.

How can I change the formula to include years?

Thanks,
A.