Today I was trying to download All Pages or Channels reports from Google Analytics as an Excel spreadsheet, I noticed that avg. time on page and avg. session duration time data look a little different like a number. This is just because after I downloaded report as excel, these time metrics data are formatted as numbers showing the number of seconds, rather than the hh:mm:ss format. I came to a solution where I have convert that data into time.
- Insert a new column in your sheet.
- Add the formula, =TEXT(TIME(,,D2),"hh:mm:ss". The TIME function converts your time elapsed in seconds to a portion of a day.
- You can also do this by this formula. =D2/60/60/24 , and change it into time form numbers.
- Drag and drop on all column values.
- Copy and paste it as a values.
That's all !!
Method 1: =TEXT(TIME(,,D2),"hh:mm:ss"
Method 2: =D2/60/60/24
