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.