Let’s say you are using Google Analytics and want to run some statistics against the Average Time Spent (why you may want to do this will be the subject of a forthcoming blog post). First, you’re naturally going to export the data into Excel since GA offers only the basics. Next, you are going to find yourself stuck and staring at one or more columns of hh:mm:ss time.
Why? Unless you convert this time format data into to integers with decimals you will not be able to analyze the data for any revealing statistics. Seems simple enough to do in Excel, just convert the time into a decimal format…but no.
Alas, I was shocked that there is no formula-level function that does essentially the opposite of the CONCATENATE function in Excel. Curiously, it is much easier to covert the decimal back to hh:mm:ss format. Thanks to the many folks that pointed me in the right direction.
- Many folks suggested the Data>>Text-to-Columns function in Excel but this would have blasted away many other columns of data and there were several of these time columns that needed this.
- Others suggested converting the time figure using the format feature; that also didn’t work.
The solution was using the HOUR, MINUTE and SECOND commands in Excel. It worked like this:
01:32:56 (1 Hour , 32 Minutes and 56 Seconds) = 92.93 Minutes
[Yes, you can cut and paste the above formula!]
Pretty neat, eh? However, still haven’t found a way to do this with some kind of delimiter like a “SPLIT” in Perl.
Works perfectly! Thanks!
You can just type in the Cell you want the Decimal value appear in “=A1*60*24” Assuming that A1 is the Cell that has the hh:mm:ss.
and it works perfectly.
the above one also works good and thus “=A1(cell no.)*24*60” also simple and good. thanks
Above one works perfectly thus the other “=A1(cell no.)*24*60” is also works simple and perfect.
Thanks
Thanks! It works perfectly!!!
This works if the time is < 24 hrs
However, I would like to convert 40:00:00 to the real number 2400 (minutes) or 144000 (seconds)
Any ideas?
for that you have to format your time cell in to (h):mm format by custom format then in result cell you put =int(cell)*24+hour(cell)+round(minute(cell)/60,2)
Sarikir, your last post was exactly what I needed to convert the minutes in [h]:mm to decimals (example: 36:45 = 36.75). Thanks for saving me some time figuring out the correct formula!
That didn’t work for what I was looking for. Mine is on a timesheet. For example; Employee A worked 9:30:00 on a given day, I need my next column to say 9.5. Can you help me with that please?
=((HOUR(E4)*3600)+(MINUTE(E4)*60)+(SECOND(E4)))