• Download Excel Sample
• Google Sheet Sample

4.33 -> 04:20 Adobe Analytics
179.04 -> 02:59 Google Analytics

When you pull metrics like average time on page from digital analytics tools the number format looks like this: 4.33

I remember scratching my head the first time I saw this in Adobe Analytics (then Omniture). Was the other dot in the colon missing and did this mean 4:33 or 4 minutes 33 seconds? Or did this actually mean 4 minutes 0.33 * 60 seconds (0.33 multiplied by 60) which equals 4 minutes 19.8 seconds?

Average Time on Page in Adobe Analytics Report & Analytics

Drum roll please… it means 4 minutes 0.33 * 60 seconds or 04:20. For those of you who were able to convert 4.33 from an a number with a decimal to time in your head please pat yourself on the back and let me know in the comments. For the rest of us, this post will show how to convert a number with decimal time to minutes and seconds time formatting using Excel and Google Sheets.

This decimal time formatting isn’t unique to Adobe Analytics. Google Analytics also formats time as a number with a decimal when you export data into Excel (XLSX) format or pull time metrics from the Google Analytics API. However, Google Analytics displays time in seconds. So 179.01 seconds is actually (179.01/60 = 2.98) 2 minutes 0.98 * 60 seconds or 2 minutes 59 seconds or 02:59.

Average Time on Page in Google Analytics (XLXS) Excel data export

How to Convert Adobe Analytics Decimal Time to hh:mm:ss

4.33/ (24*60)= 0.00300694444444444

We divide the minutes and seconds decimal time by the number of minutes in a 24 hour period 24*60= 1440 minutes. This Microsoft knowledge base article has more on formatting time in Excel.

The formula will return a serial number which Excel and Google Sheets can apply time formatting to and display time as 00:04:20. Serial numbers are described in more detail later in the post.

4.33 -> 00:04:20

Since most pages that I’ve worked with don’t have an average time on page of more than an hour I would recommend formatting time as mm:ss

4.33 -> 04:20

How to Convert Google Analytics Decimal Time to mm:ss

179.04 / (24*60*60)= 0.002071875

We divide the minutes and seconds decimal time by the number of seconds in a 24 hour period 24*60*60= 86400 seconds.

The formula will return a serial number which Excel and Google Sheets can apply time formatting to and display time as 00:02:59

179.04 -> 00:02:59

Format Time in Excel

To format a serial number to time in Excel first select the cell. In this example click on cell A1 with the serial number value 0.00300694444444444. On the Home Tab click on the number formatting drop down and select More Number Formats.

From the Number Format Category select Time and select the type that displays hh:mm:ss then click OK.

To format as mm:ss since average time on page is going to less than one hour, select Custom from the Number format. Then in Type you can enter mm:ss in the field or if you have already added this Custom number type you can select it from the list. Then click OK.

Format Time in Google Sheets

To format a serial number to time in Google first select the cell. In this example click on cell A1 with the serial number value 0.00300694444444444. In the top nav click on Format > Number > More Formats > Custom number format.

Type in mm:ss in the Custom number format field and click the Apply button.

What the heck is a Serial Number?

For those wondering what the heck a serial number is I will try my best to explain. A serial number represents the number of days, hours, minutes, seconds from 1900 in Windows Excel. So day 1 in a 1900 based date system is 1/1/1900  12:00:00 AM.

Microsoft incorrectly assumed that 1900 was a leap year including 2/29/1900 in the calendar (serial number 60). February 29, 1900 did not exist… When Mac computers first came out they used a 1904 based date system where day 1 was January 1, 1904. These different date systems caused all kinds of compatibly issues and confusion. You can read more on the history of the 1900 vs. 1904 date system here.

Google Sheets Date System: 1900 Variation

So you may be asking what date system does Google Sheets use? Google Sheets’ uses a variation on the Windows Excel 1900 based system where day 1 in Google Sheets is 12/31/1899 12:00:00 AM. Using this method Google Sheets is able to have 28 days in February 1900. The Google Sheets serial numbers meet up with the Windows Excel serial numbers on March 1, 1900 which is serial number 61.