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!

The Best How-To Videos Of 2008

You might...

Post Metadata

Date

June 26th, 2008

Author

andyfox1979

9 Comments

wim

1

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))
July 11th, 2008 at 7:01 pm
fooo

2

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.
September 12th, 2008 at 8:19 pm
fooo

3

ROUND could also be used in place of TRUNC
September 12th, 2008 at 8:20 pm
fooo

4

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)
September 12th, 2008 at 8:35 pm
andyfox1979

5

Thanks for the tips. I’ll try it next month on my report
September 12th, 2008 at 10:12 pm
InteMarket

6

Excellent, Fooo. Simplicity is beauty. You da man!

Q: How does all this relate to misanthropy?
October 16th, 2008 at 11:56 am
andyfox1979

7

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.
October 19th, 2008 at 3:32 pm
vincelaxton

8

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
November 17th, 2008 at 2:29 pm
cat pickett

9

Thank you — this worked for me! !#$%^&* Google Analytics…
November 18th, 2008 at 2:03 pm