Using PivotTable to calculate average duration

%3CLINGO-SUB%20id%3D%22lingo-sub-1681742%22%20slang%3D%22en-US%22%3EUsing%20PivotTable%20to%20calculate%20average%20duration%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681742%22%20slang%3D%22en-US%22%3E%3CP%3EHad%20below%20error%20message%20when%20using%20PivotTable%20to%20calculate%20average%20duration.%26nbsp%3B%20I've%20highlighted%20the%20formular%20i%20used%20to%20calculate%20this%20ducation.%26nbsp%3B%20%26nbsp%3B(If%20I%20create%20another%20new%20column%20and%20type%20the%20duration%20manually%2C%20It%20will%20work)%26nbsp%3B%3C%2FP%3E%3CP%3Esample%20data%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ivy_Hai_1-1600398989047.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219424i482C16965274073A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Ivy_Hai_1-1600398989047.png%22%20alt%3D%22Ivy_Hai_1-1600398989047.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ealready%20make%20sure%20the%20'number%20format'%20is%20set%20to%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ivy_Hai_2-1600399010038.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219425i0A169DB6790DF84F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Ivy_Hai_2-1600399010038.png%22%20alt%3D%22Ivy_Hai_2-1600399010038.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1681742%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1681872%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20PivotTable%20to%20calculate%20average%20duration%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798242%22%20target%3D%22_blank%22%3E%40Ivy_Hai%3C%2FA%3E%26nbsp%3BThe%20formula%20you%20used%20to%20display%20duration%20created%20texts.%26nbsp%3BSimply%20use%20%3DD2-C2%20and%20custom%20format%20as%20mm%3Ass%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20you%20can%20refresh%20the%20pivot%20table%20to%20display%20average%20call%20duration%20per%20day.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Had below error message when using PivotTable to calculate average duration.  I've highlighted the formular i used to calculate this ducation.   (If I create another new column and type the duration manually, It will work) 

sample data attached. 

 

Ivy_Hai_1-1600398989047.png

already make sure the 'number format' is set to time. 

Ivy_Hai_2-1600399010038.png

 

 

2 Replies
Highlighted

@Ivy_Hai The formula you used to display duration created texts. Simply use =D2-C2 and custom format as mm:ss 

 

Now you can refresh the pivot table to display average call duration per day. See attached.

Highlighted

@Ivy_Hai 

As variant you may use calculated field and use it in PivotTable

image.png