Analytics
6.3K members online now
Understand information in your reports and troubleshoot reporting issues such as self-referrals, (not set) data, and inaccurate information
 
Guide Me
star_border
Reply

Google Analytics Exported Report Time Format

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am having a difficult time figuring out the Average Duration Time Format that shows up on the Exported Google Analytics's Report on Microsoft Excel:

 

So on the Google Analytics website, Average Duration Spent (Minutes): 4:30

Excel Sheet shows: 27012.7

 

How do I convert it to the Minutes/Seconds format? PLEASE HELP 

1 Expert replyverified_user

Re: Google Analytics Exported Report Time Format

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆
Is it feasible to share the XL report and corresponding GA report for comparison.

Re: Google Analytics Exported Report Time Format

Rising Star
# 3
Rising Star
change the fields format to h:mm:ss it can be found under custom.

Re: Google Analytics Exported Report Time Format

Rising Star
# 4
Rising Star
Saurabh,

When you export to excel you need to format the time field as follows the number that GA gives you/86400 then format this field to h:mm:ss format by right clicking and chosing custom and the scroll down until you see h:mm:ss this will convert the 27012.7 to 0:04:30. Note it is best to do this in a new column and then copy and paste that column over itself and choose save as values then you can cut that column and paste it over your origional column and all your data will be in the proper format.

Google Analytics Exported Report Time Format

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

If I understand you correctly, when I format the Avg Time field to custom h:mm:ss near the bottom of the custom formatting section of that part of Excel, it turns all of the values to 0:00:00.  It doesn't matter what GA's average time is, the value Excel converts it to is 0:00:00.  What am I missing?

Re: Google Analytics Exported Report Time Format

Rising Star
# 6
Rising Star

In a new column within Excel take your existing column and divided it by 86400 then change its format to hh:mm:ss (example A2 contains your Average Time on Page in column say F2 type the following =A2/86400 then change column F's format to hh:mm:ss) this should give you a correct representation of the time.