Software tip: Charting time data on a control chart

Matt SavageRecently I was given an Excel file that contained some data for the time it took to complete a task. The data had a column that listed the “time” and the data was in time-of-day format. I wanted to make a control chart showing how long, in minutes & seconds, a task took. Unfortunately, Excel categorized this as a Date/Time field, which can not be easily charted. Since the column listed the time the task took in hours:minutes:seconds, I needed a way to convert this to a number that I could chart on a control chart.

For example, the data listed:

Elapsed Time

0:15:20
0:13:43
0:16:22
0:16:57
0:15:08
0:14:36

My colleague, Josh (a PQ Systems tech support analyst) showed me a function in Excel that will covert time to a number that can be easily charted. The formula in Excel to convert a time to a number is: =MINUTE(A2) & “.” & SECOND(A2) where A2 is where the time is located.

If you are ever faced with trying to chart elapsed time, consider the formula above. Next, I easily created the following CHARTrunner control chart:

Click to enlarge chart.