How to Convert GMT to EST in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for the Conversion of GMT to EST in Excel

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

Type Formula to Convert GMT to EST

 💡 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.

Drag Formula

  • Hence, the cells will convert the GMT to EST.

Convert GMT to EST in Excel

 ➡ Note: If you don’t add “1” with the GMT, then Excel will automatically calculate the first date of Excel (0/1/1900). And subtracting the start date of Excel will result in an Error in the Output. So be careful. Excel doesn’t want to learn from you! 😛 

Error Occurred without Adding 1

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.

Convert data containing Date from GMT to EST

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.

Substracting Hours to Convert GMT to EST

  • 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.

Convert Current GMT to EST using NOW Function


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!


Related Articles

Rafiul Hasan
Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo