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:

Pretty neat, eh? However, still haven’t found a way to do this with some kind of delimiter like a “SPLIT” in Perl.