I was wondering If any excel gurus would know the best formula to work out first stoppage after PM working with raised by dates and PM and BK coded columns?
Any examples are welcome? I tend to waste a lot of time trying to figure the best formula to show the first workorder with a BK code after an PM workorder has been closed.
I guess in reliability it is important to track why things fail after servicing if we are to improve our quality of work?

What is a BK code? Does BK stand for breakdown?

I don't understand your question. Maybe you can rephrase.

My guess is you want to identify which is the first work order with the breakdown code being ticked? Is this correct?

Yes, I am simply trying to be able to use excel to extract the first entry after a listed PM service to indicate what is the first failure after each service and I was wondering if anyone else uses this method and can give me an example of the formulas used to display the data on excel. At present I export the Data onto a spread sheet but I am trying to design a formula to simplify to process.
I am interested in what methods are being used elsewhere?

Sorry
[LIST]

Bk=Breakdown
AD= Accident Damage
PL= Planned
WR= Warranty
SG= System Generated
So if a list is sorted in date sequence i would like to have another cell in the same row but different column display the first Bk work order that occured in date sequence after every PL or SG type.

Example an underground Haulage truck has just gone back to work after leaving the workshop and in 20hrs time it is repaired again for lets say having loss of power. The new column will display the loss of power header from the work order that occured straight after the PM service.
I hope that explains it clearer?
Regards Dusted.

What CMMS do you use? Do you use Excel to record work orders?

I still cannot figure out whether your problem is related to CMMS or IT/Excel.

Hi Dusted,

I attach an Excel file prepared for 50 events to be entered in columns B (codes) and C (dates). I think this is what you are looking for. If this is not the case, please let me know and I will try again.

There is an example already in the spreadsheet showing 20 events. The BK-PL time intervals are automatically calculated in column D.

Columns G and H are used only for auxiliary calculus and should be hidden during normal use.

Should you approve this solution and the coding of columns D, G and H can be extended as further down as you wish, in order to encompass a much larger period.

Hope it helps,

Regards,

Rui

Dusted_3.xls (24 Kb, 16 downloads)

Thankyou I will check it out and give you some feed back

Regards
Dusted

Thanks Rui,
I want to be able to add a work order no column and a work order description column as well. Also I will need an Equipment Reference column. I currently average around 700 - 1000 work orders per month as we have a lot of equipment and there are 4 major workgroups. I like what you have done but I intend to paste my data onto your spread sheet, can you tell me if its safe to create extra columns without changing your formulas? I also want to go through the archives to establsh FSAPM.(fist stoppage after PM)trending. I have approx 16K workorders on data base. I appreciate your help.
Regards
Dusted.

I have worked on the sheet have a look and see if anyone can think of any improvements. Remember the idea is to be able to export from your main Data Base CMMS then use that data in this sheet to Analyse different conditions.

I look forward to seeing what people are able to come up with?

Regards
Dusted!

First_stoppage_after_PM_Calculator_in_days_.xls (230 Kb, 19 downloads) First stoppage calculator

Hi Dusted,

Yes you can create as many extra columns as you want, provided that you don’t create them in between columns G and H. These two columns have to be maintained side by side, otherwise formulas in column D will have to be adjusted manually. All you are allowed to do is to move them together to some other location in the spreadsheet or even to a new one. For any other changes in the spreadsheet, formulas will adjust automatically and accordingly.

The spreadsheet attached to your last post cannot be opened. It requires a password!

Please feel free for further questions.

Regards,

Rui

I attach another version of the spreadsheet I attached the other day. The code is more straightforward this time because it doesn´t need any auxiliary columns anymore.

Hope it is of better help.

Rui

Dusted_4.xls (26 Kb, 10 downloads)