Calculate Overdue Service Status

[Andrew Watson]

Hi everyone,

I have a spreadsheet that is exported from another program for the servicing of our fleet of vehicles and equipment. My partner has currently being using conditional formatting to show which items are overdue by a coloured cell based upon a set range of values. This doesn't work well as the exported data cell shows kms and hrs overdue. I have an IF formula to determine what is serviced by hrs, kms or monthly. What I need to do though is have a formula that can sniff out that an asset that is 20 days overdue for a service by giving that cell an orange colour but a vehicle that is 20kms overdue for a service is still treated as a green colour until it reaches 500kms then it moves to an orange colour. I've made a table below to give you an example of what I'm trying achieve.

Please note that I'm from Australia and all dates are dd/mm/yyyy

Data below is copied from exported spreadsheet. Next service type refers to either 3 (3 monthly) or the hourly or km service type required eg. 250 or 500 etc (for hr service items) and 1000, 5000 etc (for km service items)

F	G	H	I	J	K	L	M	N	P	Q	R
Last Service Date	Last Service Type	Last Service Reading	Next Service Due	Next Service Type	Last Meter Reading	Last Reading Date	Hrs/Kms until due	Days until due	3 Mnth Service Date	Reading Type	Service Status
10/10/2015	3	3	10/01/2016	6	1	05/02/2016	N/A	-26	10/01/2016	MTH	Unacceptable
22/10/2015	5000	91938	90000	5000	96105	05/02/2016	-1105	N/A	22/01/2016	KM	Unacceptable
05/11/2015	1000	13086	13250	250	13433	27/00/2016	-183	N/A	05/02/2016	KM	Acceptable
12/6/2015	1000	1029	1250	500	2001	05/02/2016	-751	N/A	19/09/2015	HR	Unacceptable
10/02/2016	5000	95105	100000	10000	97896	12/02/2016	2104	N/A	10/05/2016	KM	Good

<tbody>
</tbody>

As you can see there is a fair amount of different data for this calculation to sort through and determine what is overdue and by how much. I am looking for a formula that can show results as per column R in the above data. All other data is pre-populated with the exception of column Q of which I have a calculation linked to on another tab.

Any help is very much appreciated as I've been going over this for days now. I had an INDEX and a MATCH calculation but with my limited calculation knowledge I could only get it to search for the HR items only and not the KM or MTH items.

Thanks

Andrew

 

[etaf]

you can use AND() or OR() in a conditional formatting

So AND( Days Cell >20 , Km cell > 500 , Q = "hrs" )
AND (Hrs/Kms until due > 200 , Q = "Mth" )