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
Google Sheets Error: "Exception: Service Timed Out: Spreadsheets" or "Service Spreadsheets timed out when accessing document"
When using the Power My Analytics spreadsheets add-on in Google Sheets, you may encounter a timeout error that prevents your reports from refreshing. This guide will help you understand the cause of this error and provide a straightforward solution ...Configure Data Source Sync Time and Google Sheets Refresh for the Latest Data
Keeping your marketing data up-to-date is crucial for accurate reporting and analysis. This guide will walk you through configuring your data source sync time and Google Sheets scheduled refresh to ensure you're always working with the most recent ...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 ...Collect Historical Data for Google Search Console
Google Search Console collects live data only. Backfilling and data warehousing are not available for this connector, and Google's API provides only the most recent 16 months of historical data. We recommend the following alternative solutions for ...