Convert Time Format to Decimal Range

Case Study - Time to Hour Span for Pareto Chart

One hospital operating room was trying to determine how long it takes from the time a patient arrives until they enter the OR. They had the actual times, so they could have run a histogram using QI Macros, but wanted to categorize it as 0-1 hour, 1-2 hours, and so on.

The formula to convert time to an hour-span involves concatenating the hour with a dash (-) and some addition. So, 2 hours and 44 minutes (2:44) becomes 2-3.

Here's the Excel Formula to Convert Time to a Decimal Range:

=HOUR(B2)&"-"&HOUR(B2)+1

View in Excel:

formula to convert time to numbers

Once Completed, Use a PivotTable to Summarize the Data:

  1. Click on Cell C1 and use the QI Macros PivotTable Wizard to summarize the data:
  2. convert-time-decimals-pivot

  3. In the pivot table, click and drag over A3 to B12 and then click the QI Macros menu and then Pareto Chart.
  4. select data

  5. Analyze the Pareto Chart for what it tells you.

convert-minutes-to-hours-pareto

Analyzing the Data

Based on my experience, I’d guess that patients in the 0-1 hour category probably came from the emergency room. Almost half, however, take 2-3 hours from arrival to the OR. If you've ever been a surgical patient, you know that the time spent waiting for surgery is like dog years - every minute seems like 10. From a patient standpoint, wouldn’t the hospital want to reduce all times from arrival to OR?

Learn More:


Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart
histogram
Histogram

QI Macros add-in for Excel makes creating smart charts a snap.