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

 

[Robert Mika]

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

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?

 

[Peter_SSs]

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

The red parts seem contradictory. Can you clarify?

 

[rustybenson]

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?

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.

 

[Robert Mika]

D3 copy down
=IF(MOD(C3,19200)=0,"time for service","")


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

Date	DT	RT
01-08-12	1600	0
02-08-12	1600	1600
03-08-12	1600	3200
04-08-12	1600	4800
05-08-12	1600	6400
06-08-12	1600	8000
07-08-12	1600	9600
08-08-12	1600	11200
09-08-12	1600	12800
10-08-12	1600	14400
11-08-12	1600	16000
12-08-12	1600	17600
13-08-12	1600	19200	time for service
14-08-12	1600	20800
15-08-12	1600	22400
16-08-12	1600	24000
17-08-12	1600	25600
18-08-12	1600	27200
19-08-12	1600	28800
20-08-12	1600	30400
21-08-12	1600	32000
22-08-12	1600	33600
23-08-12	1600	35200
24-08-12	1600	36800
25-08-12	1600	38400	time for service

<TBODY>
</TBODY>

 

[Vidar]

D3 copy down
=IF(MOD(C3,19200)=0,"time for service","")

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

 

[Robert Mika]

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

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.

Assumig that what the OP want:

26-08-12	1600	40000
27-08-12	1600	41600
28-08-12	1600	43200
29-08-12	1600	44800
30-08-12	1600	46400
31-08-12	1600	48000
01-09-12	1600	49600
02-09-12	1600	51200
03-09-12	1600	52800
04-09-12	1600	54400
05-09-12	1600	56000
06-09-12	1600	57600	time for service
07-09-12	1600	59200
08-09-12	1600	60800
09-09-12	1600	62400
10-09-12	1600	64000
11-09-12	1600	65600
12-09-12	1600	67200
13-09-12	1600	68800
14-09-12	1600	70400
15-09-12	1600	72000
16-09-12	1600	73600
17-09-12	1600	75200
18-09-12	1600	76800	time for service
19-09-12	1600	78400
20-09-12	1600	80000
21-09-12	1600	81600
22-09-12	1600	83200
23-09-12	1600	84800
24-09-12	1600	86400
25-09-12	1600	88000
26-09-12	1600	89600
27-09-12	1600	91200
28-09-12	1600	92800
29-09-12	1600	94400
30-09-12	1600	96000	time for service

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

 

[Vidar]

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

 

[Peter_SSs]

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

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:

It is as close to 20000 but not over

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
	A	B	C	D	E
1	Date	Dist	Running Tot	Service?	20000
2			0	0
3	1/08/12	1600	1600
4	2/08/12	1600	3200
5	3/08/12	1600	4800
6	4/08/12	1600	6400
7	5/08/12	1600	8000
8	6/08/12	1600	9600
9	7/08/12	1600	11200
10	8/08/12	1600	12800
11	9/08/12	1600	14400
12	10/08/12	1600	16000
13	11/08/12	1600	17600
14	12/08/12	1600	19200	1
15	13/08/12	1600	20800
16	14/08/12	1600	22400
17	15/08/12	1600	24000
18	16/08/12	1600	25600
19	17/08/12	1600	27200
20	18/08/12	1600	28800
21	19/08/12	1600	30400
22	20/08/12	1600	32000
23	21/08/12	1600	33600
24	22/08/12	1600	35200
25	23/08/12	1600	36800
26	24/08/12	1600	38400	1
27	25/08/12	1600	40000
28	26/08/12	1600	41600
29	27/08/12	1600	43200
30	28/08/12	1600	44800
31	29/08/12	1600	46400
32	30/08/12	1600	48000
33	31/08/12	1600	49600
34	1/09/12	1600	51200
35	2/09/12	1600	52800
36	3/09/12	1600	54400
37	4/09/12	1600	56000
38	5/09/12	1600	57600	1
39	6/09/12	1600	59200
Service

 

[Peter_SSs]

The other reason I would like this formula driven is that I can adjust the daily Kilometers which will automatically adjust the service dates.

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