Avg Time Formulas

Options
Stu5677
Stu5677 Member
edited July 7 in Calculations & Variables (Beast Mode)

I am having lots of trouble trying to figure this conversion out.. I have an excel file that I am bringing into domo.. within the card (table format) I am trying to get the average (as the average column already existing cannot be properly summed within domo). I am trying to take the 'talk time' divided by 'call id count' to get the 'average' which should match the 'average TALK TIME' but the new column 'avg talk time' is not working as expected..

image.png

the beast mode for the 'avg talk time' is :

ROUND(
(
(HOUR(TALK TIME) * 3600) +
(MINUTE(TALK TIME) * 60) +
SECOND(TALK TIME)
) / CALL ID count
)

Anyone deal with this issue or know a solution?

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Stu5677 Since your TALK TIME column is already in a time format, I'd suggest using the TIME_TO_SEC function to convert that duration into seconds, divide the number of seconds by the CALL ID count, then use the SEC_TO_TIME function to convert those seconds back into a time format like this:

    SEC_TO_TIME(TIME_TO_SEC(`TALK TIME`)/`CALL ID count`)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Stu5677 Since your TALK TIME column is already in a time format, I'd suggest using the TIME_TO_SEC function to convert that duration into seconds, divide the number of seconds by the CALL ID count, then use the SEC_TO_TIME function to convert those seconds back into a time format like this:

    SEC_TO_TIME(TIME_TO_SEC(`TALK TIME`)/`CALL ID count`)
  • Stu5677
    Stu5677 Member

    @MichelleH Thank you! So simple! Do you know if there is a way to change the subtotal format, to add up the time rather than the count of each row? etg. 00:14:01 + 00:11:58+ 00:05:07…

    image.png

Categories