Formula for tracking service intervals on running equipment

Copper Contributor

Greetings, all.  I've been tasked with creating a live service interval tracking formula for our onsite equipment.  I have current hours, and service intervals are 250 hrs and 500 hrs respectively.  

 

=IF(AND((E30-C30)<500),"OK","SERVICE")

 

C30 = last serviced hrs

E30 = current live hrs

 

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

I'd like to have OK in green and SERVICE in red.  

 

 

Any suggestions would  be greatly appreciated.  Thank you for your time.

EXCEL HELP.JPG

BP

 

7 Replies

@_BP_ ,

 

You don't need AND here, just

=IF((E30-C30<500),"OK","SERVICE")

and you may use conditional formatting to color your cells automatically depends on hours difference.

 

@Sergei Baklan thank you for your prompt aide.  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.

 

BP

@_BP_ , please check this post https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/comment... how to work with conditional formatting.

Your formula could be like

=($E1-$C1)<500

for the one color, and similar one for another. Two colors - two rules.

 

If you provide small sample file I could illustrate details on it.

Sounds good, @Sergei Baklan. Truly learning as I go.  Thanks for the link.  I will peruse it thoroughly.  I'm looking to create a parameter that includes a 50 hr warning color prior to service being due as well.  lol  Big dreams.   Green = OK, yellow = 50 hrs out from next service, red = SERVICE.  I update the hrs weekly. 

@_BP_ ,

 

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

image.png

To give the name stay on the cell, type in left upper box desired name, Enter.

 

Next select entire column D, Home->Conditional Formatting->New Rule and here select

image.png

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.

 

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.

image.png

 

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.

image.png

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

image.png

Sequence is important for thirst two rules, otherwise we have to complicated formulas (first we check if hours > limit, after that if > 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)<=IF($D1=ServiceLevel2,HoursSL2,HoursSL1)-HoursGap)*($E1>0)

Forgot to say, I modified yout formula in column F as well

=IF((E2-C2<IF($D2=ServiceLevel1,HoursSL1,HoursSL2)),"OK","SERVICE")

Indubitably, @Sergei Baklan, the best direction given to date for my exploits within Excel.  Thank you for the link and the walk through of creating the needed formulas.  I still have lots of reading and practicing to do, so I'll carry on.  Have a good weekend.

 

Thx again.

 

BP

@_BP_ , you are welcome. Exploring Excel is never ended process, good luck here.