In this article:

Google Sheets Time Zone Conversion (How to Use It in 2024)

Google Sheets Time Zone Formula

In Google Sheets, there isn't a built-in function specifically designed for converting between time zones directly. However, you can create a formula that effectively converts time from one time zone to another by adding or subtracting the difference in hours (and minutes, if necessary) between the time zones.

 Here’s how you can construct such a formula using the DATE, TIME, and TEXT functions:

=TEXT(A1 + (TIME(N, 0, 0)), "yyyy-mm-dd hh:mm AM/PM")

  • A1: The cell reference containing the original time you want to convert.
  • N: The number of hours you need to add or subtract for the time zone difference. For time zones ahead, use a positive number; for time zones behind, use a negative number.
  • "yyyy-mm-dd hh:mm AM/PM": The format in which you want the converted time to be displayed. You can adjust this string to change the date and time format according to your needs.

Google Sheets Time Zone Conversion

Follow the steps below to use Google Sheets time zone conversion. 

1. Input the Original Time and Date in Your Spreadsheet

In your spreadsheet, select a cell like A1, and type in the time and date you want to convert. Use a clear format, such as "2024-02-23 10:00 AM". This ensures Google Sheets can correctly recognize and manipulate the time and date information for conversion.

google sheets timezone

2. Enter the Basic Time Zone Conversion Formula

Decide on where you'd like the converted time to appear in your spreadsheet. Click into the output cell (e.g., B1) and enter the formula framework: =TEXT(A1+(TIME(N,H,M)),"yyyy-mm-dd hh:mm AM/PM"). 

google sheets timezone conversion

This formula is the foundation for your conversion, where "A1" is the reference to your original time and date, and N, H, and M will be the adjustments you make for the time zone difference.

3. Customize the Formula for Your Specific Time Zones

To convert between specific time zones, you need to adjust the N, H, and M values in the formula based on the time difference between your current time zone and the target time zone.

For example: If you're converting from Eastern Standard Time (EST) to Pacific Standard Time (PST), you subtract 3 hours because PST is 3 hours behind EST. The formula in B1 would be =TEXT(A1-TIME(3,0,0),"yyyy-mm-dd hh:mm AM/PM").

google sheets now timezone

For another example: If you need to convert from Central European Time (CET) to Eastern Standard Time (EST), you subtract 6 hours (considering CET is 6 hours ahead of EST). The formula would be =TEXT(A1-TIME(6,0,0),"yyyy-mm-dd hh:mm AM/PM").

google sheets convert timezone

4. Execute the Conversion by Pressing Enter

With the formula entered, press the Enter key on your keyboard. This action applies the formula and the converted time. This also accounts for the time zone difference, which will be displayed in your selected output cell.

google sheets timezone formula

5. Replicate the Formula for Additional Time Conversions

If you have a list of times in the same column that need converting, you can easily apply the same conversion to them. Click on the cell with your conversion formula, then hover over the small square at the bottom right corner until the cursor becomes a crosshair.

timezone google sheets

We hope that you now have a better understanding of how to use Google Sheets time zone conversion. If you enjoyed this article, you might also like our article on how to hide tabs in Google Sheets from certain users or our article on how to set up Google Sheets image function.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started