TIme Math Excel 2007 PivotTable Calculated FIeld
Posted on 2012-03-09
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:
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
=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.
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?