If you are working internationally, navigating the time zone differences is useful in managing global data efficiently.
One of the significant challenges I faced was converting the GMT timezone values into the EST timezone values.
With that in mind, I took this moment to develop different methods to do this in Excel. Let us dive deep into different time zones by staying in your own time zone (isn’t it?).
GMT Vs EST: Understanding the Difference
Before going right to the Excel stuff, it’s essential to grasp the time difference between GMT (Greenwich Mean Time) and EST (Eastern Standard Time).
The time difference is typically 5 hours, with EST being 5 hours behind the GMT. Since you have time in GMT, EST will always be GMT – 5 hours.
Convert GMT to EST using Simple Subtraction
In this section, I will introduce you to a simple subtraction method based on the logic that EST is always five hours behind GMT. You will use the same logic combined with the TIME function to get the time in EST.
For this section, I am using the following dataset where column A contains the times in GMT, and column B will hold the time in EST format.
In row B2, type in the following formula:
=A2-TIME(5, 0, 0)
Drag the formula down across the rows using the Fill Handle.
The Fill Handle is a “+” icon that appears when you hover your mouse toward the cell where the formula is added.
Pro Tip: You can also select all the rows down and hit Ctrl + D on the Windows system or use the ⌘ + D on Mac to populate this formula across the rows below.
The TIME() function is essential in this formula as it creates a 5-hour time offset that is then subtracted from the time values in column A.
Also, if you see the values in decimal in column B, that’s probably because the column is not formatted to show the values as time and just shows the result of the formula as a number. Go to Home > Format > Format cells… option and convert it to Time format.
What happens when AM appears in GMT Time?
As you see above, all the values in column A are PM’s. But what if those are in AM, and you still wish to convert them to EST? You need a workaround for that, and I will show you the same.
For this example, I use a dataset with all GMT values in AM. I want to show you how it works with the subtraction method for AM values. The dataset is as below:
Now, if you add the = A2 – TIME (5, 0, 0) formula in cell B2 for some of the AM values, you will see hash/pound signs (# # # # #).
If you are thinking that it is just because the column width is so small and you will be able to see values by increasing the column width, you are wrong (Just try it, and you will see what I mean!)
Why is this happening?
Well, Excel doesn’t understand that the timestamp mentioned in column A is just a time. That’s not how it works.
For Excel, every timestamp has a date value attached to it, and if you don’t specify that date, the tool considers the standard date 01/01/1900, the first date recorded in its memory.
Now, imagine for the first cell, where GMT is 12:23 AM, but in the backend, it is 01/01/1900 at 12:23:00 AM for the system.
The tool then tries to subtract 5 hours from it and generate another timestamp, which will be 31/12/1899 7:23:00 PM.
Since there is no such date in Excel’s memory, it cannot provide any time component in column B, and that’s the reason you are seeing #### for such time conversion.
If you look for the last two dates, even if 5 hours are subtracted from them, they still fall under the 01/01/1900 memory date and don’t go beyond it.
Enough of explaining already! What is the workaround for this?
Well, it is as simple as adding 1 to the entire formula before subtracting the time component from the GMT values.
This will add one day to the standard system date to make it 02/01/1900, and then after subtraction, the EST time value will flow correctly.
See it yourself by adding the following formula in cell B2.
=A2 + 1 - TIME(5, 0, 0)
Also read: How to Change Date and Time to Date in Excel?
Using the Array Formula with MOD
The MOD function in Excel is used to get the remainder after dividing one number by another. When it comes to converting the GMT to EST in Excel, the same function can be utilized to handle the date overflow and ensure that the EST result stays within 24 hours.
Remember the issues you faced with the GMT time zones that were in AM format and falling under the previous dates? The MOD function, combined with the array formula, tackles the same.
For this demo, I am using the following dataset.
1. Select the entire range B2:B6.
2. Now, add the formula below in cell B2 and hit the Ctrl + Shift + Enter to convert it to an array formula.
=MOD(A2:A6 - TIME(5, 0, 0), 1)
The MOD function here does not calculate the remainder after the division. However, in this combination, it is used to cover the date time overflow. Here, argument 1 allows you to keep the result in a 24 hours period.
The TIME function then creates a five-hour offset that then is subtracted from every GMT value present in A2:A6. The array formula does that precisely.
If you see the formula, the curly brackets represent an array formula. This means that the formula used is an array formula.
Also read: How to Add Hours to Time in Excel?
Using the LAMBDA Function
If you are a Microsoft Excel 365 user, you would definitely have an edge over others because the LAMBDA function is introduced in this version that allows users to create custom functions of their own with the help of the built-in functions provided by Excel.
The benefit of this method in converting GMT to EST is you have a modular and reusable function of your own which can be proved as an elegant way to represent this conversion between two time zones.
The data you will be using for this demo is –
Since the function itself is new to a lot of the users, it is good to have a look through its syntax, which is as below:
=LAMBDA([parameter1, parameter2, …,] calculation)
Where,
- parameter1, parameter2, …, – are the values you want to use while defining this function. These can be cell references, strings, or numbers.
- calculation – represents the formula created using the built-in Excel functions that can be executed in the context of the parameters defined and then returned as an output.
Now, utilize the LAMBDA function to convert the GMT to EST. Copy down the following formula in cell B2.
=LAMBDA(Time,hoursToSubtract,Dst, Time - TIME(hoursToSubtract, 0, 0) - IF(Dst, 1/24, 0)) (A2, 5, FALSE)
Then, drag the formula down across the rows in column B to apply this function on each row to convert the GMT to EST from column A.
The first part of the LAMBDA function defines all three parameters you are using in this GMT to EST conversion.
- Time – represents the GMT that needs to be converted to EST.
- hourToSubtract – is a time offset that, in your case, will be five hours. In this case, you can also specify it as 5 as a hardcode value, but it is better to keep it dynamic through a formula so that you can use the same formula for other time conversions.
- Dst – This parameter comes into the picture when there is daylight saving. Otherwise, it will be idle. It is a Boolean value set to TRUE, meaning daylight saving in the timezone.
Then comes the calculation where the 5-hour time offset is subtracted from the GMT.
The formula designed under IF is for daylight saving. If there is daylight saving, it will subtract the additional 1 hour from the result (If the Dst argument is set as TRUE).
The LAMBDA function is then evoked for cell A2, where the time offset is set to five hours, and the daylight saving argument is kept as FALSE.
This formula then converts the GMT to EST.
Pro Tip: This formula is dynamic in its way. Imagine you have another timezone to convert to EST. If you know how many hours difference it has from the EST, you just need to add that while evoking the LAMBDA function and you are through!
Also read: Convert Military Time to Standard Time
Convert GMT to EST using the VBA Code
One of the powerful tools in Excel is VBA, which offers you a variety of automation solutions as per need.
You can create a customized procedure tailored explicitly for situations like timezone conversions between GMT and EST.
Throughout this section, you will learn how to write a customizable and scalable VBA code to create a custom function of your own that converts GMT values from one column to respective EST values in another.
Let’s use the following data for this demo.
Inside Excel, hit Alt + F11 to open the VBA editor. If Alt + F11 doesn’t work, you should use the Fn + Alt + F11 combination.
Now, go to the Insert menu and click the Module option to insert a new module.
Inside the Module added, copy and paste the following VBA code that creates a custom function for you of your own, which you can then call on the cells to convert the GMT values to EST.
Function ConvertGMTtoEST(gmtTime As Variant) As Variant
On Error Resume Next
' Convert GMT to EST (GMT-5)
Dim estTime As Date
estTime = gmtTime - TimeValue("5:00:00")
' Check for errors
If Err.Number <> 0 Then
ConvertGMTtoEST = CVErr(xlErrValue)
Else
' If the result is negative, add 1 to avoid negative values
If estTime < 0 Then
estTime = estTime + 1
End If
' Return the numeric value
ConvertGMTtoEST = estTime
End If
On Error GoTo 0
End FunctionNow, click on the Save button in the VBA editor to save this code.
You are not running this code because you are just creating a function, and saving the code is the only necessity for you to use this function on the front end (inside the cells).
Close the VBA editor and go to the Excel sheet. Start typing the =ConvertGMTtoEST in cell B2, and you will see the function in the list.
Use GMT value from cell A2 as an argument in it. Execute the function, and you will see that it works as intended.
Drag the formula down the rows to fill the EST values in all other rows of column B.
The VBA code uses a simple logic of defining a function that subtracts five hours from the GMT time from the input cell (in this case, cells from column A), checks for the EST time falling beyond 01/01/1900, which is a date from onward Excel knows dates.
It adds 1 day to such times, so there is no error for negative values. Finally, it returns the EST values in the cell.
The only challenge with this method is to format the EST values column. If you see values in decimals after you implement the formula, you must apply the formatting to these rows so that they are shown as time values.
Cool, right!? You have created a custom function to convert GMT values to EST through VBA. Pat on your back!
Also read: How To Combine Date and Time in Excel?
Convert GMT to EST using Power Query
There are many ways Power Query can be helpful to someone by allowing them to import, transform, and combine data.
In this process, during the transform phase, you can utilize its power to create a custom column using the M-code formula that converts the GMT values from one column to EST values in another column.
The data you should use for this demo is a bit tweaked. You just need the column of GMT values.
Select the entire range A1:A6 and go to the Data tab > click on the From Table/Range section placed inside the Get & Transform Data group.
Inside the Create Table window that appears next, you will see the range A1:A6 already reflecting as you selected that in the previous step.
If not selected there, you will have to select it explicitly here. Tick the option “My table has headers” so that Excel recognizes that the first row is a header from the selection. Click on the OK button.
This will launch the Power Query Editor that looks like below. You might instantly notice that the values are no longer in hh: mm AM/PM format as those were in the Excel sheet. This is because Power Query treats the time values differently and doesn’t understand the custom format you set.
To convert the values back to Time format:
Go to the Home tab > click on the Data Type: dropdown and select Time from the list.
In Power Query, each operation you perform is recorded as a step. When you initiate this step for converting column data type to Time, it will give you two options: either you can Replace the current step or Add a new step.
In this case, you don’t want to keep the decimal formatting at all, so replacing it with the step is fine.
Now, you want to add a column that does all the calculations involved in this GMT to EST conversion and return it row by row. In Power Query, you can do this using the Custom Column option through the Add Column menu.
Inside the Custom Column dialogue box that opens up, add the following formula and use EST Time as a New column name. Finally, click OK to execute it.
Here, the GMT Time column is used for this column operation, and 5 hours are subtracted from it to return the EST Time column.
Note: The most significant difference here is whatever formulas you were using in Excel were implemented row by row. But in Power Query, any formula you apply will directly create a new column by considering all rows from the reference column. In this way, you don’t have to drag the formula down across the cells to populate it for all the rows.
Now, you can see the EST Time column beside the GMT Time. But it is in the Date Time format as Power Query doesn’t deliberately transform the values into Time values.
By now, you know the drill!
Go to the Home tab > click on the Data Type: dropdown > select the Time to convert the EST Time column into time format.
Finally, click on the Close & Load dropdown from the Home tab and select the option Close & Load.
This loads the data on your sheet as a table with two columns, GMT Time and equivalent EST Time, in the column beside.
Here finally ends the article where I tried to provide you five different ways to convert GMT to EST in Excel using the simple subtraction between GMT value and time offset, Using the MOD function to create an array formula that runs for all the cells altogether, the LAMBDA function that you can customize as per your needs, The VBA code which allows you to create a custom function again like the LAMBDA, and finally the Power Query way where you can create a Custom column by defining a M-Code formula.
Please let me know through the comments section if you know of any other method for the GMT to EST conversion. It’s all yours!
Other Excel articles you may also like:
