Avg Time Formulas

Stu5677

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..

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?

MichelleH

@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`)

MichelleH

@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

@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…