How to Convert UTC time to EST in Excel: 6 Ways

Have you ever found you needed to convert time between different time zones in Excel? Whether you are working on a global project, planning a virtual meeting, or maintaining a timesheet for project status. In this article, you will learn various Excel methods that allow you to convert Universal Time (UTC) to Eastern Standard Time (EST) easily.

It is better to have a clear understanding of time zones before starting to learn the conversion of time zones. The time zones are regions with the same standard time, enabling people across the world to coordinate activities and synchronize.

The difference between time zones is usually measured in hours ahead or behind Coordinated Universal Time (UTC). EST (Eastern Standard Time) is 5 hours behind UTC. This will help to convert time from UTC to EST accurately.

This article explores 6 different ways to convert UTC to EST. Use the Excel TEXT and TIME functions to convert it and utilize the Power Query and Power Pivot methods for multiple data files and large data sets.

Excel Office Scripts, VBA, and Python code allow you to automate the process of converting from UTC to EST.

Get your copy of the example workbook used in this blog post to follow along!

Convert UTC time to EST with Excel Formula

In this section, you will use the two important functions – TEXT and TIME formulas to convert time from UTC to EST in Excel. The TEXT formula in Excel helps you to manipulate the format of your data in various ways, which makes your data presentation more visually appealing and meaningful. Excel offers a variety of time-related functions that allow you to convert between time zones.

Go to cell C2, type the formula =TEXT( B2 - TIME( 5, 0, 0 ), "dd-mm-yyyy hh:mm" ). Press Enter. Drag the fill handle to the range B2:B21 to copy the formula.

In the above formula, cell B2 refers to the UTC time you want to convert. The TIME function takes three arguments hours, minutes, and seconds. Part of the formula subtracts the time difference between UTC to EST. In this formula, you subtract 5 hours from the UTC time zone. It is the time difference between UTC to EST.

Excel will calculate the converted time, giving you the result in EST. Finally, you use the TEXT() function with the converted date and the desired format to display the EST date as “dd-mm-yyyy hh:mm”. In the cell C2, the result will be 12-11-2023 02:20.

Convert UTC time to EST with Excel Power Pivot

Power Pivot is a data modeling tool in Excel. It allows you to create complex relationships between measures, add calculated columns, pivot tables, charts, and reports. You will use the Power Pivot FORMAT formula along with the TIME function to convert UTC to EST.

1. Select the range of cells A1:B21 and press the command Add to Data Model in the Power Pivot Tab.

2. Rename the table name PPData, and column Header EST Time in the Power Pivot editor, select the third renamed column, type the formula =FORMAT( PPData[UTC Time] - TIME( 5, 0, 0 ), "dd-mm-yyyy hh:mm" ), and press Enter. In this formula you can see the PowerPivot FORMAT function, which is formatting the date value like the TEXT function in the above section.

3. Select the Flattened PivotTable option in the PivotTable command.
4. You will get options to select the New Worksheet or Existing worksheet and select the worksheet location.  Once you select the worksheet and press the OK button, Excel adds an empty pivot table in the worksheet.

5. You can add the EmpName, UTC Time and EST Time fields in the Rows Area and see the converted EST Time in column C.

Convert UTC time to EST with Excel Power Query

You will walk through the process of using Excel Power Query to convert between Universal Time (UTC) to Eastern Standard Time (EST). Excel Power Query is a powerful tool that allows for seamless data manipulation and analysis. It saves time and effort when dealing with complex time zone conversions. You can transform your data and ensure accurate conversions between UTC and EST with just a few simple steps.

Steps to convert UTC to EST in Power Query

1. To import the data to power query, Select the data range A1:B21 in the Data sheet, navigate to the Data tab and click the option From Table/Range.  

Make sure your data is properly formatted. This will help to avoid any issues during the conversion process. 

2. Once the data is imported, go to the Add Column tab. Click the ABC icon on the header of the column in the Power Query and select the Date/Time option to change the data type to DateTime type if the column is not formatted.
3. Select Custom Column. To convert UTC to EST, you will need to use a formula in the custom column.
4. In the Custom Column dialog, enter a name EST Time for your new column.
5. Type the formula =DateTimeZone.SwitchZone( DateTime.AddZone( [UTC Time], 0 ), -5 ) in the Custom column formula pane

You have used two Power Query functions DateTimeZone.switchzone() and DateTime.AddZone() in this formula.

The DateTime.AddZone( [UTC Time], 0) function is used to add the timezone information to the dateTime value from UTC time. You can specify the offset values if your input time is not UTC.

The DateTimeZone.SwitchZone() changes timezone information to the new timezone information provided by timezoneHours. Assigned -5 for the argument timezoneHours in the function, since the given input time is in UTC time zone and EST (Eastern Standard Time) time zone is 5 hours behind UTC.

6. Press OK

7. Finally, you have to change the column type to Date/Time before loading to Excel. Simply click the ABC icon on the header of the custom column and select the Date/Time option to change the data type. 
8. Press Close & Load icon to load the table to Excel Sheet.

9. In the Import Data dialog box, select the Table option, and provide the location in the Existing worksheet to insert the transformed Power Query table into your worksheet.

Convert UTC time to EST with Excel VBA

VBA (Visual Basic for Applications) can be utilized to convert time from Universal Time (UTC) to Eastern Standard Time (EST). This functionality helps to deal with international time conversions and ensures accurate scheduling or coordination across different time zones.

VBA provides a range of functions and methods to process date and time values within Excel. You can easily convert UTC time values into their corresponding EST equivalents by using these capabilities. By using VBA code, the process of time conversion can be automated and streamlined.

To convert UTC to EST in Excel using VBA, you can follow the below example code.

Sub ConvertEST()

' Declare the variables UTCTime and ESTTime

Dim UTCTime As Date

Dim ESTTime As Date

' Loop iterate through the selected range of cells

For Each rg In Selection

    ' Assign the current cell value to the variable UTCTime

    UTCTime = rg.Value

    ' Use DateAdd function to convert UTC to EST and assigned to ESTTime variable

    ESTTime = DateAdd("h", -5, UTCTime)

    ' Paste the converted EST time to the adjacent column

    rg.Offset(0, 1).Value = ESTTime

Next

End Sub

Insert the above code into your VBE.

Macro declares the two variables UTCTime and ESTTime, which represent the specific UTC and EST time zone value that needs to be converted. Loop through each cell in the selected range of cells, inside the loop in each iteration the macro assigns the current cell value to the UTCTime variable, by using the DateAdd function to convert the specified UTC time value into its equivalent EST time value and subtract 5 hours from the UTC time. Finally set the ESTTime values to the adjacent cell.

Steps to Run the ConvertEST macro.

1. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
2. Choose the ConvertEST Macro and then Press Run.

Convert UTC time to EST with Excel Office Scripts

Office Scripts is a powerful scripting language within Excel that allows you to automate repetitive tasks and execute both Web and Desktop applications. In this section, you will learn how to convert Universal Time (UTC) to Eastern Standard Time (EST) using Office scripts, along with an example code.

You can apply the Office Scripts built-in functions and methods to convert UTC to EST.

function main(workbook: ExcelScript.Workbook) {

  // Assign the range of cells to the variable

  let rng = workbook.getSelectedRange();

  let rows = rng.getRowCount();

  // loop through the range of cells

  for ( let i = 0; i < rows; i++ ) {

    // Assign the current cell value to the variable currentDate.

    var currentDate = new Date( rng.getCell(i,0).getValue() as string );

    // Properly format the currentDate string values as a UTC time

    var utcDate = new Date( Date.UTC(currentDate.getFullYear(), currentDate.getMonth(), currentDate.getDate(), currentDate.getHours(), currentDate.getMinutes(), currentDate.getSeconds() ) )

    // Convert UTC Time to EST Time

    var estDate = utcDate.toLocaleString( "en-US", { timeZone: 'EST' } );

    // Set the EST Date to the respective cells

    rng.getCell( i, 1 ).setValue( estDate )

  }

}

First get the selected range of cells in the active sheet and assign it to the variable rng. Assign the number of rows in the selected range to the rows variable. Loop through the selected range in each iteration of the loop stores the current cell value to the variable currentDate as a Date object.

var utcDate = new Date(Date.UTC(currentDate.getFullYear(), currentDate.getMonth(), currentDate.getDate(), currentDate.getHours(), currentDate.getMinutes(), currentDate.getSeconds()))

The Date.UTC() function is similar to the Date constructor, but it treats them as UTC time.

Above statement creates a Date object with the arguments treated as a UTC time zone and assigned to utcDate variable. Next use the toLocaleString function with the appropriate timezone parameter en-US to convert it into Eastern Standard Time (EST). Finally, the code sets estDate to the adjacent cell.

Follow the below steps to execute the Convert UTC to EST Office script in Excel

1. Select the range of cells B2:B21
2. Go to the Automate menu.
3. Select the Convert UTC to EST Script – to open the Code editor on the right side of your excel application.
4. Press Run 

Convert UTC time to EST with Python in Excel

Python in Excel is available on the Beta channel. It runs on Microsoft Cloud and has a variety of packages and functions that enable time conversion. One commonly used package for time conversion in Python is the datetime module. This module provides classes and functions to manipulate dates and times effectively. By utilizing the datetime module and Pandas, you can easily convert times from Universal Time (UTC) to Eastern Standard Time (EST) by using the timedelta function.

The timedelta function accepts 7 arguments: days, seconds, microseconds, milliseconds, minutes, hours, and weeks. You will use the hours argument to convert UTC to EST in Python.

Steps to run Python in Excel

1. Select the cell C2.
2. Click the Insert Python command in the Formulas Tab Python (Preview) groups.
3. You can see the PY in the formula bar. It indicates that you are writing a Python script. Copy the below Python script in the formula bar.

# Python script to convert UTC to EST.

# Import the pandas and datetime packages

import pandas as pd

from datetime import datetime, timedelta

# Assigns excel range B2 to B21 to the dataframe variable utcTime

utcTime = xl( "Python!B2:B21" )

# Declare estTime variable

estTime = []

# Loop through the utcTime dataframe

for i in range( utcTime.size ):

    # Subtract 5 hours from the data. EST is 5 hours behind UTC.

    estTime.append( utcTime.iloc[ i, -1 ] - timedelta( hours = 5 ) )

# Write estTime data to Excel cells

estTime

In the above code, you first import the necessary libraries – datetime and pandas. The datetime and timedelta classes get from the datetime package and then assign the excel range to the variable utcTime by using xl ( ) function. Declare an empty list estTime.

Iterate through each item in the utcTime dataframe using for loop. The code utcTime.size returns the number of items in the utcTime dataframe. Inside the loop create a timedelta object using hours = 5 argument and subtract it from the current value and append it to the estTime list. Since the EST (Eastern Standard Time) is 5 hours behind UTC. Finally, you simply pass the estTime list object to Excel.

4. Press the key Ctrl + Enter to commit Python code in Excel. Default, it will return the result as a Python list object. You need to convert it to Excel values.

5. Choose the Excel Value option in the formula bar Python Output button drop down to convert python list object to Excel value.

Conclusion

You understood the time zones and explored 6 different methods to convert UTC time zone to EST in Excel. 

Excel in-built functions TEXT and TIME are easy to use, and the formula automatically updates the cells whenever the input cell value is changed. The TEXT formula enables you to transform data formats, while the TIME formula simplifies time-related calculations.

If you are connecting multiple data sources and creating complex data models you can use Power Pivot or Power Query methods. Excel’s Power Query and Power Pivot are the perfect tools for handling complex calculations and data transformations.

You can utilize the VBA, Office Scripts, and Python code, which helps you to convert UTC to EST time zones in your automation or dashboard projects.

Do you know any other method to Convert UTC to EST in Excel? Let me know in the comments below!