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: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3760 times since Fri, Nov 9, 2007
CR: Data grouping tardiness
Viewed 3248 times since Tue, Oct 6, 2009
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2107 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 2031 times since Thu, Feb 23, 2012
CR: Installing CHARTrunner disables desktop wallpaper
Viewed 2099 times since Fri, Nov 9, 2007
CR: Making a chart transportable
Viewed 2149 times since Fri, Nov 9, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2115 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 1971 times since Fri, Jan 22, 2010
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2140 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2351 times since Fri, Nov 9, 2007