Hi,
I wanted to share this information with other users in case they also encounter the same headaches I've had with the limited functionality of the reporting part of RC.
My requirement was to be able to determine Total Calls / Inbound / Outbound / Total Time etc Obviously scheduled reports under Call Log are pretty unfriendly, Simple logs dont show the Users, Detail reports are just over complicated.
I found running the Reports daily for the previous day for my defined users yielded clearer results in the exported Call Data tab.
BUT, more problems arose where the data in Call Duration is defined as xxH xxM xxS so isn't a format something like Excel will work into a time format you can use constructively, ie Total Time for User X
Anyway here are the formulas for excel to sort this out.
Assuming that Column C is the original data from the export, add in the Modified, h, m, s Time columns, then use the following formulas. Note in this example i'm refering to the data in Column C, Row 3
Column D
=IF(ISERR(FIND($E$2,C3))=TRUE,IF(ISERR(FIND($F$2,C3))=TRUE,"0h 0m "&C3,"0h "&C3),C3)
Column E
=IFERROR(LEFT(D3,FIND("h", D3)-1), 0)
Column F
=IFERROR(LEFT(RIGHT(LEFT(D3, FIND($F$2,D3)),FIND($F$2,D3)-FIND($E$2,D3)-1), FIND($F$2,RIGHT(LEFT(D3, FIND($F$2,D3)),FIND($F$2,D3)-FIND($E$2,D3)-1))-1), 0)
Column G
=IFERROR(LEFT(RIGHT(D3,FIND($G$2,D3)-FIND($F$2,D3)),FIND($G$2,RIGHT(D3,FIND($G$2,D3)-FIND($F$2,D3)))-1), 0)
Column H
=TEXT(E3&":"&F3&":"&TRIM(G3),"hh:mm:ss")
Hope that helps someone.
I wanted to share this information with other users in case they also encounter the same headaches I've had with the limited functionality of the reporting part of RC.
My requirement was to be able to determine Total Calls / Inbound / Outbound / Total Time etc Obviously scheduled reports under Call Log are pretty unfriendly, Simple logs dont show the Users, Detail reports are just over complicated.
I found running the Reports daily for the previous day for my defined users yielded clearer results in the exported Call Data tab.
BUT, more problems arose where the data in Call Duration is defined as xxH xxM xxS so isn't a format something like Excel will work into a time format you can use constructively, ie Total Time for User X
Anyway here are the formulas for excel to sort this out.
Assuming that Column C is the original data from the export, add in the Modified, h, m, s Time columns, then use the following formulas. Note in this example i'm refering to the data in Column C, Row 3
Column D
=IF(ISERR(FIND($E$2,C3))=TRUE,IF(ISERR(FIND($F$2,C3))=TRUE,"0h 0m "&C3,"0h "&C3),C3)
Column E
=IFERROR(LEFT(D3,FIND("h", D3)-1), 0)
Column F
=IFERROR(LEFT(RIGHT(LEFT(D3, FIND($F$2,D3)),FIND($F$2,D3)-FIND($E$2,D3)-1), FIND($F$2,RIGHT(LEFT(D3, FIND($F$2,D3)),FIND($F$2,D3)-FIND($E$2,D3)-1))-1), 0)
Column G
=IFERROR(LEFT(RIGHT(D3,FIND($G$2,D3)-FIND($F$2,D3)),FIND($G$2,RIGHT(D3,FIND($G$2,D3)-FIND($F$2,D3)))-1), 0)
Column H
=TEXT(E3&":"&F3&":"&TRIM(G3),"hh:mm:ss")
Hope that helps someone.
