Equipment Maintenance Spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2865652%22%20slang%3D%22en-US%22%3EEquipment%20Maintenance%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2865652%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help%20provided.%20Keep%20in%20mind%2C%20I'm%20plenty%20tech%20savvy%20but%20I%20am%20not%20super%20well%20versed%20in%20Excel.%20I%20am%20trying%20to%20figure%20out%20how%20I%20can%20build%20a%20service%20spreadsheet%20for%20my%20Company's%20heavy%20equipment.%20I'm%20trying%20to%20figure%20out%20how%20to%20build%20it%20where%20I%20simply%20key%20in%20the%20machine's%20hours%20and%20It%20tells%20me%20when%20it%20will%20be%20due%20for%20its%20next%20service.%20I%20of%20course%20will%20have%20other%20fields%20that%20won't%20change%2C%20such%20as%20machine%20serial%2C%20make%2C%20model%2C%20and%20location.%20Our%20equipment%20maintenance%20is%20based%20on%20hours%20of%20operation%20and%20normally%20machines%20get%20serviced%20every%20500%20hours.%20I%20would%20like%20for%20it%20to%20be%20able%20to%20flag%20a%20machine%20when%20it%20is%20within%2020%20hours%20of%20a%20service%20being%20due.%20For%20instance%20if%20I%20key%20in%20that%20a%20machine%20has%20482%20hours%2C%20it%20flags%20that%20machine%20and%20indicates%20that%20it%20will%20be%20due%20for%20service%20soon%2C%20maybe%20by%20turning%20a%20cell%20red%20or%20something%20of%20that%20nature.%20I%20also%20would%20like%20it%20to%20show%20when%20the%20next%20service%20is%20due%2C%20(Say%20I%20key%20in%20a%20machine%20that%20is%20at%20899%20hours%20its%20next%20service%20is%20due%20at%201%2C000%20hours.%20If%20I%20could%20use%20the%20500%20hour%20service%20interval%20and%20get%20it%20to%20predict%20its%20next%20service%20due%20that%20would%20be%20top%20notch)%20Any%20help%20is%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3ENick%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2865652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2865861%22%20slang%3D%22en-US%22%3ERe%3A%20Equipment%20Maintenance%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2865861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1190994%22%20target%3D%22_blank%22%3E%40Nick_McClellan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20example%20i%20have%20formula%3C%2FP%3E%3CP%3E%3D500-MOD(C2%2C500)%3C%2FP%3E%3CP%3Eto%20calculate%20the%20remaining%20hours%20until%20the%20next%20service%20and%20the%20rule%3C%2FP%3E%3CP%3E%3DIF(500-MOD(C2%2C500)%26lt%3B20%2C1)%3C%2FP%3E%3CP%3Efor%20conditional%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2866174%22%20slang%3D%22en-US%22%3ERe%3A%20Equipment%20Maintenance%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2866174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20response!%20That's%20a%20great%20idea%20to%20use%20hours%20until%20next%20service.%20I%20was%20able%20to%20get%20that%20formula%20to%20work%20on%20mine%20for%20the%20countdown.%20However%20what%20would%20be%20even%20better%20is%20if%20it%20read%20hours%20since%20last%20service%20and%20counted%20up.%20I%20attached%20my%20spreadsheet%20that%20I'm%20working%20on%20for%20reference.%20I%20got%20the%20%22hours%20since%20last%20service%22%20column%20to%20count%20up%20from%20the%20%22last%20service%22%20column%2C%20by%20entering%20the%20hours%20in%20the%20%22hour%20reading%22%20column%20and%20simply%20calculating%20the%20difference.%20However%20I%20would%20love%20for%20it%20to%20turn%20red%20when%20it%20goes%20over%20500%20hours%20since%20last%20service.%20I%20had%20some%20trouble%20getting%20your%20second%20formula%20to%20work.%20I%20attached%20the%20error%20I%20am%20given.%20Again%20sorry%20for%20the%20Excel%20ignorance%2C%20I%20just%20haven't%20used%20it%20much.%20Thanks%20again%20for%20your%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good morning,

 

Thanks in advance for any help provided. Keep in mind, I'm plenty tech savvy but I am not super well versed in Excel. I am trying to figure out how I can build a service spreadsheet for my Company's heavy equipment. I'm trying to figure out how to build it where I simply key in the machine's hours and It tells me when it will be due for its next service. I of course will have other fields that won't change, such as machine serial, make, model, and location. Our equipment maintenance is based on hours of operation and normally machines get serviced every 500 hours. I would like for it to be able to flag a machine when it is within 20 hours of a service being due. For instance if I key in that a machine has 482 hours, it flags that machine and indicates that it will be due for service soon, maybe by turning a cell red or something of that nature. I also would like it to show when the next service is due, (Say I key in a machine that is at 899 hours its next service is due at 1,000 hours. If I could use the 500 hour service interval and get it to predict its next service due that would be top notch) Any help is greatly appreciated. 

 

Thanks!

Nick 

3 Replies

@Nick_McClellan 

In my example i have formula

=500-MOD(C2,500)

to calculate the remaining hours until the next service and the rule

=IF(500-MOD(C2,500)<20,1)

for conditional formatting.

 

 

 

@Quadruple_Pawn Thanks for the response! That's a great idea to use hours until next service. I was able to get that formula to work on mine for the countdown. However what would be even better is if it read hours since last service and counted up. I attached my spreadsheet that I'm working on for reference. I got the "hours since last service" column to count up from the "last service" column, by entering the hours in the "hour reading" column and simply calculating the difference. However I would love for it to turn red when it goes over 500 hours since last service. I had some trouble getting your second formula to work. I attached the error I am given. Again sorry for the Excel ignorance, I just haven't used it much. Thanks again for your help 

@Nick_McClellan 

To apply conditional formatting please follow this path:

Home / Conditional formatting / New rule / Use a formula to determine which cells to format 

Now you can enter a formula (rule) for conditional formatting in the field under:

Format values where this formula is true

In order to format the cells when column "hours since last service" is more than 500 enter below formula:

=IF(K4>500,1,0)

and choose your format.

According to the size of your sheet in the manager for conditional formatting in the field "applies to" you have to enter:

=$K$4:$K$110

I attached picture of how to enter data for conditional formatting, however it is from the german version of excel.