Learn how to convert time zones in Excel with Python step-by-step.

Often, you use third-party websites to convert time zones that might not follow any quality control for data accuracy. Also, such websites might be unavailable when you urgently need to convert the local time to an offshore timezone.

Instead, you can create a robust time zone converter app using Excel for Microsoft 365 desktop app and Python in simple steps. The pytz Python library gives you free access to the dynamically updated and maintained Olson tz time zone database. Hence, without waiting on any third-party app or website, you can convert almost any time zone with complete accuracy and reliability.

Follow along with the steps and illustrations outlined below to create your first Excel Python app that converts time zones with ease. The methods described below require a working internet connection since all the Python scripts are executed on a Microsoft Cloud server.

Fetch All Time Zones and Create a DataFrame

You should see the Python commands block in the Formulas tab if you’re using an updated Excel for Microsoft 365 desktop app. If you don’t or aren’t sure about Python in Excel, you can go through the following Excel Python tutorial:

📒 Read More: How To Use PY Function in Microsoft Excel

Now, go to the destination worksheet, select A1, and name it All Time Zones.

Go to A2, enter the PY function, and type in the following Python script into the cell:

import pytz

# Get a list of all time zone names
all_timezones = pytz.all_timezones

# Create a DataFrame from the list of time zone names
timezone_df = pd.DataFrame(all_timezones, columns=['TimeZone'])

# Output the DataFrame
timezone_df

Hit Ctrl + Enter to execute the script.

Excel will show a Python DataFrame in A2.

Click on the Python Object drop-down arrow in the formula bar for A2 and switch the output to the Excel Value option.

You should now see a complete list of all official time zones.

Create a List of Time Zones You Often Use

Now, rename the first cell of column B to My Time Zones.

If you need to create a list of 10 time zones for frequent use, select 10 cells below column B.

Go to the Data tab and click on the Data Tools drop-down menu.

Select Data Validation from the context menu.

The Data Validation dialog box will open.

Click on the Allow drop-down arrow and select the List option.

Go to the source field and enter the cell range $A$2:$A$598. This is the reference for the time zone list that you created earlier.

Click OK to apply the changes.

Now, select B2 and type in the first few letters of one of your preferred time zones.

According to the initial characters, the Data Validation rule will show a list of matching time zones. For example, if you type US, you get all the USA time zones.

Select one from the list.

Now, repeat the above steps to populate the rest of the 9 time zones you’d often use.

Make a Table for Time Zone Conversion

It’s time to create a table containing the input and output date, time, and time zone values.

For example, your table could contain the following rows:

• Local/ Original Date & Time
• From Time Zone
• To Time Zone
• Converted Date & Time

Find above a graphical representation of the sample time zone conversion table.

Now, you need to appropriately format D2 and D5 so these cells can show date and time values seamlessly.

Select both the cells and press Ctrl + 1 to launch the Format Cells dialog box.

Select the Custom category in the Number tab and apply the yyyy/mm/dd hh:mm:ss formatting style.

Click OK to save the changes you’ve made.

📒 Read More: 9 Ways to Format Time in Microsoft Excel

Select D3 and D4 and apply a Data Validation rule so these cells show inputs only from the My Time Zones column.

Follow the steps mentioned earlier in this tutorial.

Finally, the From Time Zone and To Time Zone rows in the column D will show clickable arrows. Therefore, you or anyone else using the time zone converter can click and select the desired source and destination time zones in a consistent format.

Now, select D5 and enter the PY function. Copy and paste the following Python script into the cell’s formula bar:

# Get the cell values
xl_date = xl("D2")
tz_from = xl("D3")
tz_to = xl("D4")

# Convert the string to a datetime object and set timezone to tz_from
datetime_from = pd.to_datetime(xl_date).tz_localize(tz_from)

# Convert the datetime from tz_from to tz_to
datetime_to = datetime_from.tz_convert(tz_to)

# Function to convert a datetime object to an Excel serial date number
def datetime_to_excel(datetime_obj):
    # Excel's base date is 1899-12-30
    excel_base_date = pd.Timestamp('1899-12-30')
    # Remove timezone information for accurate delta calculation
    datetime_obj_naive = datetime_obj.tz_localize(None)
    # Calculate the difference in days and seconds
    delta = datetime_obj_naive - excel_base_date
    # Return the total number of days plus the fractional part for the time
    return delta.days + delta.seconds / 86400

# Convert the datetime object to an Excel serial date number
excel_date = datetime_to_excel(datetime_to)

You can customize the following code elements only according to your worksheet structure and cell references. You can keep the rest of the code intact.

• xl_date = xl(“D2”) where xl_date is the value of the original date and time
• tz_from = xl("D3") where tz_from is the original time zone
• tz_to = xl("D4") where tz_to is the destination time zone

Press Ctrl + Enter to calculate D5.

Now, enter a date and time value in D2 to find its equivalent date and time as in the Canada/Eastern time zone.

If the Converted Date & Time row shows multiple # characters click on the Python Output arrow in the formula bar and switch it to the Excel Values mode.

Create a Table for Multiple Time Zone Conversions

You can create a table of multiple from and to time zone entries if you need to convert 10 or more time zones simultaneously.

Make an Excel dataset structure that looks like the one shown in the above screenshot.

Select the cell range A2:A11 and apply the custom yyyy/mm/dd hh:mm:ss date and time formatting code using the format cells dialog box.

Repeat the above step for the cell range D2:D11 as well.

Select B2:B11 and apply a Data Validation rule so the cells show drop-down lists for time zone names either from the All Time Zones or the My Time Zones columns.

Now, copy the Data Validation rule from B2:B11 and paste it onto the cell range C2:C11.

Once the dataset formatting is complete, populate the input values for the following columns:

• Original Date & Time
• From Time Zones
• To Time Zones

Your time zone conversion table should now look like the one shown above.

Go to D2 and enable the PY function.

Copy and paste this Python code inside the formula bar for the cell D2.

# Get the cell values
xl_date = xl("A2:A11")[0].tolist()
tz_from = xl("B2:B11")[0].tolist()
tz_to = xl("C2:C11")[0].tolist()

# Function to convert a datetime object to an Excel serial date number
def datetime_to_excel(datetime_obj):
    # Excel's base date is 1899-12-30
    excel_base_date = pd.Timestamp('1899-12-30')
    # Remove timezone information for accurate delta calculation
    datetime_obj_naive = datetime_obj.tz_convert(None)
    # Calculate the difference in days and seconds
    delta = datetime_obj_naive - excel_base_date
    # Return the total number of days plus the fractional part for the time
    return delta.days + delta.seconds / 86400

# Convert each date in the list to the new timezone and store results
results = []
for date_str, from_tz, to_tz in zip(xl_date, tz_from, tz_to):
    # Convert the string to a datetime object and set timezone to tz_from
    datetime_from = pd.to_datetime(date_str).tz_localize(from_tz)
    # Convert the datetime from tz_from to tz_to
    datetime_to = datetime_from.tz_convert(to_tz)
    # Convert the datetime object to an Excel serial date number
    excel_date = datetime_to_excel(datetime_to)
    # Append the results as a dictionary
    results.append({
        "Original": date_str,
        "From": from_tz,
        "To": to_tz,
        "Converted": excel_date
    })

# Create a DataFrame from the results
df = pd.DataFrame(results)

# Output the DataFrame
df["Converted"].to_numpy()

Except for the following code elements, you don’t need to modify the rest of the Python script:

• A2:A11: Represents the input date and time values.
• B2:B11: References to the from time zones.
• C2:C11: Represents the destination time zones.

If your dataset has different cell ranges for the above inputs, customize these cell range addresses accordingly.

In this tutorial, I’ve shown how to convert up to 10 time zones. If you need to convert more than 10, say 15, simply enter the cell range references in the following way:

• A2:A16 instead of A2:A11
• B2:B16 instead of B2:B11
• C2:C16 instead of C2:C11

The Python script has been coded in such a way that it creates a spill range in the destination cell based on the inputs.

Now, press Ctrl + Enter to run the Python code and generate the converted date and time data according to the destination time zones.

Conclusions

So far, you’ve learned how to convert time zones in Excel with Python without relying on a third-party Excel add-in or website.

Follow along with the steps on your own worksheet to practice and perfect the skill. You can also download this workbook if you don’t have a dataset handy to try the steps.

If this Microsoft Excel tutorial helped you to learn Python in Excel, you can share your acknowledgment in the reply field at the end of the guide. Also, don’t forget to share feedback or suggestions should you have any.