service reminder

[Blue Point]

Good evening from Australia. I'm new to the forum have been told it's a great forum with very helpful members. I require some assistance with a formula for work. Hoping the formula is possible. I have a number of fleet vehicles that I look after and want to get a reminder when services are due. I have 1 columns with date of last service and another with kilometres at last service. What I need is a formula that will tell me when 6 months have past since the late service date or 12,500km, whichever comes first. Is this possible? Many thanks in advance. Blue Point
[TABLE="width: 263"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

How do you know when 12,500 km have been achieved?
What columns contain what information?

 

[Blue Point]

How do you know when 12,500 km have been achieved?
What columns contain what information?

Hi footoo. I have 1 row for each vehicle. There is a about 15 columns with various information and details about the vehicle. Every month for each vehicle I populate Column E with the of the date of the last service and column F has the odometer reading at that service. In the past I have overwritten the info in columns E&F. However going forward I would like to add 2 more columns for the new date & odometer reading each time and then run a formula to calculate if ether 6 months or 12500 km have passed since the last service date. Apologies if this a bit vague

 

[jtakw]

Hi,

What we Need clarification on is:

You have Columns showing Date of Last service and Odometer reading of Last service, How do we know What the Odometer reading is Now (Present Day), so that we know 12500km has Passed??

Also, showing sample of your current layout would be helpful.

 

[Blue Point]

Hi again and thanks for your reply. I have 145 rows as follows. Hope this helps. I've adjusted the figures to try and give some different scenarios.

C D E F G H I J K
[TABLE="width: 1192"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1192"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
Last Odometer Km[/TD]
[TD]Last Odometer Date[/TD]
[TD]Last Service Date[/TD]
[TD]Last Service Odometer KM[/TD]
[TD]New Odometer Km[/TD]
[TD]New Odometer Date[/TD]
[TD]Last Service Date[/TD]
[TD]Last Service Odometer KM[/TD]
[TD]Formula required[/TD]
[/TR]
[TR]
[TD]158691[/TD]
[TD]10/02/2018[/TD]
[TD]12/07/2017[/TD]
[TD]145021[/TD]
[TD]160839[/TD]
[TD]14/05/2018[/TD]
[TD]12/07/2017[/TD]
[TD]147169[/TD]
[TD]if G2 is more than 12500 than F2 or if I2 is more than 6 months than E2 return 'SERVICE DUE' otherwise return 'SERVICE NOT DUE'[/TD]
[/TR]
[TR]
[TD]162238[/TD]
[TD]8/04/2018[/TD]
[TD]21/06/2017[/TD]
[TD]161114[/TD]
[TD]167416[/TD]
[TD]14/05/2018[/TD]
[TD]21/06/2018[/TD]
[TD]166292[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]141887[/TD]
[TD]27/05/2018[/TD]
[TD]1/02/2018[/TD]
[TD]123763[/TD]
[TD]145898[/TD]
[TD]28/06/2018[/TD]
[TD]1/02/2018[/TD]
[TD]127774[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]114908[/TD]
[TD]20/05/2018[/TD]
[TD]1/06/2017[/TD]
[TD]95067[/TD]
[TD]115890[/TD]
[TD]22/06/2018[/TD]
[TD]1/06/2017[/TD]
[TD]96049[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

The sample you provided above is a bit confusing, you have two sets of "Last Service Date" & "Last Service Odometer", the Dates are Exactly the same, but the Odometer is slightly different?
So for my sample below, I'm Ignoring Columns I and J, and using Columns E and F:

Book1
	C	D	E	F	G	H	I	J	K
1	Last Odometer Km	Last Odometer Date	Last Service Date	Last Service Odometer KM	New Odometer Km	New Odometer Date	Last Service Date	Last Service Odometer KM	Formula required
2	158691	10/02/2018	12/07/2017	145021	160839	14/05/2018	12/07/2017	147169	SERVICE DUE
3	162238	08/04/2018	21/06/2017	161114	167416	14/05/2018	21/06/2018	166292	SERVICE DUE
4	141887	27/05/2018	01/02/2018	123763	145898	28/06/2018	01/02/2018	127774	SERVICE DUE
5	114908	20/05/2018	01/06/2017	95067	115890	22/06/2018	01/06/2017	96049	SERVICE DUE
Sheet131

Cell Formulas
Range	Formula
K2	=IF(OR(EDATE(TODAY(),-6)>=E2,F2+12500<=G2),"SERVICE DUE","SERVICE NOT DUE")

K2 formula copied down, adjust Cell reference/range as needed.

 

[Blue Point]

thanks ktakw, I was able to make some minor adjustments and it works great. thanks again

 

[jtakw]

You're welcome, welcome to the forum.