How to show time in Google Sheets according to your local time zone?

Show local timezone in Google Sheets

Submission times are displayed in Coordinated Universal Time (UTC) when exported to integrations like Google Sheets. You can add another column in your spreadsheet to display submissions in your local timezone using a formula.
To add the formula, follow the steps below:
  • Create a new column and title it Local submitted at.
  • Copy the formula below and paste it into the formula field for the first cell under the new header you created:
=ARRAYFORMULA(IF(ROW(C2)=1, "Local submitted at", IF(ISBLANK(C2), "", C2+6/24)))
  • Adjust +6/24 in the formula to fit your time zone in relation to the UTC time. For example, if it’s -8 hours, change it to -8/24.
  • The formula will calculate and show the new time zone in the cell. Make sure the format is set to display the results as a time and date. Go into Format in the navigation, click on Number, and select Date time.
  • To apply the formula to the rest of the form entries, as well as forms submitted in the future, you can click and drag the first cell down manually.
  • Alternatively, click on the new calculated cell and copy it using cmd/ctrl+ C shortcut.
  • Then, press cmd/ctrl + shift + ↓ to automatically select all cells in the column and press cmd/ctrl+ V to paste the formula. This will ensure that whenever a new form is filled, the formula will apply to the newly created cells showing the adjusted time zone.