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?
in your query, and similarly for the Departure?