How to Convert UTC to EST in Excel: 5 Top Methods

Do you manage an international team or need to make an itinerary for your next trip abroad? Learn how to convert UTC to EST in Excel in quick steps.

UTC (previously known as GMT) is the global time standard. All global locations are assigned to a particular time zone based on UTC. One of the time zones is EST, which is 5 hours behind GMT and followed in 23 states of the USA, and some parts of Canada, Mexico, Caribbean islands, and many more.

If you use Excel regularly, you don’t have to use any additional app to convert UTC into EST. Read this tutorial on how to convert UTC to EST in Excel, and you’re good to go.

Why Do You Need to Convert UTC to EST in Excel

• If you’ve got an international team whose members are spread across the world, you’ll need to convert UTC to EST to find out if a time is suitable for your colleagues that follow the EST. While dealing with global communication in different time zones, especially in some regions of North America, it’s often helpful to convert UTC to EST during the non-daylight saving period.
• Are you planning to travel to Eastern USA, Eastern Canada, Mexico, and other countries of Central America? In that case, too, you need to convert UTC to EST to adjust your schedule and accurately manage arrival and departure times.
• International broadcasting companies, media, and event organizers need to make their programs accessible to local viewers and audiences. They need to convert UTC to EST to ensure attendees can join the events at the correct local time.
• For companies that use UTC, it’s crucial to convert that into EST if they want to schedule meetings and appointments with parties that follow EST. It enables both parties to understand the timing accurately.
• In this time of globalization, many of us have family members and friends living in the Eastern Standard Time zone. If you’re one of them, you need to convert UTC to EST for timely communication with them.

Find below common methods to change a timestamp in UTC to EST in Excel:

Convert UTC to EST Using TIME Function

The time difference between UTC and EST is 5 hours. You can use the TIME function to subtract this time difference from a UTC timestamp to get its EST equivalent. Follow these steps:

1. Go to the worksheet and double-click the cell where you want EST time.
2. Copy and paste the following formula into the cell:

=B2-TIME(5,0,0)

3. Press the Enter key to convert UTC to EST

In the above formula, B2 is the source of date and time or simply time in the UTC zone. So, change this cell reference according to your own dataset.

If there are multiple rows of UTC time that need converting, drag the fill handle below the cell where you converted UTC time to EST time. Excel will apply the same formula to all the rows beneath the selected cell.

Suppose, it’s winter or autumn and you’re following EDT instead of EST. You can introduce a small change into the above formula and follow the rest of the steps as is. Here’s the modified formula you should use:

=B2-TIME(4,0,0)

Translate UTC to EST Using Subtraction

To convert a UTC timestamp to EST, you need to subtract 5 hours from the source. Firstly, you must convert 5 hours to a fraction of the day.

You can accomplish this by dividing 5 hours by 24 hours. Then, subtract the fraction from the UTC timestamp. Find below the formula and steps you must use in your Excel worksheet:

1. Select cell C2, considering the column for the EST timestamp is C.
2. Copy and paste the following formula into the selected cell:

=B2-5/24

3. Press the Enter key to complete the conversion process.

The above formula pulls the source timestamp from B2. If your UTC timestamp is in another cell, say, A2, change the cell reference accordingly.

You can also copy and paste the formula below the column to convert other UTC timestamps to EST quickly.

If you wish to convert UTC to EDT following the above formula, replace 5 hours with 4 hours. Here’s the modified formula:

=B2-4/24

Convert UTC to EST Using Excel VBA

If you find the above methods too manual and time-consuming, you can automate the conversion process using a simple Excel VBA script. Find below the code and steps to use the code:

1. Go to the Developer tab on the Excel ribbon and click Visual Basic inside the Code commands block.

2. The Excel VBA Editor will pop up.
3. On this, click the Insert button.
4. Choose Module on the context menu that shows up.
5. A blank module will open. Into this, copy and paste the following script:

Sub ConvertTimestampsToEST()
    Dim ws As Worksheet
    Dim rngUTC As Range
    Dim rngEST As Range
    Dim utcCell As Range
    Dim estCell As Range
    Dim targetCell As Range
    
    ' Set the worksheet object
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    ' Set the range for UTC timestamps (B2:B7)
    Set rngUTC = ws.Range("B2:B7")
    
    ' Set the range for EST timestamps (C2:C7)
    Set rngEST = ws.Range("C2:C7")
    
    ' Loop through each cell in the UTC range
    For Each utcCell In rngUTC
        ' Get the UTC timestamp value
        Dim utcTimestamp As Date
        utcTimestamp = utcCell.Value
        
        ' Convert UTC to EST (subtracting 5 hours)
        Dim estTimestamp As Date
        estTimestamp = utcTimestamp - TimeSerial(5, 0, 0)
        
        ' Set the corresponding cell in the EST range with the converted timestamp
        Set estCell = rngEST.Cells(utcCell.Row - rngUTC.Row + 1)
        estCell.Value = estTimestamp
    Next utcCell
    
     ' Set the target cell
    Set targetCell = ws.Range("C2:C7") ' Replace "C2" with the desired cell reference
    
    ' Apply the custom number format to the target cell
    targetCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
    
End Sub

6. Click the Save button on the Excel VBA Editor toolbar.
7. Close the VBA Editor.

In the above code, you must change the cell or cell range references to make the script work on your own Excel dataset. Here are the code elements that need modification:

• ws.Range("B2:B7"): Replace B2:B7 with actual cell ranges of the UTC timestamp source on your spreadsheet
• ws.Range("C2:C7"): Similarly, replace C2:C7 with the actual destination cell range where you want the EST timestamps
• TimeSerial(5, 0, 0): Replace 5 with 4 if you need to convert UTC to Est

Once you’re done with the necessary editing, don’t forget to save your Excel VBA script again. Now, follow these steps to execute this Excel VBA macro:

1. Press Alt + F8 to bring up the Macro dialog box.
2. There, select the ConvertTimestampsToEST macro.
3. Hit the Run button to execute the macro.

Once you run the above VBA script, Excel will automatically convert UTC timestamps to EST and format the resulting cell values with a Custom Cell Format m/d/yyyy h:mm AM/PM.

The results will show up in the cell range C2:C7 or the ones you entered into the above code.

Using Power Query to Convert UTC to EST in Excel

Are you importing UTC timestamps of servers or software from a remote database? Do you need that time data in EST? You can use this Power Query technique to convert UTC to EST in Excel:

1. Click the Data tab on the Excel ribbon and click Get Data inside the Get & Transform Data block.
2. Choose your option for the data-importing channel on the Get Data context menu.
3. You should now see the imported data in Power Query.

4. Click the Add Column tab on the toolbar.
5. Inside the General block, click Custom Column.
6. Type EST into the New column name field.
7. Enter the following formula inside the Custom column formula field:

DateTime.AddZone([#"Lisbon (UTC)"],-5)

8. In the above formula, [#"Lisbon (UTC)"] is the column reference for source timestamps. Change it by inserting your own dataset from the Available columns section.
9. Click OK to save and create the new column.

10. On the Power Query Editor, click File and then select Close & Load.
11. The converted EST timestamps will show up in a new table in your Excel workbook.

The resulting EST timestamps will show as numbers instead of a m/d/yyyy h:mm AM/PM format.

Here’s how to reformat:

1. Select the cell range of the EST timestamps.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. Click the Custom category on the left.
4. Copy and paste the following code inside the Type field:

m/d/yyyy h:mm AM/PM

5. Click OK to save and close the dialog box.

You should now see the converted EST timestamps in the appropriate format.

Convert UTC to EST Using Office Scripts

Want to automate the UTC to EST timestamp conversion process on Excel on the web? You can use this Office Scripts method. The technique is also compatible with Excel for Microsoft 365 desktop app.

However, the feature would only be available to you if you own Microsoft 365 Business Standard or a higher subscription plan. If you see the Automate tab on your Excel installation or web app, follow these steps:

1. Click Automate and choose New Script inside the Scripting Tools block.
2. Inside the Office Scripts Code Editor, copy and paste the following code:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=B2-5/24");
	// Paste to range C3:C7 on selectedSheet from range C2 on selectedSheet
	selectedSheet.getRange("C3:C7").copyFrom(selectedSheet.getRange("C2"), ExcelScript.RangeCopyType.all, false, false);
}

3. Click Save script to save the code.
4. Click Run to convert UTC timestamps to EST in Excel.

In the above Office Scripts code, make the following changes to suit the code to your own dataset:

• C2: Change it to the cell reference where you want the converted timestamps. This should be the second cell of the destination column considering the first cell contains a column header.
• B2: Change it to the cell reference of the first cell below the column header of the source timestamps in UTC.
• C3:C7: This is the span of column C where Excel will populate the converted values. Change this according to your own dataset. For example, if you’ve got source timestamps between B2:B100, then the destination cell range would be C3:C100.
• In this codelet "=B2-5/24", replace 5 with 4 if you need to convert UTC timestamps to EDT.

Conclusion

Different countries fall under various time zones due to their geolocation. You must be aware of the time zone difference for international communication for personal and business purposes.

Also, you can convert your local time zone into another time zone to know which time is convenient for the residents of that target time zone for communication. 

If you follow the UTC time zone and want to know where your time zone stands against EST, you need to convert UTC to EST. Now that you know how to convert UTC to EST, you can easily convert the time zone. 

Interested readers can also learn how to use Excel for calculating business days left in a year and converting Kb to GB.