Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

calculate the process time

Hello,

I have built a simulation model for a real case. In order to set the distribution of one of its process. I've tried to use the historical data from that system but I faced this problem:

For each arrival there is a date and time for arrival and the end of process. By calculate the difference we could get the time between the arrival until complete the task. My question is how to calculate the process time only for each arrival to fit the appropriate distribution for that process?
here is an example with some arrivals:

Arrival                       end of process

23/03/2013 12:12      23/03/2013 14:23
24/03/2013 11:03      24/03/2013 12:33
23/03/2013 13:49      24/03/2013 13:51
24/03/2013 10:39      24/03/2013 14:26
23/03/2013 14:30      24/03/2013 14:49
24/03/2013 13:21      24/03/2013 15:17
0
amq10
Asked:
amq10
  • 7
  • 6
  • 3
1 Solution
 
Danny ChildIT ManagerCommented:
Hi,
if your data is in 2 separate columns for the Arrival and End, you can just subtract the Arrival from the End, and Excel will do it.

To cope with periods that run longer than 1 day, it's good to use a custom format of [hh]:mm
as this stops Excel rolling forward any duration longer than 24 hours into days, which often is confusing.

See attached example.
M--ee---time-duration.xls
0
 
Danny ChildIT ManagerCommented:
if your data is arriving in a single cell, you'll have to use LEFT, RIGHT or MID functions to pull out the times you need.  
If you could post an example sheet in this case, it would be very handy.
The good news is that your text seems to have a consistent format - ie dates and times with a correct leading zero if required eg: 03
0
 
awking00Commented:
The problem is that subtraction will not work in the format you presented. Assuming arrival is in column A and end of process is in column B, in column C enter =MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,10), copy that to column D and, in column E enter =column D - column C. Format column E, using More Number Formats ..., as Time of type 13:30. So your first example would show 23/03/2013 12:12 in column A, 23/03/2013 14:232 in column B, 03/23/2013 12:12 in column C, 03/23/2013 14:23 in column D, initially 0.0909722 in column E and 2:11 after formatting.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
Typo for column B, should be 23/03/2013 14:23.
0
 
amq10Author Commented:
Hi DanCh99 and awking00,

The subtracting will return the difference between arrival and end, which represent the time in service and queue. I need time of service only.
0
 
awking00Commented:
>>My question is how to calculate the process time only for each arrival to fit the appropriate distribution for that process?<<
What do you mean by "process time ... to fit the appropriate distribution for that process"? Given your example data, what is it you expect to see as a result and what is the criteria used to determine it?
0
 
Danny ChildIT ManagerCommented:
Subtraction worked for me in Excel 2010, and all I did was paste the Arrival and End into 2 separate columns.

How do you know what the queue length is?
0
 
amq10Author Commented:
Actually, that is my question!!!

I have no idea about queue and process time and looking for that information with arrival time and leaving time only
0
 
awking00Commented:
I started getting dressed at 7:05 this morning and finished at 7:08. Now I want to know how much time I took getting dressed leaving out the time it took to button my shirt. Without knowing how long it took to button my shirt, there's no way to tell. Is there anything such as a formula for computing the percentage of queue time or process time? What is the real life instance you're trying to measure (i.e. what is going on between arrival and leaving)?
0
 
awking00Commented:
>>Subtraction worked for me in Excel 2010<<
DanCh99, what format are you using for the arrival and end? I've tried many, but I keep getting #VALUE! as a result.
0
 
amq10Author Commented:
I have a company, which received customers problems and deal with it and delver the solution to them:

I have time of arrival task and time of finishing the solution. I need to know how long in takes in repairing the problems. finish time - arrival time = waiting time and repairing time.

I  need repairing time only

This is my real problem
0
 
Danny ChildIT ManagerCommented:
So you need some system where each job has an additional time noted on it - the actual start time.

You could make an assumption that the *finish* time of the previous job becomes the start time of the next one, but that only works if each job queues directly after the one before, with no down-time in between.  

awking - my sheet is attached earlier if that helps?
All I did was paste in the rows above to both columns, and delete the unnecessary data from the columns.
0
 
Danny ChildIT ManagerCommented:
updated version, using a single cell only - more for awking?
M--ee---time-duration-2.xls
0
 
awking00Commented:
DanCh99, sorry I didn't see your attached sheet earlier. Your subtraction seems to work because you've changed, for example, the 23/03/2013 12:12, as presented by amq10, to 3/23/2013 12:12, which is why I used the mid, left and right functions to format it. Also, I'm not sure what you meant by deleting the unnecessary data.
0
 
Danny ChildIT ManagerCommented:
I'm in the UK, so that dd/mm/yyyy format is native to me.  Your regional settings in Excel may change that around.  However, the underlying date serial number should make this irrelevant.  

All I meant by the deleting was that I pasted the entire start-finish string into the first cell, and deleted the -finish section.  Then I pasted the same start-finish string into the second cell, and deleted the start- section.

Anyway, we need the OP to come back to us with more info on determining the queue timings...
0
 
Danny ChildIT ManagerCommented:
C grade?  We answered fully with all the info that was given?  Ouch!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now