Working with Time in Pivot Tables/Functions

Posted on 2011-04-27
Last Modified: 2012-08-13
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
Question by:karaflanagan
    LVL 85

    Accepted Solution

    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.

    Author Closing Comment

    Awesome!  That is exactly what I was looking for.  Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now