Mursalin Ibne Salehin
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.
Download Practice Workbook
You can download the workbook from here.
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).
3 Easy Ways to Convert UTC to EST in Excel
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, so the EST will be 01:00:00.
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
- Secondly, hit Enter and drag the Fill Handle down.
- As a result, you will see the EST in the range C5:C8.
- 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.
Read More: How to Convert IST to EST in Excel (5 Easy Ways)
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)
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.
- As a result, you will see the timestamps in EST format.
- 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.
Read More: How to Convert GMT to EST in Excel (4 Quick Ways)
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.
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.
- Repeat the same in the range B6:B8.
- Secondly, type the formula below in Cell C5:
=(SUBSTITUTE(LEFT(B5,19),"T", " "))+(MID(B5,19,3)/24)
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.
- 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.
Read More: How to Convert GMT to IST in Excel (2 Suitable Ways)
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
- After that, press Enter and drag the Fill Handle down to get the results.
- 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.
Read More: How to Convert Time Zones in Excel (3 Ways)
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.
- To convert UTC to Tokyo local time, we need to add 9 hours with 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.
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. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
