Rafiul Hasan
If you work for a foreign international organization, then you might need to know the time of different regions of the world. Because of the rotation of the earth, the time varies from country to country, region to region. When you need to travel from one region to another, you must have to know the time difference and convert time for the specific region. I will show you how to convert GMT to EST in Excel in this article.
Basics of GMT and EST
GMT stands for Greenwich Mean Time. It is the local clock time at Greenwich. This time-zone is Until 1960, it was the first time standard. But later it was replaced by the Universal Time Coordinated (UTC). Still now, people in many regions consider it as a standard.
EST means Eastern Standard Time. It is the time on the eastern coast of the United States and Canada.
GMT is 5 hours ahead of EST. To convert one time zone to another, you have to just add or subtract the difference of the time-zone. If you are located to the east of the UK, you have to subtract the difference and if you are in the west, add the difference.
So, to convert GMT to EST, you have to subtract 5 hours from GMT.
How to Convert GMT to EST in Excel: 4 Quick Ways
In this section, you will find 4 quick and efficient ways to convert GMT to EST in Excel. I will demonstrate them one by one here. Let’s check them now!
1. Convert (hh:mm:ss AM/PM) Format GMT Time to EST
Let’s say, we have got a dataset of some travelers who travel at different times from the time-zone of GMT to the time-zone of EST. As a result, they have to get to know the time at EST zone.
Here, the time is formatted as (hh:mm:ss AM/PM). Here, we will use the TIME function to convert GMT to EST. In order to serve this purpose, proceed with the following steps.
Steps:
- First of all, create a column for the timestamp of EST and apply the following formula for the first cell of the column.
=C5+1-TIME(5,0,0)
Here,
- C5= timestamp GMT
💡 Formula Breakdown
TIME(5,0,0) returns 5 Hours 0 min 0 sec.
Here, C5+1 means the time only (1 is added to ignore Error induced from Date).
So, C5+1-TIME(5,0,0) subtract 5 hours from 6:11 PM and returns 1:11 PM.
- Then, press ENTER, and your cell will return the EST.
- Now, drag the Fill Handle tool down to Autofill the formula for the other data.
- Hence, the cells will convert the GMT to EST.
Read More: How to Convert GMT to IST in Excel
2. Converting to EST from (DD-MM-YY hh:mm:ss) Format
If your GMT data includes the date (DD-MM-YY hh:mm:ss), even then you can convert it to EST.
Let’s say the travelers in our previous dataset traveled on different days and timespan and we want to convert them. In order to do so, follow the steps below.
Steps:
- Firstly, type the following formula in the selected cell.
=C5-TIME(5,0,0)
Here,
- C5= timestamp GMT
➡ Note: As this data includes the date, so you don’t have to add 1 to the formula.
- Then, hit ENTER and drag the formula for the next cells to get the time-zone conversion.
Read More: How to Convert UTC to EST in Excel
3. Substracting Hours to Convert Time Zone
If you don’t want to use the TIME function for time-zone conversion, even then Excel allows you to convert GMT to EST. You have to just subtract the hours in this case. This method is helpful for those who don’t want to use the TIME function. So, let’s start the process like the one below.
Steps:
- First of all, type the following formula to the selected cell.
=C5-5/24
Here,
- C5= timestamp GMT
💡 Formula Breakdown
C5-5/24 returns the time after subtracting 5 hours out of 24 hours from the cell value of C5.
So, the output is 28-08-22 (18:11-5:00)= 28-08-22 13:11
- Then, press ENTER to allow the cell to show the result.
- Now, drag the formula down to the other cells.
4. Convert Current GMT to EST
If your location is in the time zone of EST and you want to know the time at this moment in the time zone of GMT, then you are welcome! We will show you two processes here for this purpose.
4.1. Using TIME Function
In order to use the TIME function for converting the time zone, just proceed with the steps below.
Steps:
- Firstly, select a cell and type the following formula.
=NOW()-TIME(5,0,0)
💡 How the Formula Works
NOW() function returns the current time..
NOW()-TIME(5,0,0) results in subtracting 5 hours from the current time.
- Then, then press ENTER and you will see the time in the EST zone.
4.2. Substracting Hours
You can also get the present time in the EST zone by subtracting the time difference from GMT. Proceed like the steps below for demonstrating this method.
Steps:
- First, type the following formula to the cell you want the result.
=NOW()-5/24
- Then, hit ENTER to let the cell show the result.
Read More: How to Convert IST to EST in Excel
Things to Remember
- Don’t forget to add “1” in the formula when your data doesn’t include a date.
- Subtract the time difference for the zone located to the east of the time zone of GMT.
Download Practice Workbook
You can download the practice book from the link below.
Conclusion
In this article, I have tried to show you some methods on how to convert GMT to EST in Excel. I hope this article has shed some light on your way of time-zone conversion in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. You can also visit our website for corresponding articles. Have a great day!
