?
Solved

TIme Math Excel 2007 PivotTable Calculated FIeld

Posted on 2012-03-09
3
Medium Priority
?
378 Views
Last Modified: 2012-08-14
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!
0
Comment
Question by:jkasavan
  • 2
3 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37708771
Can you not just use:

Arrival_time: TimeValue([Arrive])

in your query, and similarly for the Departure?
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37710780
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.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 37710796
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question