We help IT Professionals succeed at work.

# TIme Math Excel 2007 PivotTable Calculated FIeld

on
Greetings Experts:

I have an Access 2007 data source that has information about trucks arriving and departing. The time stamps are text fields in military time, like this:

Arrive     Departure
08:34     11:15
17:59     18:28

I tried to create a calculated field in Excel pivottable to give the elapsed time in minutes, but I am unsuccessful. Every attempt either shows 0, 12:00:00 AM, 00:00 or #VALUE. I tried

=Departure-Arrive
=24*(Departure-Arrive)
=left(Departure,2)*60+right(Departure,2))-(left(Arrive,2)*60+right(Arrive,2)
=TIMEVALUE(Departure&":00 PM")-TIMEVALUE(Arrival&":00 PM")

plus several other variations along those lines.

When I create a formula to do the time math in a cell outside the pivot table, it kind of works.

=24*(N13-M13)

If N13 is "14:14" and M13 is "10:26" I get 3.8 hours.

I DID get the elapsed time into the pivottable by including this in the SQL Command Text in the Definition of the Connection properties:

SELECT ....(fields I want)....

....abs((left(Departure,2)*60+right(Departure,2))-(left(Arrive,2)*60+right(Arrive,2))) AS 'Elapsed'....

.... FROM (Access data file)....

The same formula in the Calculated Field dialog of the pivottable didn't work.

So the question is:

How do I get the calculated field in the pivottable to work and not have to resort to using the substring extraction functions in the SQL?

Thanks!
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Can you not just use:

Arrival_time: TimeValue([Arrive])

in your query, and similarly for the Departure?

Commented:
Yes, TimeValue([Departure])-TimeValue([Arrive]) works in the query. I am able to get the elapsed time by creating the calculation in the query.

But, what I am trying to find out is if there is a way to create the elapsed time as a calculated field in the pivot table.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
I don't believe so since any operations you do on a calculated field occur after the field values have been aggregated. Since your 'values' are actually text, the aggregation returns 0 and anything you try and do after that is too late. You either need to alter the query, or use an additional formula column in the data.

Regards,
Rory