Forum Discussion
Formula for tracking service intervals on running equipment
_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.
SergeiBaklan 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-page-6/ 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, SergeiBaklan. 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
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")