August 13, 2009

## How Do I Convert Seconds to Minutes in Excel?

More articles by »
Written by: Ivan Printis
Tags: , , ,

I run across this problem all of the time when I am exporting data out of Google Analytics. I thought it would be a great idea if I finally documented it somewhere I would remember and in the process help you out along the way.

For those of you that don’t know, whenever you export data out of Google Analytics that contains for example, the average time a user is on the site, Google only provides you with the seconds.

The quick and easy way for me has been the following:

1. Create a new column beside the data you want to convert
2. Enter the following formula =DataCell/(60*60*24)
3. and then format your column or cell to display the data in minutes:seconds
4. Open the Format Cells Dialog
5. Select Custom
6. In the Type: box enter mm:ss
7. Voila! You are done. If you follow these steps it should convert the seconds to minutes:seconds.

If you were wondering about the formula the creative people over at Microsoft decided that it was an excellent idea to store all dates as integers and all times as decimal fractions.  So for example in Excel a period of one day or a 24 hour period looks sort of like this:

1 = 1 day = 1 24 hour period = 1 24 hour period * 60 minutes * 60 seconds * 24 hours = Total Seconds

Remember that time is stored as a decimal between .0 and .99999, where .0 = 00:00:00 and .99999 = 23:59:59. Anyway, I hope this helps you out the next time you need to convert seconds to minutes in Excel.

Ivan Printis

## 5 Simple Ways To Decrease Your Site Bounce Rate

Unless you’re a brand new marketer or you’ve been underground for the past 10 years, you should know exactly what a bounce rate is. You should also know that the higher your bounce rate, the less money you’ll make. Your b...

1. Steve

Great was looking for this exact information- thanks for posting!

2. Erana

I was having this exact problem. This is great – you’re a lifesaver! Thanks for this

• Think.Point.Click.

No problem Erana, Glad I could help.

3. siva naidoo

How do i convert mm:ss to ss. e.g 18:15 to seconds

• Think.Point.Click.

Hi Siva, What you will want to do is enter your time in Excel like this 00:18:15 with the custom format mm:ss. In the cell where you want to calculate seconds enter =DataCell*86400 and format the cell as a number not time. If everything is working correctly you should end up with 1095 seconds. Let me know if you run into any trouble.

4. Sara

Blimey, a total lifesaver… thanks so much!

5. bijoy

6. Mark O'C

Fantastic. Clear and concise instructions. Worked easily. After I had racked my brains for so long I finally found this – could have done it in minutes. Well done and thank you.

• Ivan Printis

I’m glad I could help. This drove me crazy when I ran into the problem originally. I kept thinking to myself there has got to be an easier way to do this. Good to see I saved you a little frustration. Hopefully Microsoft did not drive you too crazy.

7. Tina

Thanks… It was of great help… I was searching for this….

8. Miekoo

How do I convert seconds into hh:mm:ss format in a Pivot Table after grabbing from a static (read only) table? When I format row label for seconds I get “#####” or 0:00:00 instead of Minute Number format.

How can I do this without coping the data to a new table and inserting more rows to reformat them prior to doing the pivot table?

9. T0ny

That sucked badly. It didn’t work for me. I’m an excel newbie.

10. Achim B

Your explanation was very useful, I’m glad I found it. Actually it’s pretty simple once you understand the logic. Thank you.

11. Joel

Awesome!!! Just what I was looking for and simple. It is people like you that give the words ‘helping hand’ merit.

12. Barry

FYI, you can use the TEXT() function in Excel to format it instead of having to find it in the list. Just use =TEXT(value,”mm:ss”) and voila! Much faster.

The Latest