Date Range Query

[Carl Carlos]

Good day all,

I have been given a list of invoices (thousands) of which usually cover a months cost but can cover anything from 15 to 93 days.

My task is to identify for each invoice how many of the invoiced days fall into each of our financial periods

For Example:

Invoices:
Invoice one: 15 days from 25-03-15 to 08-04-15
Invoice two: 56 days from 10-03-15 to 04-05-15

Financial Periods:
Period 1: 01-03-15 to 04-04-15
Period 2: 05-04-15 to 02-05-15
Period 3: 03-05-15 TO 30-05-15

Result Required:
I am looking for a formula that can say invoice one has 11 days in Period 1, 4 days in Period 2 and none in Period 3. While Invoice two will show 26 days in Period 1, 28 days in Period 2 and 2 days in Period 3.

These invoices span several months and are a little random in the nature of days they cover.

Guys, any help would be greatly appreciated.

 

[Tubal]

Hi Carl,

Let R and S represent the date range of the invoice (R <= S)
Let W and X represent the period range to count number of days (W <= X)

Here are the possible combination of invoice dates and period dates:

   W   X       Formula
---|---|---
RS |   |         #1:  0
R  | S |         #2:  S-W
R  |   | S       #3:  X-W
   | RS|         #4:  S-R
   | R | S       #5:  X-R
   |   | RS      #6:  0

If RS is totally before (or after) the period, then the answer is 0. That takes care of 2 of the 6 possibilities.
Formula 1 & 6

	=if(or(S < W, R > X),0,1)	{use 1 when false, it will add to the sum to count the number of days, not days between}

Formula for 2

	=if(and(R<=W,S=MEDIAN(S,W,X)),S-W,0)

Formula for 3

	=if(and(R<=W,S>=X),X-W,0)

Formula for 4

	=if(and(R=MEDIAN(R,W,X),S=MEDIAN(S,W,X)),S-R,0)

Formula for 5

	=if(and(R=MEDIAN(R,W,X)), S>=X, X-R,0)

Final Result:

=if(or(S < W, R > X),0,1) + if(and(R<=W,S=MEDIAN(S,W,X)),S-W,0) + if(and(R<=W,S>=X),X-W,0) + if(and(R=MEDIAN(R,W,X),S=MEDIAN(S,W,X)),S-R,0) + if(and(R=MEDIAN(R,W,X)), S>=X, X-R,0)

Excel Example:
A3 = R
B3 = S
C1 = W
C2 = X

=if(or(B3 < C1,A3 > C2),0,1) + if(and(A3<=C1,B3=MEDIAN(B3,C1,C2)),B3-C1,0) + if(and(A3<=C1,B3>=C2),C2-C1,0) + if(and(A3=MEDIAN(A3,C1,C2),B3=MEDIAN(B3,C1,C2)),B3-A3,0) + if(and(A3=MEDIAN(A3,C1,C2)), B3>=C2, C2-A3,0)

I hope this helps.
Tubal