Report a Different Time Zone in Spreadsheets
When reporting your data with our spreadsheets addon, you may want to adjust the date and time reported to account for a timezone offset. This article will show you how to use a formula to adjust the reported date and time in Sheets.
Some connectors may report dates and times using a time offset other than your current timezone. To correct this and display times in your desired timezone, open your report in Google Sheets.
1. Identify the column and cell where the date and time field begins. In this example, the date and time are listed in column A and they begin at cell A3.
2. Determine the timezone offset you want to apply. For example, adjusting the time to be 5 hours earlier would be an offset of -5, and adjusting the time to be 5 hours later would be an offset of +5.
3. Enter the following formula in the same row as the first cell, substituting the correct cell, column, and time offset:
=arrayformula(if(len([cell]:[column]),[cell]:[column][offset]/24,))
In our example, the first cell is at A3, so we'll enter the following formula in cell C3 to calculate an offset of 5 hours earlier (-5):
=arrayformula(if(len(A3:A),A3:A-5/24,))
The remaining rows in the column will automatically be populated with the adjusted date and time values.
Still having issues?
Send us a ticket and we will get back to you.
Related Articles
Unable to Schedule Refresh in Google Sheets
This article will address the following error: Unable to schedule refresh in Google Sheets. Error shown is "Exception: Bad Value." Cause Google Sheets requires you to set a Time Zone. Additionally, you must be a member of the report's hub in order to ...Facebook and Instagram Insights Time Zone Mismatch
This article will address the following issue: The time in a Facebook or Instagram Insights report does not match the time shown in Facebook Creator Studio. Cause Facebook and Instagram's APIs format all of their data to match the time zone at their ...Missing Data in Google Sheets Report
This article will assist with the following issue: A Google Sheets report is missing data or does not show enough data for a date range. Cause Power My Analytics' Google Sheets connector defaults to a 1,000 row limit. Solution Check the sidebar under ...Spreadsheets Error: Exceeded Maximum Execution Time
This article will assist you with the following error: Exceeded maximum execution time Cause When using the spreadsheets add-on for Google Sheets or Excel, the add-on may time out with this error when you have a large number of reports in your ...Google Sheets Data Integration Guide
Integrate your marketing data into Google Sheets to build custom reports and dashboards. This guide will walk through how to install the Sheets add-on, create reports, edit reports, refresh data, and more. Install the Google Sheets Add-on To install ...