We help IT Professionals succeed at work.

TIme Math Excel 2007 PivotTable Calculated FIeld

jkasavan
jkasavan asked
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

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?

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.