Working with Timezones in Google Sheets and Google Data Studio

Some interesting facts about working with timezones:

  1. By default, Google Sheets uses the timezone 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 timezone your Google Sheets is using. 
  3. You can change the date format and timezone in Google Sheets.
  4. You can change the date format and timezone across all Google Sheets.
  5. Supermetrics uses the timezone set in your data platform.
  6. Google Data Studio uses the timezone set in your data platform.
  7. All timezones are calculated based on GMT / UTC

 

How to find out what timezone Google Sheets is using

You can find out what timezone your Google Sheets is using by using the ‘now()’ function. 

This function returns the current date and time:

Here the time is displayed in GMT timezone because that’s the 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 pick up location from your computer settings:

Changing the date format and timezone 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:

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

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

 

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 timezone.

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:

 

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

The date is still in the american date format but now the time is displayed in EST timezone.

 

Changing the date format and timezone across all Google Sheets

When you use the ‘Spreadsheet Settings’ (under the ‘File’ menu) to change the date format and timezone, 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 timezone settings across all Google Sheets then you would need to change the location and timezone settings for your computer. 

 

Changing the timezone used by Supermetrics

Supermetrics uses the timezone set in your data platform

So if you are pulling data from ‘Google Analytics’ via 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 timezone used by supermetrics then you would need to change the timezone settings for the reporting view:

Similarly, if you are pulling data from any other data platform say ‘Google Ads’ via 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. 

So same timezone 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 in data studio.

 

Changing the timezone used by Google Data Studio

Google Data Studio uses the timezone set in your data platform

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

If you want to change the timezone used by data studio then you would need to change the timezone settings for the 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 say ‘Google Ads’ into data studio then the data studio will use the time zone set for your Google Ads account. 

There is currently no option to change the timezone for reports from within data studio. 

 

How timezones are calculated worldwide

Since timezone 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 timezone is either ahead or behind GMT. 

For example, EST (Easter 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 set doesn’t use UTC, you may see discrepancies when charting data for the current day (due to differences between UTC and your timezone).

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

So if you live in say ‘Australia’ and you pull the data for the current day (from the data set which uses different timezone say ‘UTC’) in data studio, it may not show you any data for ‘today’. 

 

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

So avoid charting data for the current day in data studio.

 

Converting timezone in UTC in Google Sheets

Google recommends that you convert your date and time field to UTC at the data source level via a calculated field which uses the TODATE() function.

However use of this function can greatly slow down your report esp. when you have got a large data set

The better way is to convert your timezone into UTC in your Google Spreadsheet. 

However Google Sheet does not provide any function out of the box to convert any timezone into UTC.

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

It is a Google Sheet which 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

Step-2: Click on the ‘File’ Menu and then click on ‘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“:

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

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

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):

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

Alternatively connect this sheet to the Google Data Studio.

 

Related Articles

 

Do you know the difference between Digital Analytics and Google Analytics?


99.99% of course creators themselves don’t know the difference between Digital analytics, Google Analytics (GA) and Google Tag Manager (GTM).

So they are teaching GA and GTM in the name of teaching Digital analytics.

They just copy each other. Monkey see, monkey do.

But Digital analytics is not about GA, GTM.

It is about analyzing and interpreting data, setting up goals, strategies and KPIs.

It’s about creating strategic roadmap for your business.


Digital Analytics is the core skill. Google Analytics is just a tool used to implement ‘Digital Analytics’.

You can also implement ‘Digital analytics’ via other tools like ‘adobe analytics’, ‘kissmetrics’ etc.

Using Google Analytics without the good understanding of ‘Digital analytics’ is like driving around in a car, in a big city without understanding the traffic rules and road signs.

You are either likely to end up somewhere other than your destination or you get involved in an accident.


You learn data analysis and interpretation from Digital analytics and not from Google Analytics.

The direction in which your analysis will move, will determine the direction in which your marketing campaigns and eventually your company will move to get the highest possible return on investment.

You get that direction from ‘Digital analytics’ and not from ‘Google Analytics’.


You learn to set up KPIs, strategies and measurement framework for your business from ‘Digital analytics’ and not from ‘Google Analytics’.

So if you are taking a course only on 'Google Analytics’, you are learning to use one of the tools of ‘Digital analytics’. You are not learning the ‘Digital analytics’ itself.

Since any person can learn to use Google Analytics in couple of weeks, you do no get any competitive advantage in the marketplace just by knowing GA.

You need to know lot more than GA in order to work in digital analytics and marketing field.


So what I have done, if you are interested, is I have put together a completely free training that will teach you exactly how I have been able to leverage digital analytics to generate floods of news sales and customers and how you can literally copy what I have done to get similar results.

Here what You'll Learn On This FREE Web Class!


1) The number 1 reason why most marketers and business owners are not able to scale their advertising and maximise sales.

2) Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

3) The number 1 reason why conversion optimization is not working for your business.

4) How to advertise on any marketing platform for FREE with an unlimited budget.

5) How to learn and master digital analytics 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 Beyond
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.

Himanshu Sharma

Digital Marketing Consultant and Founder of Optimizesmart.com

Himanshu helps business owners and marketing professionals in generating more sales and ROI by fixing their website tracking issues, helping them understand their true customers purchase journey and helping them determine the most effective marketing channels for investment.

He has over 12 years experience in digital analytics and digital marketing.

He was nominated for the Digital Analytics Association's Awards for Excellence.

The Digital Analytics Association is a world renowned not-for-profit association which helps organisations overcome the challenges of data acquisition and application.

He is the author of four best-selling books on analytics and conversion optimization:

error: Alert: Content is protected !!