Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Nov 19 2012

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

convert Google Analytics date formats in Excel

UPDATE: Since I’ve had a couple questions about how to display the date as just the month and year, I created a Google Sheet demonstrating several fixes. You can view it here.

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.

Learn More

You can learn more about data formatting in my Annielytics Dashboard Course, a video course that will teach you how to put your data in stilettos and work the pole. 🙂

Photo by Matthew Kenwrick.

~~~

If you would like to learn more about Excel, check out my Excel dashboard course. 24 instructional videos, totaling 6+ hours of instruction for $95.

buy-now-button

Written by Annie · Categorized: Excel

Comments

  1. disqus_7PmucBWuki says

    May 20, 2013 at 10:38 AM

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

    Reply
    • Annie Cushing says

      May 20, 2013 at 11:55 AM

      My pleasure!

      Reply
  2. AMc2010 says

    January 14, 2014 at 8:47 AM

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

    Reply
    • Annie Cushing says

      January 14, 2014 at 12:15 PM

      Happy to help! 🙂

      Reply
  3. Andrew Teoh says

    July 8, 2014 at 10:07 AM

    Thanks for the help!

    Reply
    • Annie Cushing says

      July 8, 2014 at 10:33 AM

      You’re welcome!

      Reply
  4. Philip Tomlinson says

    August 14, 2014 at 3:05 PM

    I found this article when trying to do the opposite.

    For those trying to convert regular dates into GA’s format for importing Cost Data manually, this is the formula to transform a regular date into the right format for uploads:

    =TEXT(A2,”yyyymmdd”)

    A2 is the actual date

    Reply
    • Annie Cushing says

      August 14, 2014 at 3:17 PM

      Great point, Philip. Thanks!

      Reply
      • Philip Tomlinson says

        August 14, 2014 at 4:44 PM

        It’s just annoying when some report exports use “Month-Date-Year” as it doesn’t seem to be picked up as a date.

        Reply
        • Annie Cushing says

          August 15, 2014 at 4:26 PM

          If a date uses hyphens or forward slashes, Excel will recognize it as a date.

          Reply
  5. Brian says

    August 7, 2015 at 11:18 AM

    100% helpful! My old formula was not as efficient.

    Thanks!

    Reply
    • Annie Cushing says

      August 7, 2015 at 12:23 PM

      Happy to help! 🙂

      Reply
  6. Jeroen says

    January 5, 2017 at 8:50 AM

    Hi Annie,
    great article and (unfortunately) still valid today.

    Here’s my challenge:
    I cannot use the Date dimensions because of the API limitations.
    In my case Month of Year returns 201501 (yyyymm) – I don’t have and don’t need to have the day.
    Is there a formula I can use so I end up with 01/2015 ?

    Thanks!

    Reply
    • Annie Cushing says

      January 5, 2017 at 12:41 PM

      Hi Jeroen!

      Absolutely! I’m a little crushed for time to set this up right now, but if you send me a spreadsheet with some dates (or a link to a Google Spreadsheet) I’ll be happy to set it up for you and leave a note of how I did it! annie(at)annielytics(dot)com

      Reply
      • Andrea says

        November 1, 2017 at 10:48 AM

        Having the same issue here. Any help?

        Tks

        Reply
        • Annie Cushing says

          November 3, 2017 at 12:31 PM

          I demonstrate the fix in this Google Sheet: http://bit.ly/ga-you-wildin. Let me know if you need any further help!

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2021 annielytics.com