Time Tracking to Reduce Cycle Time

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 - 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.

Excel short cuts to record date and time

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)

excel formulas to calculate cycle time

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

format data and time in excel

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.

formula to convert hours to minutes in excel

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:

formula to convert hours to minutes in excel

The result is a decimal number of hours or minutes:

formula to convert hours to minutes in excel

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

Many hospital emergency rooms are aiming to get a patient from the door to doctor in under 30 minutes. Last year's Baldridge award winning hospital even offers a guarantee that the patient will see a nurse in under 15 minutes and a doctor in less than 30 minutes. To do this, emergency departments have to be able to track the time for each activity:

  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:

track ed events in Excel

Then I use formulas to calculate the times:

formula to convert hours to minutes in excel

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.

xmr chart in excel
histogram in excel 
pareto chart in excel

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., hand-offs) and give us a call or email your request to knowwareman@qimacros.com. We will send you an estimate.

Create these charts and diagrams yourself in just seconds using the QI Macros for Excel...
Try It Now!


How We Can Help Download Free QI Macros Trial Buy QI Macros for Excel Now