PMG Digital Made for Humans

Two Quick Ways to Correct Google Analytics Dates in Excel

3 MINUTE READ | February 2, 2017

Two Quick Ways to Correct Google Analytics Dates in Excel

Author's headshot

Christian Buckler

Christian Buckler has written this article. More details coming soon.

If you spend a lot of time working with Google Analytics, you’ve probably had to deal with a column of “dates” that look like this:

Google Analytics Date Formatting

And if you’re like most people, you tried to change the format of the affected cells from “General” to “Date”… to no avail. Cue frustration.

In this post, I will not tell you why that date format makes sense to GA, but I will show you two easy ways to change this format to one the rest of us can understand.

The main reason Excel can’t automatically convert Google Analytics’s “yyyymmdd” format to a “mm/dd/yyyy” format is because Excel can’t tell that those eight digits are the pieces of a date — to Excel, it’s just an eight-digit number.

However, we can use a setting on Excel’s “Text to Columns” tool to instruct Excel how these numbers are formatted and reformat them as a “mm/dd/yyyy” date. To begin, highlight the cells you would like to change and click Data > Text to Columns (or shortcut ALT + A + E):


At this point, you can select “Delimited” or “Fixed Width.” Since the output won’t actually be pushing values into other columns, neither option has a real impact.


On Step 2, I recommend deselecting all delimiters to prevent any headache when using Text to Columns at a later time. Otherwise, nothing to do here — skip to Step 3!

Step 3 is where the actual cell re-formatting takes place. Click “Date” and change the drop-down to read “YMD” (for “YearMonthDay,” another way of saying the “yyyymmdd” format GA gives us). Then click finish and admire your work!


Now that these cells are formatted like dates, they can act like dates too: you can sort them from oldest to newest, use a WEEKDAY formula to identify which dates are Saturdays, etc.

However, the major limitation of this method is that we can only use it on a specific group of cells. If we want to add more data to the set, we’ll have to replicate the process over again. Suddenly, not quite as quick and painless. For situations like that, we can use a formula that, once set up, we can continue using indefinitely.

This Excel formula will achieve the same result (turning the eight-digit number into a date that Excel can understand and work with) via a nested formula:

= DATE(LEFT(cell reference, 4), MID((cell reference,_5,2),RIGHT(_cell reference,2))


This can be understood in layperson terms by breaking down each part of the formula in context. The DATE function asks for three pieces to assemble a date, in the order Year,Month,Day.

  • For Year, we use the LEFT function to select the first four digits: 20170104

  • Then for Month, we use the MID function to start on the 5th digit (20170104) and select two digits: 20170104

  • Finally, for Day we bring in the Right function to select two digits in from the right end of the string (counting right-to-left this time): 20170104

After hitting enter to finish the formula, you may see an odd result if the cell format is set to Text or Number. To resolve this, change the cell format to Date and you should be good to go!


It’s a little cumbersome having two “Date” columns in a data set, but this formula is handy to have in your back pocket for a quick, replicable means of converting GA date codes into workable Excel date cells.

Stay in touch

Bringing news to you

Subscribe to our newsletter

By clicking and subscribing, you agree to our Terms of Service and Privacy Policy

These two methods are helpful in quick instances where Excel has to be the “duct tape” for executing quick data functions outside a formal SQL-fueled data visualization environment. Hopefully they’ll come in handy in your Excel toolbox!

Related Content

thumbnail image

AlliCampaigns & Client WorkData & TechnologyStrategyDigital MarketingCompany News

PMG’s Predictive Dashboard Wins Innovation Award

1 MINUTE READ | September 28, 2021

thumbnail image

Consumer TrendsSocial MediaPlatforms & MediaDigital Marketing

What You Need to Know About Facebook’s Latest Content Transparency Reports

4 MINUTES READ | August 30, 2021

thumbnail image

Consumer Trends

The Road to Recovery for the Travel Industry

5 MINUTES READ | November 19, 2020

thumbnail image

EMEA Search Trends Amid COVID-19

8 MINUTES READ | April 28, 2020

thumbnail image

A Permanent Shift Into Retail Media

1 MINUTE READ | April 23, 2020

thumbnail image

Social eCommerce is The Darling of Cyber Weekend

4 MINUTES READ | December 2, 2019

thumbnail image

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019