How To Convert Google Analytics’ Funky Month of Year Format In Excel

convert Google Analytics date formats in Excel

Whoever engineered the Date format for Google Analytics’ latest version has probably never actually tried to chart it out inExcel . Today’s date would show up as 20121118. And, of course, since that’s not a valid date format, Excel interprets it as text.

Formula To Use

To convert this to a date format Excel recognizes I wrote a function that uses the DATE, LEFT, MID, and RIGHT functions. You can see how it looks here:

converting Google Analytics date format in Excel

Click for larger image.

 

Or you can copy and paste this and replace H7 with the cell address that contains the first date in your column:

=DATE(LEFT(H7,4),MID(H7,5,2),RIGHT(H7,2))

Breaking It Down

Date: The DATE function follows this format: DATE(year,month,day).
Year: This says, “Go into H7 and grab the four left-most characters.”
Month: This says, “Go into H7 and grab the middle two characters. Oh, and it starts at the fifth character. No need to thank me. Just trying to be helpful.”
Day: This says, “Go into H7 and grab the two right-most characters.”

To copy the formula down the column just double-click on the bottom-right corner of the cell containing the formula (in my case K7) or grab the fill handle (same bottom-right corner) and drag it down the column.

Wait Just A Second … Where’s The Year?

Very observant! Yes, there’s no year in any of the dates in Column K. You can decide exactly how you want your dates to show in cell formatting options. Learn more in this post on custom date formatting in Excel.

Google Analytics Has A Date Dimension???

Yep. To use access it, you need to use a custom report or the GA API. It’s not built in to the standard reports. You can see where to find it when building a custom report in this screenshot:

date dimension in Google Analytics

Click for larger image.

 

Photo by Matthew Kenwrick.

 

  • disqus_7PmucBWuki

    Very helpful. Thank you so much. Need more practice on this. :)

    • http://www.annielytics.com/ Annie Cushing

      My pleasure!

  • AMc2010

    I’d been tearing my hair out with this issue and a custom report – thanks for sharing!

    • http://www.annielytics.com/ Annie Cushing

      Happy to help! :)