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:
- Create a new column beside the data you want to convert
- Enter the following formula =DataCell/(60*60*24)
- and then format your column or cell to display the data in minutes:seconds
- Open the Format Cells Dialog
- Select Custom
- In the Type: box enter mm:ss
- 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.
Great was looking for this exact information- thanks for posting!
I was having this exact problem. This is great – you’re a lifesaver! Thanks for this
No problem Erana, Glad I could help.
How do i convert mm:ss to ss. e.g 18:15 to seconds
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.
Blimey, a total lifesaver… thanks so much!
thank you….it was very helpful
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.
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.