• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Working with Time in Pivot Tables/Functions

I export data from a phone system daily.  This tells me how many total phone calls were made and total talk time.  I use a formula to figure out average talk time each day.  I want to create a pivot table using this data to summarize weekly total calls and total talk time as well as average talk time and I also want to be able to summarize this for monthly and quarterly data.  I can't figure out how to work with time when using formulas and pivot tables.  Can anyone help?  I have attached a small sample of the data that I intend to use.  I know pivot table basics.  My problem is working with time. Time-Source-Data-Example.xlsx
0
karaflanagan
Asked:
karaflanagan
1 Solution
 
Rory ArchibaldCommented:
Here's a demo. I replaced your average column with a column to convert the time data (which is actually stored as text) to real times, then built a pivot table, added the data fields, created a calculated field dividing talk time by call count to get average time, then formatted both time fields with a custom format of [hh]:mm to show elapsed time (in case you had an average over 24 hours!)
Let me know if you have any questions.
Time-Source-Data-Example.xlsx
0
 
karaflanaganAuthor Commented:
Awesome!  That is exactly what I was looking for.  Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now