Google Sheets & Google Data Studio Timezone Conversion

Some interesting facts about working with time zones

  1. By default, Google Sheets uses the time zone set for your computer. So any date and/or time data that you enter into Google Sheets would be considered to be in the time zone set for your computer.
  2. You can find out what time zone your Google Sheets is using. 
  3. You can change the date format and time zone in Google Sheets.
  4. You can change the date format and time zone across all Google Sheets.
  5. Supermetrics uses the time zone set in your data platform.
  6. Google Data Studio uses the time zone set in your data platform.
  7. All time zones are calculated based on GMT / UTC

How to find out what time zone Google Sheets is using

You can find out what time zone your Google Sheets is using via the ‘now()’ function. 

This function returns the current date and time:

google sheets timezone conversion

Here the time is displayed in the GMT time zone because that’s the time zone set for my computer:

timezone set for my computer

Here the date is formatted as dd/mm/yyyy because the location for my computer is set to ‘United Kingdom’. 

Google Sheets picks up location from your computer settings:

country or region settings

Get the ebook on Google Data Studio (50+ Pages)Get the FREE ebook on Google Data Studio (50+ Pages)

Changing the date format and time zone in Google Sheets

If you want to change the way a date is displayed in your Google Spreadsheet, maybe you want the date to display in American date format (where the month comes first, followed by day and year) then click on the ‘Spreadsheet Settings’ under the ‘File’ menu:

Google Spreadsheet Settings

And then change the location by selecting ‘United States’  from the ‘Locale’ drop-down menu:

Locale drop down menu

Now the current date and time in the spreadsheet would like the one below:

current date and time in the spreadsheet

The date is now displayed in American format (where the month comes first, followed by day and year).

However, the time is still displayed in GMT time zone.

In order to change the time zone to say EST, click on the ‘Spreadsheet Settings’ again (under the ‘File’ menu) and then select ‘(GMT-05:00) Eastern Time’ from the ‘Time zone’ drop-down menu:

Time Zone drop down menu

Now the current date and time in the spreadsheet would like the one below:

current date and time in the spreadsheet 2

The date is still in the American date format but now the time is displayed in EST time zone.

Changing the date format and time zone across all Google Sheets

When you use the ‘Spreadsheet Settings’ (under the ‘File’ menu) to change the date format and time zone, it works only on the individual sheet. These changes won’t work across all Google Sheets. 

So if later you create a new Google Sheet then the date will display according to the location set for your computer and time will be displayed in the time zone set for your computer.

If you want to change the date format and time zone settings across all Google Sheets then you would need to change the location and time zone settings for your computer. 

Changing the time zone used by Supermetrics

Supermetrics uses the time zone set in your data platform

So if you are pulling data from Google Analytics via the Supermetrics Google Sheets Add-on or scheduling data refreshes then Supermetrics will use the time zone set for the reporting view in GA. 

If you want to change the time zone used by Supermetrics then you would need to change the time zone settings for the reporting view:

timezone settings for the reporting view

Similarly, if you are pulling data from any other data platform, say Google Ads, via the Supermetrics Google Sheets Add-on or scheduling data refreshes then Supermetrics will use the time zone set for your Google Ads account. 

So you need to make sure that you pull data from the data platforms which all use the same time zone. 

What that means same time zone settings for Google Analytics, Google Ads, Facebook ads, Google Sheets, etc. 

Otherwise, you may see a lot of discrepancies when charting data for the current day or previous day in Data Studio.

Changing the time zone used by Google Data Studio

Google Data Studio uses the time zone set in your data platform

So if you are pulling data directly from Google Analytics into Data Studio then Data Studio will use the time zone set for the reporting view in GA. 

So if you want to change the time zone used by Data Studio then you would need to change the time zone settings for the GA reporting view.

If you are pulling data from Google Sheets into Data Studio then the data studio will use the time zone set for the Google Spreadsheet.

Similarly, if you are pulling data from any other data platform (like Google Ads) into Data Studio then Data Studio will use the time zone set for your Google Ads account. 

There is currently no option to change the time zone for reports from within Data Studio. 

How time zones are calculated worldwide

Since time zone was invented by a British inventor, all time zones are calculated based on GMT (Greenwich Mean Time) which you can say is like London’s local time (as Greenwich is a district in London, UK). 

Your local time zone is either ahead or behind GMT. 

For example, EST (Eastern Standard Time) is 5 hours behind GMT. That’s why EST is represented as ‘GMT – 05:00’. 

Similarly, PST (Pacific Standard Time) is 8 hours behind GMT. That’s why PST is represented as ‘GMT – 08:00’. 

UTC (Universal Coordinated Time) is not a time zone but a time standard. But for all intent and purposes, it is just another name for GMT. 

There is no time difference between UTC and GMT. So for example, EST can be represented as ‘GMT – 05:00’ or ‘UTC – 05:00’.

What that means if you live in the UK and your clients are also from the UK then during the winter months (when the time zone is GMT), you don’t need to worry about time zone differences in Google Data Studio. 

Avoid charting data for the current day in Data Studio

By default, Google Data Studio uses UTC standard time

If your data source doesn’t use UTC, you may see discrepancies when charting data for the current day (due to differences between UTC and your time zone).

Such discrepancies increase significantly when you live farthest from London (like in Australia, New Zealand, etc.) where the time difference between UTC and your time zone is pretty big.

So if you live in Australia and you pull the data for the current day (from the data source which uses a different time zone say ‘EST’) in data studio, it may not show you any data for ‘today’. 

Moreover, I don’t see any real benefit of analyzing an incomplete data set. 

So avoid charting data for the current day in Data Studio.

How to convert your time zone into UTC in Google Sheets

Google Sheets does not provide any function out of the box to convert a time zone into UTC.

So if you like, you can use my tool called the ‘OptimizeSmart – UTC Time Converter’

It is a Google Sheet that is tied to scripts that converts any time zone into UTC.

Note: This time zone converter is based on the scripts and instructions provided by David of https://davidkeen.com/blog/2017/01/time-zone-conversion-in-google-sheets/ . I just made a few changes to it.

In order to use this time converter, follow the steps below:

Step-1: Click on the Google Sheet OptimizeSmart – UTC Time Converter

OptimizeSmart UTC Time Converter

Step-2: Click on the ‘File’ menu and then click on ‘Make a Copy’:

Make a Copy

Step-3: Find your time zone name from the TZ Database: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

For example, the TZ database time zone name for EST (Eastern Standard Time) is America/New_York

Similarly, the TZ database time zone name for PST (Pacific Standard Time) is America/Los_Angeles

Step-4: Delete the existing data under the two columns: “Your local date and time” and “Your TZ database time zone name“:

Your TZ database time zone name

Note: Do not delete the data in the third column ‘UTC Time’. This column contains the cells which contain the formula that converts your time zone into UTC.

Here is how that formula looks like:

Here is how that formula looks like

Step-5: Add your own data under the two columns: “Your local date and time” and “Your TZ database time zone name“:

Add your own data under the two columns

Step-6 (optional): Copy the formula by dragging down the fill handle (the plus sign that appears at the lower right corner of a cell):

Copy the formula by dragging down the fill handle

Step-7: Copy the converted time zones from the ‘UTC Time’ column and paste them into the Google Sheets where you want to use them. 

Alternatively, connect this sheet to your data source schema via the ‘Google Sheets’ connector.

How to report a user-specific time zone in Google Data Studio using Google Analytics and Google Tag Manager

As you know, Google Analytics reports the user timing as per the time zone set in the admin section. But if you want to understand user experience for the international audience, then it becomes a little more tricky. However, this can be overcome by capturing user-specific time in Google Analytics. This process involves following steps

  1. Creating hit scope custom dimension
  2. Adding data source parameter
  3. Adding calculated fields
  4. Creating a dashboard

Now let’s see each of these steps in more detail.

Creating hit scope custom dimension

The very first step is to create a custom dimension in Google Analytics with hit scope that will capture the user-specific time.

Follow the below steps to set create and set up this custom dimension

Step-1: Navigate to the admin section of Google Analytics and under the ‘Property’ column click on ‘Custom Dimension’.

custom dimensions

Step-2: Now create a new custom dimension with hit scope and name it ‘User-specific date and time’.

create custom dimension

Step-3: You will get a screen like below, note down the dimension index number, and click on ‘Done’.

done

Step-4: Now navigate to your Google Tag Manager account and then click on variables.

GTM Variables

Step-5: Create a new user-defined variable of type ‘Custom JavaScript’ and name it ‘User-specific date and time’.

custom variable

Step-6: Now add the below lines of code to the variable and click on ‘Save’.

function()

{

  var currDate = new Date();

  function pad(number) {

    if ( number < 10 ) {return '0' + number;}

    return number;

  }

  return currDate.getFullYear()

         + "/" + pad(currDate.getMonth()+1)

         + "/" + pad(currDate.getDate())

         + " " + pad(currDate.getHours())

         + ":" + pad(currDate.getMinutes()) 

}

code and save

Step-7: Now open your Google Analytics settings variable and, under ‘More settings’, map the recently created variable to the custom dimension index.

update analytics settings variable

Step-8: Now Preview the GTM container and check if the dimension contains the user-specific time or not.

preview

As you can see the dimension contains the user-specific time and date information. Now you can publish the GTM container.

Now you need to wait for a couple of days to generate the data. Then you can move on to the next step.

Adding data source parameter

Now let’s add a data source parameter to switch between application time (Google Analytics time zone) and user-specific time. This configuration is done in Google Data Studio. If you are new to data studio, you can read more about it here Google Data Studio Tutorial with FREE PDF ebook

Follow the below steps to add the required parameter.

Step-1: Navigate to Google Data Studio and under the ‘Resources’ menu, click on ‘Manage added data sources’.

manage datasource

Step-2: Click on ‘Edit’.

edit source

Step-3: Now click on ‘Add parameter’.

app parameter

Step-4: Now give the parameter the name ‘Time repository’ then select the radio button ‘List of Values’ and add ‘Application Time’ and ‘User Specific Time’, like below.

parameter configuration

Step-5: Now click on ‘Save’.

Adding calculated fields

In this step, we are going to create calculated fields in Google Data Studio to map them with the ‘Time repository’ parameter. I am going to use ‘UST’ as a prefix for ‘User-specific time’ and ‘AST’ for ‘Application specific time’.

Follow the below step to create the calculated fields.

Step-1: Click on ‘Add Field’.

add field

Step-2: Add the field name as ‘UST- Date and Time’ and then in the formula paste the below code:

“PARSE_DATETIME('%Y/%m/%d %H:%M', User specific date and time)”

Here, the user specific date and time is the exact name of the custom dimension that we created in Google Analytics.

1st field

Now click on ‘Save’.

Step-3: Now create another field with the name ‘UST – Day of week’ and then in the formula paste the below code:

REPLACE(FORMAT_DATETIME('%w-%A', UST – Date and time),'0','7')

Here UST – Date and time is the name of the first custom field that we created.

2nd field

Now save the field.

Step-4: Now create a third field with the name ‘AST – Day of week’ and then in the formula paste the below code.

REPLACE(FORMAT_DATETIME('%w-%A', Date),'0','7')

3rd field

Save the field.

Step-4: Now create the fourth field with the name ‘UST – Hour’ and then in the formula paste the below code

CAST (FORMAT_DATETIME('%H', UST – Date and time) AS NUMBER)

Here UST – Date and time is the name of the first custom field that we created.

4th field

Now save the field.

Step-5: Now lets we need to create a wrapper inside a calculated dimension so that we can use the parameters. This is done by adding our next field as below

 “UST – Time repository – dim wrp”

  • Time repository

5th field

Step-6: Now let’s create the sixth calculated field and name it ‘UST – Selected day of week’ and paste the following code in the formula box.

CASE 

WHEN UST Time repository dim wrp = 'User Specific Time' THEN UST – Day of week

WHEN UST Time repository dim wrp = 'Application Time' THEN AST – Day of week

end

6th field

Step-7: Now we need to add our last field as ‘UST – Selected hour’ and paste the following code in the formula box.

CASE

WHEN ust_time_repository_dim_wrp = 'User Specific Time' THEN UST – Hour

WHEN ust_time_repository_dim_wrp = 'Application Time' THEN Hour

end

7th field

We are done with the data source configuration now.

Creating a dashboard

Now let’s create a dashboard with the below steps.

Step-1: Click on ‘Add a control’ and select ‘Drop-down list’.

Add dropdown

Step-2: Select ‘Time repository’ as the control field.

select control field

Step-3: Add another drop-down list and select the control field as ‘continents’.

2nd dropdown

Step-4: Now insert a ‘Time series’ chart and use ‘UST – Selected hour’ as the dimension then select any metric such as pageviews.

time series chart

teime series chart metric

Step-5: Now add another chart as ‘Pivot’ and use ‘UST – Selected hour’ as the row dimension and ‘UST – Selected day of the week’ as the column dimension, then add your metric.

pivot table

pivot metrics

For all the above steps you can customize your style based on requirements.

Step-6: Now let’s view the report and it will look like below.

report look

Congratulations, you have successfully reported user-specific time zone in Google Data Studio using Google Analytics and Google Tag Manager.

Now you have the option to select the user-specific time or application time (Google Analytics time zone setting) from the ‘Time Repository’ drop-down.

time dropdown preview

You can also select the different continents from the “Continent” drop-down

Frequently Asked Questions about Google Sheets & Google Data Studio Time Zone Conversion

How do I find out what time zone Google Sheets is using?

You can find out what time zone your Google Sheets is using via the ‘now()’ function. This function returns the current date and time.

How do I find out what time zone Google Analytics is using?

Navigate to Google Analytics account and select the property and view. Time zone settings are available in View settings. You can set the different time zone for different views.

What is the difference between the application set time zone and the user-specific time zone?

The application set time zone captures the user timing based on the configuration settings in Google Analytics whereas user-specific time zone is the time of the country where the user belongs.

For example, if I set my Google Analytics time zone to (GMT-7.00) Los Angeles time and suppose now it’s 9:00 AM. If a local user visits my website at then both user’s time and application set time will be reported at 9:00 AM. But let’s suppose if any user is visiting from a different country like India at 5:00 PM then it won’t be reported as 5:00 PM in Google analytics. Here Google analytics will convert the time according to the application time set up and will be reported as 7:30 AM.

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!