Calculate Mileage For Discrete Date Ranges

I have a list of mileage rates that correspond with different date ranges. For example the mileage rate for March 19, 2008 – July 31, 2008 was 50.5 cents per mile. I have 11 different mileage rates / date ranges from 2000 onwards.

I would like to devise a way that I could input the date and the number of miles travelled into the spreadsheet, then Excel would check the date entered in order to choose the appropriate mileage rate and calculate the dollar amount based on the # of miles input. Thanks!

EDIT: Here are the date ranges and rates in the event that anyone is interested in putting something like this together. I would be grateful for an example for one or two of the rates though and hopefully could extrapolate from there.

1/1/00 - 3/31/00 - 32.5 cents per mile
4/1/01 - 12/31/01 - 34.5
1/1/02 - 12/31/02 - 36.5
1/1/03 - 12/31/03 - 36
1/1/04 - 12/31/04 - 37.5
1/1/05 - 8/31/05 - 40.5
9/1/05 - 12/31/05 - 48.5
1/1/06 - 1/31/07 - 44.5
2/1/07 - 3/18/08 - 48.5
3/19/08 - 7/31/08 - 50.5
8/1/08 - current - 58.5

Re: Calculate Mileage For Discrete Date Ranges

Thank you very much! That worked perfectly. I have one more request -- how would I go about totalling up all of the mileage amounts (cell e6 and any other cells with amounts)based on what was entered by the user? I've expanded the worksheet and thus now cells cells E6:R6, E12:R12, and E18:R18 contain the POV. I thought about using the SUM function, but I don't think you can use that when the fields you are adding up have IF statements in them. Is there a way to accomplish this?

Re: Calculate Mileage For Discrete Date Ranges

You were right. Works like a charm! I had a cell that I'd made with an error in it when I was working in it because I didn't protect my worksheet first. You are a life saver, thanks again!

I'm going to bug you for two more things if it's possible. Then I'm going to buy a book for the next idea I have and try to learn this stuff myself, because there is obviously so much you can do!

Is it possible to do the following:

1. The ability to enter a date as just numbers (i.e. 081308 for 8-13-2008 or even 8132008 or 08132008 would work too) without the dashes and have the program correctly recognize that? It would make entering the dates much more data-entry friendly.

2. Modify the behavior of the cursor by way of the enter key in the worksheet -- so for example move from E2 (date) down to E3 (mileage), and then jump back up F2 (date) down to F3 (mileage) and so on. Optimally, it would automatically "jump down" after completing P3(the last mileage cell) to the next set of date fields starting in cell E8, then follow the same pattern and jump down again to E14 and so on and so forth for as large as my worksheet is. I'm going to lock all the other cells outside of these so that no entry is possible in any other cell.