New Contributor

Formula for tracking service intervals on running equipment

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.

BP

7 Replies

Re: Formula for tracking service intervals on running equipment

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.

Re: Formula for tracking service intervals on running equipment

@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

Re: Formula for tracking service intervals on running equipment

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

`=(\$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.

Re: Formula for tracking service intervals on running equipment

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.

Re: Formula for tracking service intervals on running equipment

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.

Next select entire column D, Home->Conditional Formatting->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.

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.

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 > 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")`

Re: Formula for tracking service intervals on running equipment

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

Re: Formula for tracking service intervals on running equipment

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