Showing the date the next service is due on a truck that travels similar distances each day

[rustybenson]

Hi
I have a truck that covers approx, 1600 kilometers a day and I have it serviced as close to every 20000 kilometers as possible avoiding going over 20000kilometers traveled. I can go down th column and add 20000 to the last service kilometers and when I get close mark the date. However I have several trucks all doing different distances each day. is there a fornula that will show what date thelast service was and when the new service is due(as close to 20000 filometers as possible.
My spraed sheet is set up
A1 heading "date" and in A2 1/8/2012 dragging this down to end in 31/7/2013 (one year or cell A367)
The next colum is the distance
B1 heading "distance travelled" and in B2 1600 dragging this down to cell B367 (1600 in each cell)
The next column is total litres
C1 Is " Running total Kilometers travelled "
C2 is 1600
c3 is C2+b3 and drag this down to c367 adding 1600 litres each time
in D column i want to display "service" when the next 20000 ks would be reached which manually would be 20800 kilometers on the 13/8/2012 and displayed in cell D14
The next service date would be at 40000 kilometers on the 25/08/2012 and displayed in cell D26
and so on.
I have used the opening odometer as 0 for ease of purpose in this example.
The other reason I would like this formula driven is that I can adjust the daily Kilometers which will automatically adjust the service dates.
I hope this makes sense and I appreciate any help
Regards
Rusty

 

[Vidar]

Vidar, If I have understood your suggestion correctly, that gives the first service at 19200 and the second at 40000. The distance between these 2 services is > 20000, which seems to contradict this:

You're right, but decreasing the service intervalls to 19200 km increases cost.
This might be a "ugly" one but it gives "Service" as close up to every 20000 km as possible.
=IF(AND(MOD(C2+800,20000)<1000,RIGHT(C2,4)<>"0000"),"Service",IF(MOD(C3,20000)=0,"Service",""))

Vidar

 

[Robert Mika]

Robert, given the above requirement, try your formula with a daily distance of 2500 km, giving a running total of 2500, 5000, 7500, ... etc

You are right Peter.
I have missed that point and the formula will fail.

 

[rustybenson]

Robert, given the above requirement, try your formula with a daily distance of 2500 km, giving a running total of 2500, 5000, 7500, ... etc

Thank you very much for your help. It works a treat.
Thanks again
Kind Regards
Rusty