Time Tracking to Reduce Cycle Time

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

One of the principles of Lean Thinking is to eliminate delays which consume up to 95% of the total cycle time. But to understand cycle time and delays, you will need a simple way to track the time it takes a product or service to travel through your process. Excel helps make this easy.

Time Tracking
Entering dates or time into a cell in an Excel worksheet is error prone--too many "/" and ":" and numbers: 05/14/05 07:05. Here's the good news: Excel will populate a cell with the current date and time with a single keystroke.

The keystrokes are:

• Date - Ctrl(;) (i.e., hold down the control key and press the semicolon)
• Time - Ctrl-Shift(;) (i.e., hold down the control and shift keys and press semicolon)
• Both Date and Time: Ctrl(;) + Space + Ctrl-Shift(;) Don't hold the Ctrl key down when hitting Space.

Pretty simple isn't it? This starts to make it easy to measure cycle time or length of stay. Here are some examples:

• A muffler shop measuring the time from when a car arrives until the owner picks it up.
• A hospital emergency room measuring the time from when a patient arrives until they are discharged or admitted to the hospital.
• A transcription service measuring the time from when a transcription is started until it's finished.

Get the idea?

Calculating Cycle Time
To get the actual cycle time from start to finish, simply subtract the start time from the end time using the formula (=endcell-startcell)

Then, right click on the cell to format the calculated cycle time hours and minutes:

This time format will allow you to have more than 24 hours. Other time formats are limited to 24.

Converting to Hours or Minutes
Since Excel stores dates and times as numbers (e.g., 38486.26875 is 5/14/2005 6:27:00 AM) you have to convert the time to hours or minutes to have any hope of graphing the data in a useful way.

Excel will automatically change this calculated value to time format, which isn't that useful, so you will have to change the cell format back to "General" or simply enclose this formula within the "Value" function. In this example, I'm calculating minutes instead of hours:

The result is a decimal number of hours or minutes:

Either of these, hours or minutes, can be used to feed the QI Macros XmR control chart template to create reports automatically as you enter the data.

Case Study - Patient Length of Stay

1. Sign In
2. Triage (nurse)
3. Exam (doctor)
4. Discharge (or bed requested)
5. If admit to hospital (Bed Request Time)
6. Bed Assigned Time
7. Patient to Assigned Bed

I recently created a system for a hospital's emergency department to track patient length of stay. The clerk in the emergency room simply enters the date and times for each patient using the ED Length of Stay template:

Then I use formulas to calculate the times:

Then I can copy and paste a link from the times into the QI Macros XmR template, or use Excel's pivot table function to calculate an average time per day and paste that into the XmR template. Since patient satisfaction starts to decline sharply after two hours in the emergency room, I set the upper specification limit for the histogram to 90 minutes. I can even link the data to a pareto chart by doctor or floor.

Here's my point:
Without measurement, you can't improve. Tracking defects is often easy, but measuring cycle time and delays often seems intimidating. With Excel and a few key strokes you can start tracking key time points in your process. With the QI Macros, you can turn those times into control charts and histograms to help manage the business.

Turn on your time tracking! Then start to eliminate the delays that are costing you time, money and customers.

Need a Custom Tracking Tool for Your Business?
Let us build it for you. Just list your key measurement points (e.g., handoffs) and give us a call or email your request to knowwareman@qimacros.com. We will send you an estimate.

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma

Try QI Macros
FREE
For 30-Days

Or Buy It Now!