Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Converting to GMT.
by Allen Wyatt
(last updated April 8, 2017)
GMT is an acronym for Greenwich Meridian Time, which is a reference time for the world; it is the time in Greenwich, England, and is sometimes referred to as "Zulu time." (This is particularly true in aviation lingo. The GMT time zone is often abbreviated as "Z," whose phonetic name is Zulu.)
You may have a need to convert a local time to GMT in your worksheet. If you always know that the time will be entered in local time, this can be done quite easily with a formula. For instance, assume that you are entering the local time in cell B7, and that you are in the Pacific time zone. In this time zone, you are either seven or eight hours behind GMT, depending on if daylight savings time is in effect. The following formula will adjust the time entered in B7 by either seven or eight hours, depending on whether the date associated with the time is within the period of daylight savings time.
=IF(AND(B7>=DATEVALUE("3/8/2009 02:00"),B19<= DATEVALUE("11/01/2009 02:00")),B7+7/24,B7+8/24)
Remember that whenever you enter a time into a cell, Excel automatically attaches a date to it. Thus, if you enter a time of 10:15 into a cell, and the day you make the entry is January 17, then Excel automatically converts the entry in the cell to 01/17/2009 10:15:00. This is done even though you may only be displaying the time in the cell—in Excel, every date has a time associated with it, and every time has a date associated with it.
Because of this entry behavior, Excel would use the formula just shown to do the proper adjustment based on the default date when you enter a time (today's date) or a date you may explicitly enter.
The only drawback to this formulaic approach is that you must remember to change the daylight savings time boundary dates from year to year. (The ones in the formula are for 2009.) You could change the formula so that you actually stored the boundary dates in cells, such as E1 and E2, as follows:
While the formula is shorter, it still has a problem with the rather static determination of when daylight savings time begins and ends—you must remember to update that information manually. In addition, if you move to a different time zone, you must remember to modify the values by which the date and time are adjusted.
A really handy way around these drawbacks is to create a user-defined function that accesses the Windows interface and determines what the system settings are in your computer. Your system keeps track of daylight savings time automatically, as well as which time zone you are in. Accessing this information through a user-defined function means you will never need to worry about those items in your worksheet. You can use the following macro to do just that:
Option Explicit Public Declare Function SystemTimeToFileTime Lib _ "kernel32" (lpSystemTime As SYSTEMTIME, _ lpFileTime As FILETIME) As Long Public Declare Function LocalFileTimeToFileTime Lib _ "kernel32" (lpLocalFileTime As FILETIME, _ lpFileTime As FILETIME) As Long Public Declare Function FileTimeToSystemTime Lib _ "kernel32" (lpFileTime As FILETIME, lpSystemTime _ As SYSTEMTIME) As Long Public Type FILETIME dwLowDateTime As Long dwHighDateTime As Long End Type Public Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End Type Public Function LocalTimeToUTC(dteTime As Date) As Date Dim dteLocalFileTime As FILETIME Dim dteFileTime As FILETIME Dim dteLocalSystemTime As SYSTEMTIME Dim dteSystemTime As SYSTEMTIME dteLocalSystemTime.wYear = CInt(Year(dteTime)) dteLocalSystemTime.wMonth = CInt(Month(dteTime)) dteLocalSystemTime.wDay = CInt(Day(dteTime)) dteLocalSystemTime.wHour = CInt(Hour(dteTime)) dteLocalSystemTime.wMinute = CInt(Minute(dteTime)) dteLocalSystemTime.wSecond = CInt(Second(dteTime)) Call SystemTimeToFileTime(dteLocalSystemTime, _ dteLocalFileTime) Call LocalFileTimeToFileTime(dteLocalFileTime, _ dteFileTime) Call FileTimeToSystemTime(dteFileTime, dteSystemTime) LocalTimeToUTC = CDate(dteSystemTime.wMonth & "/" & _ dteSystemTime.wDay & "/" & _ dteSystemTime.wYear & " " & _ dteSystemTime.wHour & ":" & _ dteSystemTime.wMinute & ":" & _ dteSystemTime.wSecond) End Function
This may look imposing, as is often the case when working with system calls, but it works wonderfully. There are three system routines referenced (SystemTimeToFileTime, LocalFileTimeToFileTime, and FileTimeToSystemTime). By setting up the calls and using them in order, the date and time are automatically adjusted to GMT. To use the function, in your worksheet you would enter this to convert the time in cell B7:
Format the cell as date/time, and the output is exactly what you wanted.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12238) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Automatically Converting to GMT.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would ...Discover More
Do you need to enter negative times into a worksheet? Excel doesn't really provide a way to do that, but understanding ...Discover More
When you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead ...Discover More
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.