This guide explains how to format dates, times, and time zones in Google Sheets while using GSheetConnector plugins. You can change these settings through WordPress Settings or directly within Google Sheets.
WordPress Settings
To change the date/time format and time zone in the connected sheet, ensure the date/time and time zone settings in the WordPress Default Settings Tab are correctly configured.
After making the changes, click save. Then, test the site by filling in the details. The date, time, and time zone will be displayed in the sheet according to your selected settings.
Google Sheets
Formatting Existing Dates/Times:
- Select the Cells: Highlight the cells containing the dates and times you want to reformat.
- Choose the Format: Go to the Format menu in the toolbar and select:
- Number > More formats > Custom date and time to create a highly customized format (e.g., “YYYY-MM-DD hh:mm
Z” for year-month-day, hours:minutes
with time zone). - Number > More formats > Date or Time for simpler predefined formats.
- Number > More formats > Custom date and time to create a highly customized format (e.g., “YYYY-MM-DD hh:mm
Converting Time Zones (Using Formulas):
If the dates/times in your sheet are in a different time zone than the one you need, you can use formulas to convert them. Here’s an example:
- Enter the Original Date/Time: In cell A1, enter the date/time in the specific time zone (e.g., “2024-07-04 10:00 AM PST”).
- Convert to UTC: In cell B1, use the TO_GMT function to convert it to Coordinated Universal Time (UTC):
=TO_GMT(A1)
- Apply Time Zone Difference: In cell C1, add or subtract the time zone difference to get the desired time zone (e.g., convert to Eastern Standard Time, which is UTC-5):
=B1 + INTERVAL '5' HOUR // Add 5 hours for EST
To finalize, navigate to Settings > General in Google Sheets and select the desired date and time format. You can also set the preferred time zone from this menu.