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:

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.

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!