Hi,
In Excel, we have some raw data that calculates the time difference between 2 cells. Example:
12/1/2005 3:05:05 PM formatted as Time (3/4/1997 1:30:00 PM)
12/1/2005 11:10:10 PM formatted as Time (3/4/1997 1:30:00 PM)
The answer is: 8:05:05 formatted as Time (37:30:55)
This is fine. But, we then have a pivot table and chart that sorts the results by highest elapsed time. This is an average and works fine. In the pivot chart, when the user double clicks on the average number, it opens a new worksheet displaying all the details of that average value. But the elapsed time always shows as a time like 8:05:05 PM. Then we have to manually reformat that cell back to Time (37:30:55), so it will drop the AM or PM portion.
How can I possibly set this up so the reformatting in the details sheet doesn't need to take place?
Thanks for any help !
Paul
In Excel, we have some raw data that calculates the time difference between 2 cells. Example:
12/1/2005 3:05:05 PM formatted as Time (3/4/1997 1:30:00 PM)
12/1/2005 11:10:10 PM formatted as Time (3/4/1997 1:30:00 PM)
The answer is: 8:05:05 formatted as Time (37:30:55)
This is fine. But, we then have a pivot table and chart that sorts the results by highest elapsed time. This is an average and works fine. In the pivot chart, when the user double clicks on the average number, it opens a new worksheet displaying all the details of that average value. But the elapsed time always shows as a time like 8:05:05 PM. Then we have to manually reformat that cell back to Time (37:30:55), so it will drop the AM or PM portion.
How can I possibly set this up so the reformatting in the details sheet doesn't need to take place?
Thanks for any help !
Paul