Are you searching for the best formula in Excel to determine the first stoppage after a PM job, using raised by dates and coded columns? Any helpful examples would be appreciated. I often spend a significant amount of time trying to find the most effective formula to display the initial workorder with a BK code following the closure of a PM workorder. Tracking the reasons for failure after servicing is crucial for improving work quality in the field of reliability.
What is a BK code and does it signify a breakdown? I'm a bit confused by your question – perhaps you could provide some clarification. Are you trying to determine the initial work order with the breakdown code marked as complete? Is my interpretation accurate?
I am currently exploring ways to utilize Excel for identifying the initial issue following a scheduled PM service. Does anyone else employ this approach and could provide a sample of the formulas utilized in Excel for this purpose? Currently, I export the data to a spreadsheet but aim to streamline the process with a formula. I am curious about alternative methodologies in use.
Apologies for the confusion! In the system, different codes are used to indicate various types of work orders, such as Breakdown (Bk), Accident Damage (AD), Planned (PL), Warranty (WR), and System Generated (SG). If we have a list of work orders sorted by date, I am looking to have another cell in the same row but a different column display the first Breakdown (Bk) work order that occurs in date sequence after every Planned (PL) or System Generated (SG) type. For example, if an underground Haulage truck returns to work after leaving the workshop and then requires repairs for loss of power 20 hours later, the new column will show the header for the loss of power work order following the PM service. I hope this explanation clarifies things. Thank you! - Dusted.
Which Computerized Maintenance Management System (CMMS) are you currently using? Are you relying on Excel for tracking work orders? It's still unclear whether the issue you're facing is tied to your CMMS or IT/Excel integration.
Hello Dusted, I have attached an Excel file containing data for 50 events, with codes in column B and dates in column C. This should meet your requirements. If not, please let me know so I can make any necessary adjustments. The spreadsheet includes an example with 20 events already filled in. The time intervals in column D are automatically calculated for the BK-PL events. Columns G and H are for additional calculations and can be hidden during regular use. If you are satisfied with this solution, the coding in columns D, G, and H can be expanded to cover a longer time period. I hope this information is helpful. Best regards, Rui Attachment(s): Dusted_3.xls (23 KB) - Version 1
It sounds like you're dealing with a complex pattern recognition scenario in your data. One way to tackle this might be to use a combination of the VLOOKUP and MATCH functions in Excel. Essentially, you can use MATCH to find the row number where the PM workorder gets closed (signifying the end of servicing) and then have VLOOKUP start looking in BK code column from the next row onwards to find the first non-empty cell (which would suggest a stoppage). This approach should help you track failures post-servicing with relative ease. Remember to sort your dates in ascending order for the formula to work correctly. It's great to see you're committed to improving work quality by understanding the causes of these incidents.
Absolutely, tracking after-servicing performance plays a pivotal role in reliability engineering. It seems like you want to identify the first BREAKDOWN (BK) work order raised post-closure of a PM work order. The Excel `MATCH` function helps here. Assume your "Raised Date" is in column A, "Work Order Status" in column B, and "Code" in column C. For a specific PM work order date (Suppose D1), a formula might look like `=INDEX(A2:A100, MATCH(1,(A2:A100>D1)*(B2:B100="BK"),0))`. The initial '1' in the MATCH function aligns with the first row that matches all criteria. The double quotation marks ("BK") ensure only the rows coded BK are considered. This is an array formula, so remember to input it with Ctrl+Shift+Enter instead of just Enter. Try modifying as per your worksheet layout, and let us know if you need help with adjustments!
You might find the LOOKUP function in Excel useful. Essentially, you could set it up to search for the first instance of a BK code in a coded column, after a PM workorder has been closed. You can use your raised by dates to sequence the data chronologically. You only need to structure your formula in consideration of these, and remember, the LOOKUP function will stop at the first match it encounters. It may take a bit of trial and error to get the exact formula that fits your specific criteria, but I'm certain this function should get you started in the right direction.
✅ Work Order Management
✅ Asset Tracking
✅ Preventive Maintenance
✅ Inspection Report
We have received your information. We will share Schedule Demo details on your Mail Id.
Answer: 1. How can I use Excel to find the first stoppage after a PM job based on dates and codes? - You can achieve this by utilizing Excel formulas to search for the initial workorder with a specific code following the closure of a PM workorder. 2. Can you provide an example of an Excel formula for identifying the first stoppage after a PM job? - Certainly! One way to do this is by using a combination of functions like INDEX, MATCH, and IF to locate the first occurrence of a specific code after a PM job's completion date. 3. Why is it important to track the reasons for failure following servicing in the field of reliability? - Tracking stoppages and failure reasons after PM jobs is vital for improving work quality and reliability in the field. It helps in identifying trends, addressing recurring issues, and enhancing overall maintenance processes. 4. How can Excel formulas assist in improving work quality by tracking stoppages after PM jobs? - Excel formulas can streamline the process of identifying and analyzing stoppages after PM jobs. By automating the search for specific codes and dates, it saves time and provides valuable insights for continuous improvement in work quality and reliability.
Join hundreds of satisfied customers who have transformed their maintenance processes.
Sign up today and start optimizing your workflow.