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!
RandomRED“> Posts
Loading…
value=”YToxOntzOjc6InBvc3RfaWQiO2k6MjMzO30=” />
Loading ...
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:
=INT(A1/60)&”:”&IF(MOD(A1,60)<10,”0″&MOD(A1,60),MOD(A1,60))
Rate This Comment:
0 
0
Actually this works even better…
=INT(A1/60)&”:”& IF(MOD(A1,60)<10,”0″&TRUNC(MOD(A1,60),0),TRUNC(MOD(A1,60),0))
Gets rid of those nasty dangling pieces of seconds.
Rate This Comment:
0 
0
ROUND could also be used in place of TRUNC
Rate This Comment:
0 
0
Actually I was totally wrong. The “correct way” to do this is to
1. convert the seconds into fractions of days (Excel thinks of time spans as fractions of a day… goodness knows why)
2. format the column to display mm:ss
=A1/(24*60*60) (Converting seconds into days)
Rate This Comment:
1 
0
the “format cell” solution that you have enlightened everyone with, is definitely more intelligent, even for it’s lack of complexity, than the clever function that was originally supplied. Dude, the simplicity made my day.
Rate This Comment:
0 
0
Thanks for the tips. I’ll try it next month on my report
Rate This Comment:
0 
0
Excellent, Fooo. Simplicity is beauty. You da man!
Q: How does all this relate to misanthropy?
Rate This Comment:
0 
0
Thanks. I just thought it might be helpful to others— the misanthropy part is that nobody else had made a page like this so i had to do it.
Rate This Comment:
0 
0
Can I back you guys up a piece.
If I want to add or subtract a column of minutes & seconds, how do I enter the data and SUM the product?
Does this formula do what I want?
thanks
Rate This Comment:
0 
0
Thank you — this worked for me! !#$%^&* Google Analytics…
Rate This Comment:
0 
0
thanx ill use it in my report for next month I think it’s realy works
Rate This Comment:
0 
0
What is the formula for converting minutes to seconds in excel?
Rate This Comment:
0 
0
In response to this comment:
Actually I was totally wrong. The “correct way” to do this is to
1. convert the seconds into fractions of days (Excel thinks of time spans as fractions of a day… goodness knows why)
2. format the column to display mm:ss
=A1/(24*60*60) (Converting seconds into days)
It has an extra unnecessary *60.
Just do A1/(24*60), and format by mm:ss, and it works. Hurrah!!! Thank you so much for this!
Rate This Comment:
0 
0
A year later and it’s still relevant. You are a god. Thank you so much.
Rate This Comment:
0 
0
erika’s last post works!
Rate This Comment:
0 
0
Make sure you copy and paste the quotes correctly
or you’ll get an error – This is still useful great post!
=INT(A1/60)&”:”& IF(MOD(A1,60)<10,"0"&ROUND(MOD(A1,60),0),ROUND(MOD(A1,60),0))
Rate This Comment:
0 
0