When creating Google Analytics reports in Excel, time metrics like Avg Session Duration are delivered in seconds, but people want to see the number shown in minutes and seconds as “1:52” (instead of as “112” seconds). Attempts to format the number as “Time” in Excel prove frustrating because Excel expects time numbers to be fractions of a day, not seconds.
Doing the 2-Step
The classic approach to this problem is to divide the number by 86,400 (which is 60 seconds/minute * 60 minutes/hour * 24 hours/day), and then format with a custom Time format. Analytics Edge Core Add-in users could add a Calculate function as part of the macro, but Basic Add-in users will need to reference a data cell with an Excel formula to make the conversion.
='Data'!E2/86400
Simplified Reporting
This just seems like a silly extra step, so I added a new conversion option to the Google Analytics connectors (both Free and Pro versions, v1.22 upgrade) that does the math for you – converting Seconds to Days.
Final Formatting
To get the desired format, select the custom format “mm:ss” for the cell.
Save time with Analytics Edge
I design my products to get the job done quickly, so you can focus on your work, not the mechanics of reporting. If you have questions or product suggestions, feel free to reach out. I am here to help.
Mike Sullivan
Founder, Analytics Edge