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


    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.

    Did you try it? It should work fine as long as you do not include any cells with errors in the cells being summed.

  • 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.

  • Re: Calculate Mileage For Discrete Date Ranges

    At this point you really need to start a new thread(s) for these questions. They are excellent questions, but deal with topics far removed from your thread title. Hence, no one will be able to find answers on the "new topics" because search focuses on the thread titles -- hence Dave's stress on good thread titles.

    You also should try searching Oz with the relavant keywords. These topics have been addressed in various forms over the years.

    Meanwhile .. hints: type 8/14 into a cell and see what happens. (Do not preceed it with anything -- no space, no =, no +)

    Also, look into sheet and cell protection. It can control tab movement.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!