# Date Range Query

#### Carl Carlos

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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:
Code:
``````   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
Code:
``	=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
Code:
``	=if(and(R<=W,S=MEDIAN(S,W,X)),S-W,0)``

Formula for 3
Code:
``	=if(and(R<=W,S>=X),X-W,0)``

Formula for 4
Code:
``	=if(and(R=MEDIAN(R,W,X),S=MEDIAN(S,W,X)),S-R,0)``

Formula for 5
Code:
``	=if(and(R=MEDIAN(R,W,X)), S>=X, X-R,0)``

Final Result:
Code:
``=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

Code:
``=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

Last edited:

Replies
4
Views
263
Replies
0
Views
176
Replies
3
Views
27
Replies
3
Views
159
Replies
1
Views
192

1,207,401
Messages
6,078,262
Members
446,324
Latest member
JKamlet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back