jkasavan

asked on

# 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

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.

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:

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!

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") =24*(Departure-Arrive)

=left(Departure,2)*60+righ

=TIMEVALUE(Departure&":00 PM")-TIMEVALUE(Arrival&":0

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).... ....abs((left(Departure,2)

.... 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!

ASKER

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.

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.

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

Arrival_time: TimeValue([Arrive])

in your query, and similarly for the Departure?