This website uses cookies to ensure you get the best possible experience. See our Cookies Policy.

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

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):

20170130_GA-Dates_Text-to-Columns-pt1

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.

20170130_GA-Dates_Text-to-Columns-pt1

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!

20170130_GA-Dates_Text-to-Columns-pt1

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))

20170131_GA-Dates_Formula-pt1

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!

20170131_GA-Dates_Formula-pt1

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.

Insights meet inbox

Sign up for weekly articles & resources.

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!


Posted by Christian Buckler

Related Content

thumbnail image

Get Informed

PMG’s Predictive Dashboard Wins Innovation Award

1 MINUTE READ | September 28, 2021

Get Informed

The Road to Recovery for the Travel Industry

5 MINUTES READ | November 19, 2020

thumbnail image

Get Informed

Facebook Details Approach to Mobile Industry Changes

4 MINUTES READ | August 31, 2020

Get Inspired

Considerations for Reengaging New Online Customers

1 MINUTE READ | April 29, 2020

Get Informed

EMEA Search Trends Amid COVID-19

8 MINUTES READ | April 28, 2020

thumbnail image

Get Informed

A Permanent Shift Into Retail Media

1 MINUTE READ | April 23, 2020

Get Informed

Social eCommerce is The Darling of Cyber Weekend

4 MINUTES READ | December 2, 2019

thumbnail image

Get Informed

Reports of Amazon Clean Room Draws Attention of Advertisers

2 MINUTES READ | September 4, 2019

Get Informed

PMG Shares Thoughts About Rumored Amazon Data Clean Room

1 MINUTE READ | August 28, 2019

Get Inspired

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

All POST