How to Pivot with Daily Averages based on Times in Excel?

Question

This sheet contains a call log where there are a variable amount of calls per day, with a variable amount of call durations. My goal is to create a chart that shows the number of calls as each day passes, but also show the average call duration per day.

I am getting stuck on the pivot table where I want to gather a column of Average of Call Duration. Problem is I can't figure out a way for to tell Excel to calculate the average on the amount of calls per day. The current table shows Call Duration as hh:mm:ss, and I want to show the Average of Call Duration as mm:ss, especially in the chart.

Attaching same picture of values and how my pivot table currently looks. Any help would be greatly appreciated.

To summarize, I need to figure out

1. Tell Excel that the average is based on hh:mm:ss so it doesn't cause issues (if there's that chance) Tell Excel to calculate averages per day

Thanks! (Using Excel for Mac 2011)

http://s22.postimg.org/w1op2ue0h/Pivot.png (values of the table) http://s22.postimg.org/bt1bh4epd/Values.png (pivot from those values where you see avg duration 1442.00)

Answer 1

In the pivot table, put the date into the row labels box and the duration into the ∑ Values box.

Click on the down arrow where it says Sum of Duration and select Value Field Settings.

Change Sum to Average under Summarize Field By.

Click the Number Format button and choose a Time format e.g. hh:mm:ss (or you can choose mm:ss from the Custom option).

The average duration for each day should be shown in hh:mm:ss or mm:ss format.