• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find when all machines running at a time with running hours

W

welsel

New Member
In our factory we have 7 machines.These machines got breakdown or stoppages during the year at various times.We maintained excel sheet containing M/c number(1-7) stopped & started date with time each in separate column.

I want to find whenever all 7 machines are in continuous service at a time for the given financial year ( From - To with Start & Stop date & time) in separate Sheet as table.


Can any one give me an idea for logic's & macro for finding above

Here I have given the file download link


http://speedy.sh/zWw6p/STOPPAGE-DATA.xlsx
 
Hi ,


Can you check out the following workbook ?


http://speedy.sh/vESsB/STOPPAGE-DATA.xlsm


Please verify that the program logic is fool-proof with as much sample data as possible before using it on real data.


Narayan
 
Hi, welsel!


Please give a look at this file:

https://dl.dropbox.com/u/60558749/How%20to%20find%20when%20all%20machines%20running%20at%20a%20time%20with%20running%20hours%20-%20STOPPAGE%20DATA%20%28for%20welsel%20at%20chandoo.org%29.xlsm


I checked it against NARAYANK991's solution and we agree up to cells I24:M24, from that point in advance there are a lot of differences.


Please compare both outputs and let us know about your conclusion.


Regards!
 
Hi Pablo ,


Just to let you know that the formulae in column M are not mine ; they were there already in the file uploaded by the OP.


I have only put in the values in columns I , J , K and L.


I downloaded your file , and I would request you to check the results for August. August is the litmus test month !


Narayan
 
@NARAYANK991

Hi!

We do agree until 30/07/2011 14:41, I24:M24.

Your I25:M25 line shouldn't be there as per line A30:F30.

Your I26:M26 line is a zero minutes gap, same from/to.

Your I27:M27 line shouldn't be there as per line A37:F37.

Your I28:M28 line is a zero minutes gap, same from/to.

I stopped the analysis there.

Regards!
 
@NARAYANK991

Hi, Narayan!

We overstepped into each other with our last two comments. Yes, I know about M column's formulas, I found them there too. I erased them without having noticed when cleared the output range, and then found easier to recreate them that defining two ranges and so on.

When I say "your Inn:Mnn" (24 in first comment and 25 thru 28 in last one) I actually mean the 4 fulfilled columns I:L as M is just a formula which I consider right.

I'm gonna check against August manually and write here again.

Regards!

PS: I think we should be buying tons of phenolphthalein... just in case :)
 
Hi Pablo ,


Just to bring you up to date on the data !


I found that towards the end of the data , I think from November onwards , I was getting warning messages when sorting the data in columns A through F. This may have been to do with the wrong date format , but I didn't analyse it too much. I re-entered the data from November onwards myself , correcting wherever required. Take the input data from my file , if possible.


Regarding your other comments , I think you should re-check.


Narayan
 
Hi, welsel!

Hi, NARAYANK991!


I reuploaded my file with NARAYANK991 data, reprocessed it, and retrieved better values than with original (welsel) data: check for example, H43:L45 of sheet 'Comparison', they are right as there are not any fault after 16/01/2012 17:31.


At this time at night, it's the most I can't do without becoming squint, so... tomorrow will be another day.


Please download again my file from same previous link:

https://dl.dropbox.com/u/60558749/How%20to%20find%20when%20all%20machines%20running%20at%20a%20time%20with%20running%20hours%20-%20STOPPAGE%20DATA%20%28for%20welsel%20at%20chandoo.org%29.xlsm


It contains the dataset provided by NARAYANK991. I hope that data in 2nd sheet will help to find out the errors of differences that might appear to remain in both solutions.


Regards!
 
Hi, NARAYANK991 & Sri JB7 thanks for your pain taking work.

we are calculating stoppages for each month end (For the month & Upto the month). That is why we close the stoppage of each m/c at 24:00 Hrs and start it again at 00:00 Hrs in next month. This is the reason for Zero min.gap between two stoppages.
 
Hi, welsel!


Thanks for your feedback but I was referring to cells on column range I:M, which are those which the solution (either NARAYANK991 or mine) should provide. In those ranges, even we both respected the cut at end of month, I found in the other suggestion many records with zero gap that I think there shouldn't be there. The other day was late at night, so we all (say "Yes!", NARAYANK991) agreed to wait until you tried the two solutions and tell us if they helped with your issue.


BTW, firstly I didn't notice the format type errors or wrong data as no runtime error arised, but I strongly suggest you to check your data (value and format) prior testing both cases, so as to avoid unnecessary wasted time.


Just advise if any issue.


Regards!
 
Sorry not replying Friends. I Was in hospital for five days.Thanks.

Now let us talk about the problem.


1.We are maintaining this record in separate file for each year.


2.My main aim is to find the duration of all m/c in continuous from the given data (particular m/c may be in service from previous financial year also which is entered manualy) (Which is shown in separate table in new uploaded file with additional records).


3.Some logics may help you


a.Get start,stop datas with date & time for each m/c (7 rows of datas)


b.Using MAX function we can find highest start value (Last start time) & corresponding m/c no. from these 7 values (Excel stores date & time in numbers).


c.We must check weather the trip time of each m/c is just greater than the latest srart time (step b ) from these 7 values.


d.Using MIN function we can find minimum stop value (earliest stop time).


d.I think Time between MAX & MIN is all 7 m/c in continous service.


e.Then we get start & stop time values greater than MIN value of step d.


f.we can repeat the steps.


Their are some errors.Between 1/7/2011 & 31/12/2011 there is of records of all m/cs in service


Pl refer my updated file with manually entered values of all m/cs service time and time map.


http://speedy.sh/fSbut/New-STOPPAGE-DATA.xlsx
 
Hi, welsel!


Hope you're all Ok now, and that'll keep on alike.


Thank you very much for the detailed explanation, for what I wrote with NARAYANK991 both of us have understood the goal to achieve. Just that the scores didn't show the same results.


With your uploaded data, I run process, run NARAYANK991's one and copy the two outputs as well as your manually provided one to worksheet "Comparison2" (added to you "Map" worksheet, just to keep all stuff together).


I didn't verify each difference but the three results don't match. So I upload an intermediate file without the final analysis just for your information. I hope that tomorrow I could update the comparison or my process updated if found out the source of the disparities.


The link, same as previous:

https://dl.dropbox.com/u/60558749/Dynamic%20Hyperlink%20shapes%20-%20Mapper%20%28for%20electricmice%20from%20Jordan%20at%20chandoo.org%29.xlsm


Regards!
 
You must log in or register to reply here.
Back
Top