# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IT 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
IT 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
Commented:
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
Commented:
Typo for column B, should be 23/03/2013 14:23.
0
Author 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
Commented:
>>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
IT 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
Author 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
Commented:
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
Commented:
>>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
Author 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
IT 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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

IT ManagerCommented:
updated version, using a single cell only - more for awking?
M--ee---time-duration-2.xls
0
Commented:
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
IT 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
IT ManagerCommented:
C grade?  We answered fully with all the info that was given?  Ouch!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Math / Science

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.