Need excel formula to calculate the number of days in a date range, if they end within that range

October 1, 2009 7:15 AM

I need a formula to help calculate the number of days as compared to a range. Importing a CSV for a query to show bookings within the date range of 9/1 to 9/30. Some bookings are booked through the entire month and some are partial. I need to figure out the number of days or percentage for the bookings that don't run all the way through the query range.

Query Start Date: 09/01/09
Query End Date: 09/30/09

Booking1 | 02/14/09 (booking start) | 10/14/09 (booking end)
Booking2 | 03/01/09 (booking start) | 09/15/09 (booking end)
Booking3 | 06/11/09 (booking start) | 12/31/09 (booking end)

So I know that Booking 1 and 3 go through the entire month of the query range (30 days), but I need a formula to figure out how many days are in Booking 2. I know that you can apply formula to each row to subtract dates, but I have hundreds of rows. I was thinking it would read something like: "If end date is less than query end date, then subtract booking end date from query start date (and add 1)." Maybe to take it further, "else sum is 30 days"
Any help is appreciated.

posted by malcommc to Work & Money (9 answers total)

Some hints (sorry can't write it today - end of month):
In Excel You can subtract 2 dates to get the number of days between them.
{A1} : = 9/30/09 ; {B1} := 9/1/09
{C1} := =A1-B1 results in 29 days

I'll state a bunch of if statements for the next part

1st Category:
IF my start booking is LESS than my first date, I need to know information about my end booking:
(IF my end booking is LESS than my first date, I am not relevant
IF my end booking is GREATER than my first date, but is LESS than my second date, I am a partial month
IF my end booking is GREATER than my first date, and GREATER than my second date, I am a total booking)

2nd Category:
IF my start booking is GREATER than my first date, I need to know information about my end booking:
(IF my start booking is LESS than my first date, either an error check, or my Start/End booking are reverssed and should be treated as such
IF my end booking is GREATER than my, but is LESS than my second date, I am a partial month
IF my end booking is Greater than my first date, and GREATER than my second date, I am also a partial month)

3rd Category:
IF my start booking is GREATER than my second date, I am not not relevant.

Now in each of those questions, there are two dates that I should be using to measure the overlapped portion, and those should be identifiable.
posted by Nanukthedog at 7:45 AM on October 1, 2009

OK, I'm assuming you've imported your data as shown, with Booking1 start date in B1 and Booking1 end date in B2

Now assume your query start date is (09/01/09) is in G2 and your query end date (09/30/09) is in H2.

Put the following formula in E1:

=IF(OR(B1>H$2,C1<G$2),0,MIN(H$2,C1)-MAX(G$2,B1)+1)

Then drag it down to cover as many lines as you need.

I can send you a spreadsheet with that on if you like. Also, you should test the forumla for yourself, not rely on me to have got it right.
posted by Mike1024 at 8:39 AM on October 1, 2009

By which of course I mean Booking1 end date in C1
posted by Mike1024 at 8:40 AM on October 1, 2009

Response by poster: Great stuff guys, thanks. I am in the right direction at least. Mike, I tried your formula and it is definitely a step in the right direction. So far, I've got the end date portion figured out with this:
B1 is Query Start Date
C1 is Query End Date

C9 is Booking Start Date
C10 is Booking End Date

=IF(C10
This helps me figure out "if Booking End Date is less than Query End Date, then figure out number of days between Query Start and Booking End. Otherwise write total number of days in Query period (30)". The example uses these dates:

QSD: 09/01/09 (B1)
QED: 09/30/09 (C1)
BSD: 08/21/09 (C9)
BED: 09/21/09 (C10)

Formula result if true: 9
Formula result if false: 30

Now I have to figure out the reverse. For example, what if something ran only one day in the month. BSD and BED are both 09/15/09. I'm sure the answer is in the formula you've written, I just have to keep working with it. I'll post any progress here. Thanks again for looking out.
posted by malcommc at 9:09 AM on October 1, 2009

Response by poster: =IF(C10 <> Formula wouldn't paste in until i put spaces around less than bracket)
posted by malcommc at 9:11 AM on October 1, 2009

You have to replace > with &gt; and < with &lt; to stop metafilter thinking you're trying to use a HTML tag.
posted by Mike1024 at 9:23 AM on October 1, 2009

Response by poster: =IF(C10<C$1,C10-B$1+1,C$1-B$1+1)
posted by malcommc at 9:28 AM on October 1, 2009

Best answer:

• If booking start date is after (greater than) query end date, there's no intersection.
• If booking end date is before (less than) query start date, there's no intersection.
• Otherwise there is an intersection. If there is an intersection:
  • The intersection ends at the booking end date, or the query end date - whichever is first (smallest).
  • The intersection starts at the booking start date, or the query start date, whichever is later (larger).
  • The booking duration is the end date minus the start date, plus one day (assuming a booking from 01/01/2009 to 01/01/2009 is a one day booking).

In other words, if B1>H$2 or C1<G$2 the intersection is 0 days, otherwise the intersection is MIN(H$2,C1)-MAX(G$2,B1)+1

Giving the formula:

=IF(OR(B1>H$2,C1<G$2),0,MIN(H$2,C1)-MAX(G$2,B1)+1)

posted by Mike1024 at 10:09 AM on October 1, 2009 [1 favorite]

Response by poster: Beautiful. Thanks. That did the trick.
I ended up modifying slightly, and the end result was:

=IF(OR(B$1>C10,C$1<B10),0,MIN(C10,C$1)-MAX(B10,B$1)+1)

Where:
Query Start Date: 09/01/09 (B1)
Query End Date: 09/30/09 (C1)
Booked Start Date: 08/21/09 (B10)
Booked End Date: 09/21/09 (C10)

Then copied to all cells below and came up with the exact number of days for partial months, and 30days for full.

Thanks again for your help!
posted by malcommc at 11:44 AM on October 1, 2009