››

How to Show Different Time Formats in Downloaded Quiz Reports

When you download reports, to make them more readable, we report the time a user takes to complete the quiz in a readable format such as "10 min 30 sec". If you need to translate this into just seconds for specific analysis, here is the Excel formula that will translate the "nn hrs nn min nn sec" into a numeric field with the number of seconds.

LOOKUP(9.9E+307,86400*RIGHT("00:00:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F3," ",""),"hrs",":"),"min",":"),"sec",""),ROW(INDIRECT("1:"&LEN(F3)))))

Follow these steps:

Step 1: Download the report data

Step 2: Use the above formula to translate time taken to seconds.

Note: You would just need to change the two references to F3 to the correct Excel column.

Below is a sample excel sheet contributed by our user John R. which has the concerned data in the F3 column. You can view the translated time (number of seconds) using the given formula in the adjacent column.

Attachment (s)

Sample Time Formula.xls

Related articles:

Was this helpful?

Thank you Your feedback helps us to continually improve our content.