Excel formula identifying number of date ranges wi

I have a complicated scenario that I'm trying to write in an Excel formula without going into macro's.

The scenario is that I am presented with a date range, refered to as Range A. ie 10/1/2011 - 5/4/2011

I have a lookup table with 3 columns; From Date, To Date, Rate. This table contains around 50 rows, and each row represents a unique 3 month period (yearly quaters) and a corresponding rate.

I need to be able to take Range A, find how many days are in each quater and multiply those days by the quaterly rate.

In the example 10/1/2011 - 5/4/2011 I should end up with 81*2011_Q1_rate + 5*2011_Q2_rate.

This would be simple with a couple of for loops and VLOOKUP, but I need to avoid macro's. Does anyone have any other suggestions?

Thanks,

Scott.

I am going to break this one down into parts because the final equation gets quite long.

We have to do two things, first pull apart your date range. My range is in cell F16 for testing. This is done using =MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1) and =MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1) where A1 through C12 is your From Date to Rate array. These two match statements will give us the rows that the first quarter and the last quarter that your range falls into. Using these row numbers we create references using address() such as =ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),2)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2). The previous formula will spit out $B$1:$B$9 in my test case.

The second portion is finding the totals. We will make use of sumproduct(). Here is the simple sumproduct() formula: =SUMPRODUCT((B1:B7-A1:A7+1),C1:C7). The range is just static now for testing. It will take the quarter end minus the quarter beginning in array format, and add 1 to each. This is due to the fact that 1/31/13 - 1/1/13 = 30 days when we want it to be 31 days. This array is then multiplied by the rates for each month.

Now when we piece it all together, we take our first range, use indirect(), and substitute our dynamic ranges into the simple sumproduct() formula and we finish with =SUMPRODUCT((INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),2)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2))-INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),1)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),1))+1),INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),3))).

Now that formula does not take into account partial months. We minus out the partial month (stealing from our previous equations) with =((DATEVALUE(LEFT(F16,FIND(" - ",F16)))-INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),1)))*INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3))) and =((INDIRECT(ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2))-DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))))*INDIRECT(ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),3))) and we are done.

The final equation: =SUMPRODUCT((INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),2)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2))-INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),1)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),1))+1),INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3)&":"&ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),3)))-((DATEVALUE(LEFT(F16,FIND(" - ",F16)))-INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),1)))*INDIRECT(ADDRESS(MATCH(DATEVALUE(LEFT(F16,FIND(" - ",F16))),A1:A12,1),3)))-((INDIRECT(ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),2))-DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))))*INDIRECT(ADDRESS(MATCH(DATEVALUE(RIGHT(F16,LEN(F16)-2-FIND(" - ",F16))),A1:A12,1),3)))

If you split the data range into two separate cells, it will make the equation much smaller and have a higher likelihood of working correctly since there is less room for error.