Glad you got it working.

One last caveat, expressing dates in your formula like "<5/01/2006" or
--("5/01/2006") can cause problems if your spreadsheet is used on a machine
that does not have the same short date format set in Windows Regional
Settings (in control panel). U.S. settings interpret the above as May 1,
2006, while many European settings would interpret it as Jan 5, 2006. Also,
the short date format could be customized from one user to the next - so even
if the user has U.S. settings, the short date format could still be something
other than what you expect. The date function doesn't have this issue, but
you can decide for yourself based on who the intended users will be.





"ed" wrote:

On Sep 21, 6:44 pm, JMB wrote:
For one thing, 1/1/04 is interpreted as 1 divided by 1 divided by 4. Need to
use DATE(2004, 1, 1) or put the dates in a cell and reference those cells in
your formula.

Sumif doesn't work in the manner you are attempting. To use sumif to get a
sum between two dates, for example:
SUMIF($B:$B,""&DATE(2003, 12, 31), D:D)-SUMIF(B:B, "="&DATE(2005, 9, 1),
D:D)*0.375

or you could use sumproduct instead of 2 Sumifs to get data between two dates.

However, I would set up a table with mileage rates and the effective dates.
Let's say this table is on Sheet2!A1:B5:

0 0.360
1/1/2004 0.375
9/1/2005 0.485
1/1/2006 0.445
2/1/2007 0.485

Then I think this will work for you:
=SUMPRODUCT(LOOKUP(B1:B5,Sheet2!A1:A5,Sheet2!B1:B5 ),C1:C5)

assuming your dates are in B1:B5 and miles are in C1:C5. I only did limited
testing, so make sure to test it out for yourself.

JMB: I ended up using your first suggestion for where the rate only chasnged once or twice, per the following:

=SUMIF($B:$B,"<1/01/2006",A:A)*90+SUMIF($B:$B,"12/31/2005",A:A)*92-
SUMIF($B:$B,"5/20/2007",A:A)*92+SUMIF($B:$B,"5/19/2007",A:A)*94

I "store" the formula in a column A cell and I just paste the formula
at the bottom of each column. Note that entering the dates "bare"
works fine. I had already built a table as per your final suggestion
(although it was siimpler that yours as I didn't use LOOKUP), for the
milage but will probalbly go back and create the formula above, but
with more dates.

Thanks for all the suggestions, however.

"ed" wrote:
Column B is a list of dates, usually in ascending date order, and
column D are number of miles driven on each date to be reimbursed at
different $ per mile rates. Other columns have hours on various dates
to be reimibursed at different pay scales, etc. I'm looking for a
universal formula format I can apply to each type of reimbursement.
For instance I want to convert the total miles driven into dollars,
however, the reimbursement rate per mile has changed several times in
the past. I constructed the following formula. =SUMIF($B:
$B,"<1/1/2004",D:D)*0.36+SUMIF($B:
$B,"AND(<9/1/2005,12/31/2003),D:D)*0.375+SUMIF($B:
$B,"AND(<1/1/2006,9/31/2005),D:D)*0.485+SUMIF($B:
$B,"AND(<2/1/2007,12/31/2005),D:D)*0.445+SUMIF($B:
$B,"2/1/2007",D:D)*0.485.

The trouble is that the formula doesn't "pick up" values for dates in
the AND time frames. A date in the first and last time periods
(without the AND)records O.K.. Any suggestions?

TIA ed- Hide quoted text -

- Show quoted text -