Formula for tracking service intervals on running equipment

Question

Greetings, all.&nbsp; I've been tasked with creating a live service interval tracking formula for our onsite equipment.&nbsp; I have current hours, and service intervals are 250 hrs and 500 hrs respectively.&nbsp;&nbsp;&nbsp;=IF(AND((E30-C30)&lt;500),"OK","SERVICE")&nbsp;C30 = last serviced hrsE30 = current live hrs&nbsp;I'm wondering if there is a better formula I can use? I'm a caveman when it comes to Excel, so this is the best I've come up with :(.&nbsp;&nbsp;I'd like to have OK in green and SERVICE in red.&nbsp;&nbsp;&nbsp;&nbsp;Any suggestions would&nbsp; be greatly appreciated.&nbsp; Thank you for your time.BP&nbsp;

sergeibaklan · Answer

_BP_&nbsp;,
&nbsp;
You don't need AND here, just
=IF((E30-C30&lt;500),"OK","SERVICE")
and you may use conditional formatting to color your cells automatically depends on hours difference.
&nbsp;

sergeibaklan · Answer

_BP_&nbsp;, please check this post&nbsp;https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/comment-page-6/&nbsp;how to work with conditional formatting.
Your formula could be like
=($E1-$C1)&lt;500
for the one color, and similar one for another. Two colors - two rules.
&nbsp;
If you provide small sample file I could illustrate details on it.

sergeibaklan · Answer

_BP_&nbsp;,
&nbsp;
First, better to define parameters of your formulas in the cells and not to hardcode in formulas. Plus give them some names to make formulas more understandable and friendly. Even better to keep them in separate from report sheet. For now let take in the same sheet as it was like this

To give the name stay on the cell, type in left upper box desired name, Enter.
&nbsp;
Next select entire column D, Home-&gt;Conditional Formatting-&gt;New Rule and here select

Select proper rule, "equal to" from drop-down menu, next equal to what (don't forget =), select desired format and Ok to close all these windows.
Repeat the same for the ServiceLevel1.
&nbsp;
Next, better to test the formulas for conditional formatting rules in separate columns. If the formula returns TRUE it will trigger the formatting, otherwise it'll be ignored. Start from the second row, be sure it works correctly for your rows with data. Copy it into te first row, it returns error but that doesn't matter in our case. Error is also result.

&nbsp;
Now select entire column F, new rule using formula, copy formula from cell M1 (we have to select formula from first row from which our range starts) and paste it here. Apply format for the red and Ok.

Repeat the same for the alert (yellow rule), and put it on the second place. Next Green and move it on the third place.
Formulas are moved up and down by these arrows

Sequence is important for thirst two rules, otherwise we have to complicated formulas (first we check if hours &gt; limit, after that if &gt; limit-gap. With such sequence it works, otherwise we shall check both boundaries for yellow rule).
In addition, for the green rule we check if Current Hours are not empty not to color blan cells.
=(($E1-$C1)&lt;=IF($D1=ServiceLevel2,HoursSL2,HoursSL1)-HoursGap)*($E1&gt;0)
Forgot to say, I modified yout formula in column F as well
=IF((E2-C2&lt;IF($D2=ServiceLevel1,HoursSL1,HoursSL2)),"OK","SERVICE")

sergeibaklan · Answer

_BP_&nbsp;, you are welcome. Exploring Excel is never ended process, good luck here.

_bp_ · Answer

SergeiBaklan&nbsp;thank you for your prompt aide.&nbsp; Can you tell me what formula I would use to colour the column when Service is the result of the first formula? I am unclear where to even place the formula, nevermind how to scribe it.&nbsp;BP

Forum Discussion

Formula for tracking service intervals on running equipment

7 Replies

Resources