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

R

rustybenson

Board Regular
Joined
Dec 14, 2002
Messages
104
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
 
Sort by date Sort by votes
rustybenson said:
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
Click to expand...

So the subsequent services will happen on the milage closer to N*20000 or the first one after?
So the fifth will be at 99761 or 101395?
 
Upvote 0
rustybenson said:
... I have it serviced as close to every 20000 kilometers as possible avoiding going over 20000kilometers traveled..
.
.
... the next 20000 ks would be reached which manually would be 20800 kilometers on the 13/8/2012 and displayed in cell D14
Click to expand...
The red parts seem contradictory. Can you clarify?
 
Upvote 0
Robert Mika said:
So the subsequent services will happen on the milage closer to N*20000 or the first one after?
So the fifth will be at 99761 or 101395?
Click to expand...

Hi
I did get carried away. The way I calculated in my example i actually went from 1600. if we start on zero
it would be
19200
38400
57600
76800
96000
115200
It is as close to 20000 but not over after a days work. We can service half way through a run it must be back at the depot.
 
Upvote 0
D3 copy down
=IF(MOD(C3,19200)=0,"time for service","")


Sorry for my numbers I did not correctly drag down the column B

[TABLE="width: 192"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Date
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]DT
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]RT
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]01-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]02-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]03-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3200
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]04-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4800
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]05-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6400
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]06-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8000
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]07-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9600
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]08-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11200
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]09-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12800
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]10-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14400
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]11-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16000
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]12-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]17600
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]13-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl65, bgcolor: #8db4e2, align: right"]19200
[/TD]
[TD="class: xl63, bgcolor: transparent"]time for service
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]14-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20800
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]15-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22400
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]16-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24000
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]17-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]25600
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]18-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27200
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]19-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28800
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]20-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30400
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]21-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]32000
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]22-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]33600
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]23-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35200
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]24-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl66, bgcolor: white, align: right"]36800
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]25-08-12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1600
[/TD]
[TD="class: xl65, bgcolor: #8db4e2, align: right"]38400
[/TD]
[TD="class: xl63, bgcolor: transparent"]time for service
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Robert Mika said:
D3 copy down
=IF(MOD(C3,19200)=0,"time for service","")
Click to expand...

It will not work for services coming after 40000.

This one gives "Service" if the distance is a rounded number (20000, 40000, 60000, etc.)
or the one before the rounded one.
=IF(MOD(C2+800;20000)<1000;"Service";"")

Vidar
 
Upvote 0
Vidar said:
It will not work for services coming after 40000.

This one gives "Service" if the distance is a rounded number (20000, 40000, 60000, etc.)
or the one before the rounded one.
=IF(MOD(C2+800;20000)<1000;"Service";"")

Vidar
Click to expand...

Hi
I did get carried away. The way I calculated in my example i actually went from 1600. if we start on zero
it would be
19200
38400
57600
76800
96000
115200
It is as close to 20000 but not over after a days work. We can service half way through a run it must be back at the depot.
Click to expand...
Assumig that what the OP want:


[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl7447, width: 64, bgcolor: transparent, align: right"]26-08-12[/TD]
[TD="class: xl7450, width: 64, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, width: 64, bgcolor: transparent, align: right"]40000[/TD]
[TD="class: xl7450, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]27-08-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]41600[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]28-08-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]43200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]29-08-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]44800[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]30-08-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]46400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]31-08-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]48000[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]01-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]49600[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]02-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]51200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]03-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]52800[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]04-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]54400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]05-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]56000[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]06-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7448, bgcolor: #8db4e2, align: right"]57600[/TD]
[TD="class: xl7450, bgcolor: transparent"]time for service[/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]07-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]59200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]08-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]60800[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]09-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]62400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]10-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]64000[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]11-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]65600[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]12-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]67200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]13-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]68800[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]14-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]70400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]15-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]72000[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]16-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]73600[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]17-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]75200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]18-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7448, bgcolor: #8db4e2, align: right"]76800[/TD]
[TD="class: xl7450, bgcolor: transparent"]time for service[/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]19-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]78400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]20-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]80000[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]21-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]81600[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]22-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]83200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]23-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]84800[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]24-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]86400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]25-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]88000[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]26-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]89600[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]27-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]91200[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]28-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]92800[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]29-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]94400[/TD]
[TD="class: xl7450, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl7447, bgcolor: transparent, align: right"]30-09-12[/TD]
[TD="class: xl7450, bgcolor: transparent, align: right"]1600[/TD]
[TD="class: xl7449, bgcolor: #95b3d7, align: right"]96000[/TD]
[TD="class: xl7450, bgcolor: transparent"]time for service[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
I'm sorry Robert! I need to read more carefully next time.
I thought he wanted the service as close up to every 20000 kilometers as possible.

Your formula does the job exactly as the op wants.

Vidar
 
Upvote 0
Vidar said:
It will not work for services coming after 40000.

This one gives "Service" if the distance is a rounded number (20000, 40000, 60000, etc.)
or the one before the rounded one.
=IF(MOD(C2+800;20000)<1000;"Service";"")

Vidar
Click to expand...
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:
rustybenson said:
It is as close to 20000 but not over
Click to expand...

My suggestion is as below. Note the extra row 2, which could be hidden.
The formula just puts a 1 where a service is due.
Maximum service interval is in cell E1.
Formula in D3 copied down.

Excel Workbook
ABCDE
1DateDistRunning TotService?20000
200
31/08/1216001600 
42/08/1216003200
53/08/1216004800
64/08/1216006400
75/08/1216008000
86/08/1216009600
97/08/12160011200
108/08/12160012800
119/08/12160014400
1210/08/12160016000
1311/08/12160017600
1412/08/121600192001
1513/08/12160020800
1614/08/12160022400
1715/08/12160024000
1816/08/12160025600
1917/08/12160027200
2018/08/12160028800
2119/08/12160030400
2220/08/12160032000
2321/08/12160033600
2422/08/12160035200
2523/08/12160036800
2624/08/121600384001
2725/08/12160040000
2826/08/12160041600
2927/08/12160043200
3028/08/12160044800
3129/08/12160046400
3230/08/12160048000
3331/08/12160049600
341/09/12160051200
352/09/12160052800
363/09/12160054400
374/09/12160056000
385/09/121600576001
396/09/12160059200
Service
 
Upvote 0
rustybenson said:
The other reason I would like this formula driven is that I can adjust the daily Kilometers which will automatically adjust the service dates.
Click to expand...
Robert, given the above requirement, try your formula with a daily distance of 2500 km, giving a running total of 2500, 5000, 7500, ... etc
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Is there a process to accomplish excel automaticly highlighing trucks with due service every day with a pop up
Replies
8
Views
439
Anthony47
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top