How to Convert UTC to EST in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to convert UTC to EST in Excel. UTC stands for Coordinated Universal Time. It was previously known as Greenwich Mean Time (GMT). On the other hand, EST stands for Eastern Standard Time. Today, we will show 3 easy methods. Using these methods, you can easily convert UTC to EST in Excel.


What Is UTC?

UTC is a common standard time that regulates the world clock. It is not adjusted for daylight saving time. UTC or Universal Coordinated Time expresses time with respect to date, hours, minutes, and seconds. The local times are ahead or behind the universal coordinated time. Also, we can find the local time from the UTC if we know how many hours we are lagging or leading.


What Is EST?

As stated earlier, EST stands for Eastern Standard Time, people of North America, the Caribbean, and Central America use this standard. The EST is 5 hours behind UTC (Universal Coordinated Time). Also, it is not adjusted for daylight saving time. When it is adjusted for daylight saving time, it becomes 4 hours behind UTC. And that is known as EDT (Eastern Daylight Time).


How to Convert UTC to EST in Excel: 3 Easy Ways

To explain the methods, we will use a dataset that contains some timestamps in UTC format. We will convert the UTC to EST using the methods. Suppose, cell B7 contains 06:00:00 UTC. As EST is 5 hours behind UTC, the EST will be 01:00:00.

convert utc to est in excel

Here, the range B5:B8 contains both date and time. If you click on a cell, for example, cell B6, you will see the date in the formula bar. The cells are formatted to show the time only. To understand the conversion, you may need to show the date also. We will show the process in the following sections.


1. Insert Simple Formula to Convert UTC to EST in Excel

In the first method, we will insert a simple formula to convert UTC to EST in Excel. We need to subtract 5 hours from the UTC to get the EST. To do so, we need to divide 5 by 24 first, then subtract it from the UTC. Let’s follow the steps below to see how we can implement this.

STEPS:

  • First of all, select cell C5 and type the formula below:
=B5-5/24

Insert Simple Formula to Convert UTC to EST in Excel

  • Secondly, hit Enter and drag the Fill Handle down.

Insert Simple Formula to Convert UTC to EST in Excel

  • As a result, you will see the EST in the range C5:C8.

Insert Simple Formula to Convert UTC to EST in Excel

  • Now, to show the time with the date, select the range B5:C8.
  • After that, press Ctrl + 1 to open the Format Cells box.
  • In the Format Cells box, go to the Number tab and select Custom.
  • Type m/d/yy h:mm AM/PM;@ in the Type field.
  • Click OK to proceed.

  • Finally, you will see the timestamps with both time and date.

Insert Simple Formula to Convert UTC to EST in Excel

Read More: How to Convert IST to EST in Excel


2. Change UTC to EST with Excel TIME Function

We can also use the TIME function to change UTC to EST in Excel. The TIME function converts hours, minutes, and seconds to serial numbers. We can substitute the previous formula with a new one. Let’s pay attention to the steps below to learn more.

STEPS:

  • In the first place, select cell C5 and type the formula below:
=B5-TIME(5,0,0)

Change UTC to EST with Excel TIME Function

As EST is 5 hours behind, so we have subtracted TIME(5,0,0) from Cell B5. The output of TIME(5,0,0) is 05:00:00. So after the subtraction, we get 19:00:00.

  • Secondly, press Enter and drag the Fill Handle down.

Change UTC to EST with Excel TIME Function

  • As a result, you will see the timestamps in EST format.

Change UTC to EST with Excel TIME Function

  • To show the date and time together, select the range B5:C8 and press Ctrl + 1 to open the Format Cells box.
  • In the Format Cells box, go to the Number tab and select Custom.
  • Then, type m/d/yy h:mm AM/PM;@ in the Type field.
  • Click OK to proceed.

  • Lastly, you will see the timestamps with both dates and times.

Change UTC to EST with Excel TIME Function


3. Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

In this method, we will use a different dataset. Here, the UTC time is formatted in a different way. The UTC is expressed as 2021-9-12T00:00:00. You can see that in the picture below. To find the EST time, we need to use the combination of the SUBSTITUTE, LEFT, and MID functions.

Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

Let’s follow the steps below to see how we can implement the method.

STEPS:

  • First of all, you need to type “-05:00:00” in Cell B5 along with the stored text. We have typed this to subtract 5 hours from the UTC.

Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

  • Repeat the same in the range B6:B8.

Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

  • Secondly, type the formula below in cell C5:
=(SUBSTITUTE(LEFT(B5,19),"T", " "))+(MID(B5,19,3)/24)

Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

This formula removes T from cell B5 and replaces it with space first. Then, subtracts 5 hours from it and show the results in a time format.

  • Thirdly, hit Enter and drag the Fill Handle down.

Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

  • As a result, you will get the results below.

  • Now, select the range C5:C8.

  • Press Ctrl + 1 to open the Format Cells box.
  • Click on the Number tab and select Custom from the Category section.
  • Then, write m/d/yy h:mm AM/PM;@ in the Type field.
  • Click OK to proceed.

  • Finally, you will see the timestamps in EST format.

Combine SUBSTITUTE, LEFT & MID Functions to Convert UTC to EST

Read More: How to Convert GMT to EST in Excel


How to Convert UTC to EDT in Excel

EDT stands for Eastern Daylight Time. Generally, it is used in summer instead of EST to make use of daylight. It is 4 hours behind UTC. So, you can follow both Method 1 and Method 2 to convert UTC to EDT. You just need to replace 5 with 4 to get the result. We will apply Method 1 to get the EDT in the following steps.

STEPS:

  • First of all, select cell C5 and type the formula below:
=B5-4/24

How to Convert UTC to EDT in Excel

  • After that, press Enter and drag the Fill Handle down to get the results.

How to Convert UTC to EDT in Excel

  • To display the dates, select the range B5:C8.

  • Then, open the Format Cells box by pressing Ctrl + 1.
  • Click on the Number tab and select Custom from the Category section.
  • Now, write m/d/yy h:mm AM/PM;@ in the Type field.
  • Click OK to proceed.

  • As a result, you will see the timestamps in EDT format.

How to Convert UTC to EDT in Excel


How to Change UTC/GMT to Local Time in Excel

We convert the UTC/GMT to any local time in Excel. GMT is the same as UTC. To convert time, we need to know how many hours the local time is behind or ahead of UTC/GMT. Here, we will convert a set of UTC to New York and Tokyo local time. Let’s follow the steps below to know more.

STEPS:

  • In the beginning, we will convert UTC to New York local time. New York is 4 hours behind UTC. So, we need to subtract 4 from the UTC.
  • For that purpose, select cell C5 and type the formula below:
=B5-TIME(4,0,0)

Here, we have used the TIME function to subtract 4 hours from the UTC.

  • After that, press Enter and drag down the Fill Handle to copy the formula.

  • Also, you can change the format to show both the time and date.

How to Change UTC/GMT to Local Time in Excel

  • To convert UTC to Tokyo local time, we need to add 9 hours to the UTC. Because Tokyo is 9 hours ahead of UTC.
  • To do so, type the formula in cell C5:
=B5+TIME(9,0,0)

  • In the end, press Enter and drag the Fill Handle down.

How to Change UTC/GMT to Local Time in Excel


Download Practice Workbook

You can download the workbook from here.


Conclusion

In this article, we have 3 easy methods to Convert UTC to EST in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. If you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo