Excel Hell: Convert Seconds To Minutes and Seconds In Excel (Esp for Google Analytics)
Let’s say you want to export your Google Analytics referring sites numbers to a CSV file. Ok everything else shows up properly with a little easy column formatting (percentage, etc) but what in god’s good name happened to Time On Site?
They turn it into seconds for you, since you know, we all have an immediate working knowledge of how long 479 seconds is.
If you have an easier way, especially a shorter way that you can do inside the column of origin that would be better. But if not, use this:
Open a new column and then =INT(A1/60)&”:”&INT(MOD(A1,60))
Some Explanation:
=(equals, obv)
INT=Integer
A1 your first cell
/60 divided by 60
& means add this in the middle
“:” after the ampersand the quotes will put anything in between, for this we use the colon. You could use “mins” and then after MOD use “secs” so that it would read 3mins57secs, if that was helpful for some reason.
INT Integer again
MOD Returns the remainder after a number is divided, we need this because we’re going to add any remainder to the decimalized figure and then divide by 60.
After this, insert a new column where Time On Site should be (in b/w PPV and %NV) and then paste your Time On Site column there, then hide the column labeled TOS.
Hope this is helpful to someone who is abused by Google Analytics and Excel on a daily basis.
Hey Google, why not add this or a better formula to your CSV files you cheapskates!
Related posts:
wim
Thanks for the formula. It needs a small correction: If the resulting seconds are less than 10, your version gives an incorrect seconds value. Also, the INT on the MOD value is not strictly necessary, as the MOD function returns an INT. Use this formula to account for seconds less than 10:
July 11th, 2008 at 7:01 pm=INT(A1/60)&”:”&IF(MOD(A1,60)<10,”0″&MOD(A1,60),MOD(A1,60))