Time Difference and Pivot Grouping

[hmahdi]

Hello Everyone,

I have a table with lots columns but two columns with the following example data,

Opened Time Closed Time
09/04/2010 16:50 12/04/2010 16:09
13/04/2010 15:37 13/04/2010 16:28
09/04/2010 14:55 12/04/2010 15:15
09/04/2010 14:55 12/04/2010 15:16
09/04/2010 14:55 12/04/2010 15:17
12/04/2010 15:45 12/04/2010 16:35
12/04/2010 16:32 12/04/2010 17:04
13/04/2010 15:39 13/04/2010 16:20

Check out this screenshot for more info.
\1

When I create a pivot table from this data and want to display it in another form I get empty entry where there is duplicate opened time, I run a formula on the data to generate the time between the two dates but due to pivot table grouping the rows when the open date is exactly the same I am getting errors.

Does anyone know how to stop pivot tables from grouping the rows when the data is the same?

Thanks

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Andy Pope]

Grouping is one of the points of using a PT.

If you do not want the information grouped don't use a PT. Or format it such that it does not include the extra information that causes the grouping.

Or maybe I just don't understand you explanation of the problem and what you are trying to acheive.

[hmahdi]

Thanks for the quick response guys, really appreciated, I have attached a dummy doc to illustrate the problem,

Andy - There are lots of data in the doc i am working with and i am using PT for other parts but this is a issue when you look at the format, the entries are not unique but this is only for a handful out of hundreds.

[Andy Pope]

You can get the pivottable to calculate the elapsed time. Then use a modified version of your formula to display that value.

[hmahdi]

You can get the pivottable to calculate the elapsed time. Then use a modified version of your formula to display that value.

That is a good idea, it actually solves another problem i was facing , calculating the average time to close a request. Let me apply it my table and see the outcome.

The issue that still remains is the blank looking entries, any way to have time show the date even though it is a duplicate?

Thanks again

[Andy Pope]

I don't think so.

You could turn you raw data into a table. Order the OpenTimes columns and filter Closed <> Blank.