Vehicle mileage tracking Spreadsheet formula help

Hello,
I'm am trying to put a spreadsheet to help me track the mileage of our fleet of trucks to track their mileage and help me designate when they need servicing.

For example
Truck 125 Mileage is 31,654 and I take down the mileage every monday/ once a week and to continue throughout the year. I want the weekly to have conditional formatting so that they turn Green, Yellow or Red based on if they are less than or equal to 10K miles over the previous week to be green and if greater than 10k but less than or equal to 15k over the previous week to be yellow and then if greater than 15k but less than or equal to 20k over the previous week to be red.

Thank you in advance!


Fleet Mileage and Servicing Tracking Log.xls

Conditional formatting should accomplish this for you. Select the cells you'd like to highlight and then go to Conditional Formatting > Highlight Cell Rules and add the rules that you've listed.

Thank you and I only know simple formulas. I don't know how to write it so let's say, the vehicle has to be serviced at 20k intervals and as the vehicle is reported each week and as they go towards that 20k mark that cell turns from Green (when it was serviced) yellow then red as it hits towards or at 20k from the previous service interval. So each cell has a range of 10k, 15k and 20k over the previous based on when it was last serviced.

Let's say there are cells A1 B1 C1 and D1
A1 has the starting miles of 20,500k miles vehicle was just serviced
B1 has an entry of 30,500 (would be yellow because it's only 10k miles over A1)
C1 has an entry of 39,500 (would be red because it's almost 20k @19k miles over A1 the last service) (So this vehicle needs to be serviced)

D1 has an entry of 44,500 (would be green because it's only 5k over C1)
E1 has an entry of 54,500 (would be yellow because it's 15k over C1)
F1 has an entry of 58,500 (would be red because it's 19k over C1

I'm assuming that I'd have to put a cell in the spreadsheet showing the mileage that the vehicle was actually serviced at to start it over?
Any ideas??

I see. Here's your updated file. If you need to add a new truck, copy and paste the entire previous row (B11:O11) to preserve the formatting. If you need to add another month, copy and paste the previous month. Should help you get what you're looking for.

Ok how does sheet 2 effect it?

Sorry, you can ignore that. I started a separate worksheet before I realized what you were asking for. Feel free to delete it.

First off thank you! It looks like I wasn't too far off however how can I get say g10 to show green if somewhere between there and the number in f10 the vehicle was serviced? Also it appears that everything in row 11 is dependent on c10?

It'd be easiest just to start a new row with the truck number at the mileage when it got serviced. Yes, everything is based off of C10. You can edit this in the Conditional Formatting > Manage Rules settings.

Ok, I messed around with it a bit and I still cannot get it to do exactly what I would like. Can you or someone take a look at the attached spreadsheet and review the notes I have written on the spreadsheet?
Thank you!!

All I'm seeing in that file is a checklist of 15 items. Where are your notes?

HAHAHA OOOPS! I was in a hurry and I uploaded the wrong file. I'm so sorry. Here you go.
Protech Express Truck and Trailer Mileage Service Log1.xls

Let me know if this is what you're looking for. I added a column for mileage of last inspection and updated the conditional formatting references to be based on that column. Now if you enter an updated inspection mileage, the cells will return to green.

That is perfect! Thank you very much!!!

VEHICLE AND EQUIPMENTS FUEL TRACKING FORMULA.

Hello, I have just newly joined a construction and contracting company who are having plus 500 vehicles and equipments of different makes. I would like to create an excel format to track the fuel consumption and to make a master details of all the vehicles and equipments. Please send me a sample format where in i can update all the informations cost centre wise and track the fuel. Thanks Josefh