In Google Analytics, you only have so many dimensions to work with before you hit your limit for your report. One thing you might have noticed in GA is that Google will spit out a long date within Excel that Excel can’t compute with the TEXT command. In this tutorial, I am going to show how to change a long date into a shorter data by using the custom text feature within excel. Once that has been accomplished, you can then use the TEXT command in excel to break out the day of the week, or simply put everything into a pivot table to break out the data into months and quarters. Let’s dive in!
Step 1. Export All Data into Excel
Any report you pull might combine the year, month, and the day into one column. This report could be from Google Analytics if you use a custom report, or from any other source. Anyway, you will see some data that looks like this below. Notice how I can tell that the year is 2016, but everything is just squashed together.
Step 2. Copy the Column You Want to Edit
With the data above, I can see the year is 2016, the month is September (09), and the day is the 6th. This is where we are going to head to data and text to columns section. Before heading over to this section, you need to copy the column that you want to change. For safety, I also do the CTRL + C command to make sure I am only copying this data from excel. This is a good tip to practice in general with excel or you might change your entire document when you only want to change one column.
Step 3. Convert the Text
Head up to the data view and click on the text to columns button. Based on your report, you will either click on delimited or fixed width in your settings. Excel will show you which. For this example, I am going to click on delimited. From there, you want to click on the next button to get to step 2.
For your data, you might notice that the dates are broken out by a tab or a comma. If that is the case, you can click on that box next to that name and excel will show you what will happen if you go ahead with this task. For this example though, I don’t have anything special that breaks up the numbers in excel. Since that is the case, I am going to click on the next button to get to step 3.
Step 4. Chose a Date Format in the Convert Text Feature
This is where you can change these numbers into a date. Simply click on the date box and choose the settings you want to change. For this example, I wanted to see the settings set to the year, the month, and the day.
This is the Before I hit the Finish Button
This is After I hit the Finish Button
Step 5. Use the TEXT Commands
With this broken out in excel, I can do a few things. First, I can take the data and put it into a pivot table. The beauty of this is that I can then combine my dates into things like quarters, months, and years if I have a lot of data. However, I can call out what each value is in the cell and reference the real month and day of week as well. The reason you might want to do this is if you work for a company that have a different business calendar compared to a traditional calendar.
To get started with this, you need to go to an empty cell and use the TEXT command in excel.
=TEXT(“cell”,dddd) –> Day of the Week
=TEXT(“cell”,mmmm) –> Month
=TEXT(“cell”,”yyyy”)–> The Year
You can even take off an extra letter in the formulas above to abbreviate the day of the week, the month, and the year if you would like.
If this is a repeated task for work or a hobby, I urge you to record the steps above in a macro in excel to save time. The macro can run by changing the date in the column to an easier number for excel to understand, and then run the TEXT commands that I highlighted above. The TEXT formula in excel will not understand the long data in the example from the start, so you have to break this out for excel.