Closest hour (closest before and closest after)

[Carlos_Velez_R]

I have a huge list of hours (units built). How can I get when the last unit of a shift was built and when the first piece of the next shift was built...? Let's say the first shift starts at 6:00 am, I would like to know the hour/minute of the last unit built (previous shift) and the hour/minute the first unit was built during first shift.
Thanks a lot,
Carlos

 

[vletm]

You find '06:00' -row.
After that You can get previous and new rows times (closest before and closest after).
If there are more shift start times then You gotta do same with other times.
Others than You could only guess - what is Your list of hours?
... Without a sample Excel-file with some sample data.

 

[Carlos_Velez_R]

You find '06:00' -row.
After that You can get previous and new rows times (closest before and closest after).
If there are more shift start times then You gotta do same with other times.
Others than You could only guess - what is Your list of hours?
... Without a sample Excel-file with some sample data.

 

[Carlos_Velez_R]

You find '06:00' -row.
After that You can get previous and new rows times (closest before and closest after).
If there are more shift start times then You gotta do same with other times.
Others than You could only guess - what is Your list of hours?
... Without a sample Excel-file with some sample data.

We have a bottle neck equipment, and we would like to understand if this equipment is being used properly and at full capacity. This is just 1 machine, but it would be used to to measure the rest of them if is required. thanks !

 

[vletm]

Your original writing gives other image than Your sample file.
I would do solve it something like this ...
# have as much data as You need in those two columns ( Your date & times ... are text )
# press [ Do It ] to get ... Your explained results (but with hh:mm:ss).
There are a lot of values to verify that results are correct ( red values are those LOSTs ).

 

[Carlos_Velez_R]

Seems like it works... Still don't know how....

 

[Carlos_Velez_R]

Sorry, I dint tell you Im color blind...I can see red.....Im sorry, so many things....

 

[vletm]

... hmm?
I started to wondering still.
I've stronger image that this modified version works more correct (than previous one).
'LOST is time, which is out from Time1 & Time2'
But ... ... You should see - what is correct - even without colors.

 

[p45cal]

In the attached:
On sheet SCHEDULE, a table at cell M7 which you can adjust. It contains the start times of breaks and handover times in the first column and the duration of such events in the next column. A handover has 0 duration.
On sheet BUILD INFO:
A table at cell B6 which is your raw data made into a proper Excel table called Table1
A table at cell E6 which is your raw data converted to proper dates and times. This table can be deleted, I left it there in case you want to use it to make, say, pivot tables and the like.
A results table at cell H6. Should you change the data in the raw data in Table1, this results table can be updated by right-clicking somewhere in the table and choosing Refresh.
Note that I've added a column to show how many builds are produced during the break times, since production doesn't seem to stop during breaks.
Also note that the results are not the same as @vletm 's because his often seem to use the date/time before the date/time before (the penultimate erather than the ultimate time) before the breaks/handovers.

Completely separately and irrelevant to your question, I've also created a table at cell S3 where I've grouped your data into 10 minute slots and counted the number of builds in each, then plotted these on a chart at cell V3.

 

[vletm]

p45cal​

About results differences ...
I used as asked closest before and closest after.
Eg if break ends time is 08:10
if the closest time is (before) eg 08:09 (too short break) then there is no lost
but if the closest time is (after) eg 08:11 then there is 1minute lost (too long break).
( And something same with breaks start time )
But if @Carlos_Velez_R has an idea to get eg something like Yours then I can modify mine code.
Usage: Have data and press [ Do It ].

 

[p45cal]

Raw data:
Which is the closest before 14:00?:


I say:


You say:

 

[vletm]

prev black time 13:58 is the closest ( = the nearest )
with gap 00:01:20 before 14:00
... that makes LOST
( next values are gray ... bigger gap 00:01:43 (~23sec) & at 14:01 )

I wondered different cases than You:
How long time would eg Build ID 10938205 take?
... or is it matter?

Maybe one day, I'll know - what really would need to get?
( too many times need can be different than want or ask )
Have You compared original #1 writing to #3 file?

 

[p45cal]

prev black time 13:58 is the closest ( = the nearest )
with gap 00:01:20 before 14:00
... that makes LOST

So you think that
13:58:40 with 1 min 20 secs before 14:00
is closer to 14:00 than
13:58:57 with 1 min 03 secs before 14:00?

I'm sure there's a world somewhere where you're right.

 

[vletm]

Oh ... now I see Your point.
I remember that in my previous version - I take care this ...
... but after some hours, I changed logic that LOST is only outside of 'break'.
I gotta modify this back ... Thank You.
( Done )
But still - what really needs is - which needs to know?

 

[Carlos_Velez_R]

@vletm @p45cal thank you so much for your support. I know all the hard work you do to help people with real needs like this one. If would be great if you could develop this process so I could run it every day. The goal of all this is to have information to make people accountable during their work/breaks hours. What you did definitely is what I need, but I need to run it every day to tracks these numbers. Thanks a lot again.!

 

[vletm]

Carlos_Velez_R​

How many machines?
Do You run machines data one-by-one?
Is it something like this sample PDF - what Do You need?
Left side shows daily lost's per machine.

Of course NOW, those values are ... same ... with all three machines.

 

[p45cal]

I need to run it every day to tracks these numbers.

Two ways:

1. Paste the data into Table1 (cell B7 of the BUILD INFO sheet) overwriting what's already there and ensuring the new data is completely covered by the table extents and that all the old data is gone. Resizing of the table mostly happens automatically, but if not, you can adjust the extents of the table by either dragging the grab handle at the bottom right corner of the table or by clicking on the Resize Table icon in the Properties section of the Table Design tab of the ribbon.




Then going to the results table, right-clicking and choosing Refresh, or by clicking on the Refresh All icon of the Queries & Connections section of the Data tab of the ribbon:



2. You must get this data from somewhere; where does it come from and how do you currently get the data into Excel?
Built-in to your version of Excel are very many ways of grabbing external data easily. Your daily routine within Excel could be reduced to only refreshing the result table (and if you want to keep the results, copying them somewhere) because we can point Excel to look at a specific file (eg. a text or csv file) or a specific folder containing the file(s). This is what Get & Transform Data is all about. It will be more robust than manually cutting and pasting data from somewhere else.

I've attached a simplified version of the workbook here.

So, how does the data currently get into Excel, and if from a file or two, could you attach an example of such a file here (undoctored and never having been previously opened or even looked at by Excel AT ALL!)?

 

[vletm]

My Next version ...
If there are more than two machines then my above PDF-layout will be a challenge to read.
This version has basically same layout as my previous sample file.
There is possible with [ Get Data ] to have as many machines and days as needed.
Now - The layout have to be same as Your original's files layout ( one machine and one day data ).
Usage:
# [ Get Data ]'s - if new data
# Select Date - if different
# Press [ Do It ]
>> It will show one day data (from 05:00 to next days 05:00) from all machines
(( Now, there are three machines with same data ))

Question: Is it known that 'morning' shift works different way (around those every 2nd hours) than two other shifts?