Home » Categories » CHARTrunner

Cr: Charting elapsed time

Revision Date: 2005-09-19

Problem:

Many users create columns in Excel to calculate the difference between two times. Usually a begin time and an end time are used to create an elapsed time, which will become the data for a chart. Depending on how this result is calculated and formatted within Excel, CHARTrunner will see the results differently.

Solution:

Review the following Excel sheet:

Begin

Finish

Elapsed1

Elapsed2

Minutes

Seconds

8:10

9:03

0:53

0.036806

53.00

3180.00

7:50

8:42

0:52

0.036111

52.00

3120.00

8:18

9:07

0:49

0.034028

49.00

2940.00

8:05

9:03

0:58

0.040278

58.00

3480.00

10:00

10:52

0:52

0.036111

52.00

3120.00

8:25

9:18

0:53

0.036806

53.00

3180.00

7:14

8:02

0:48

0.033333

48.00

2880.00

8:17

9:14

0:57

0.039583

57.00

3420.00

7:08

7:54

0:46

0.031944

46.00

2760.00

The column Elapsed1 contains this formula: =B2-A2. Since the two columns in the formula are both formatted as a time, Excel may automatically format Elapsed1 as a time. CHARTrunner will see Elapsed1 as a Date/Time column.

Elapsed2 contains the same formula. However, it has been formatted as a number with 6 decimal places. This number is the internal representation used by Excel. It expresses the amount of time as some fraction of an entire 24-hour day.

The Minutes column contains this formula: =(B2-A2)*1440. The column is formatted as a number with two decimal places. The 1440 converts the fraction into number of minutes. CHARTrunner sees this as a number.

The Seconds column contains this formula: =(B2-A2)*86400. The column is formatted as a number with two decimal places. The 86400 converts the fraction into the number of seconds. CHARTrunner sees this as a number.

In CHARTrunner, if you use the Elapsed1 column as a measurement, all of the data points will plot at zero. CHARTrunner does not understand time values that contain the colon character, so it stops after the zero. The solution is to use a column such as Minutes or Seconds as your measurement. Although you could use the Elapsed2 column, the chart would not be scaled in understandable units such as minutes or seconds. There are other ways to solve this problem. Search the Excel help file for “difference between two dates.”

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
CR: CR1.5 - My control chart data lines are not printing
Viewed 2152 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2451 times since Fri, Nov 9, 2007
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2064 times since Fri, Nov 9, 2007
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 1982 times since Fri, Nov 9, 2007
CR: A simple query pattern for summarizing raw data
Viewed 2860 times since Tue, Oct 6, 2009
CR: Show every date on the chart
Viewed 2189 times since Thu, Jan 31, 2008
CR: Collating sequence not supported
Viewed 2570 times since Tue, Dec 11, 2007
CR: Charts with large amounts of data
Viewed 1870 times since Fri, Nov 9, 2007
CR: CHARTrunner and Open Office
Viewed 2338 times since Fri, Nov 9, 2007
CR: CHARTrunner and SharePoint
Viewed 3899 times since Fri, Mar 9, 2012