TIme Math Excel 2007 PivotTable Calculated FIeld

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!
LVL 2
jkasavanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Can you not just use:

Arrival_time: TimeValue([Arrive])

in your query, and similarly for the Departure?
jkasavanAuthor 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.
Rory ArchibaldCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.