Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Converting to GMT.

Automatically Converting to GMT

by Allen Wyatt
(last updated May 19, 2016)


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." (Zulu is the phonetic name for zero, and the zero refers to the longitude of Greenwich, England.)

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 _

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

    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, _
    Call LocalFileTimeToFileTime(dteLocalFileTime, _
    Call FileTimeToSystemTime(dteFileTime, dteSystemTime)

    LocalTimeToUTC = CDate(dteSystemTime.wMonth & "/" & _
      dteSystemTime.wDay & "/" & _
      dteSystemTime.wYear & " " & _
      dteSystemTime.wHour & ":" & _
      dteSystemTime.wMinute & ":" & _
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 (2185) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Converting to GMT.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Correcting a Capital Mistake

As you are entering data in a worksheet, Excel can monitor what you type and make corrections for common mistakes. One such ...

Discover More

Tying Workbooks Together

If you work with multiple workbooks at the same time, you might wonder how to tie them together so they open and close and ...

Discover More

Locking All Non-Empty Cells

Need to make sure that your worksheet is locked, with only the blank cells accessible to editing? You can do this easily by ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Working with Imperial Linear Distances

Excel works with decimal values very easily. It is more difficult for the program to work with non-decimal values, such as ...

Discover More

Converting Mainframe Date Formats

Different industries and different computer systems specify dates in all sorts of strange ways. If you need to convert a date ...

Discover More

Converting to ASCII Text

When you work with imported or pasted data in an Excel worksheet, you may see some strange looking characters at times. If ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 8?

2016-04-21 13:31:20


I believe a mistake was made on the first conversion formula. GMT is either 7 or 8 hours behind GMT. So the formula needs to change out B17+7/24 to B17-7/24.

2014-12-17 09:41:40


My spreadsheet converts local time to GMT I would like it to retain the date and time in local time. How can I convert GMT time back to local date and time I am in U.S Central Time Zone. Thanks

2014-04-19 10:27:07


The domain has expired. Anyone know if the code that was at is available elsewhere?

2013-09-10 07:22:46

Michael (Micky) Avidan

You may considering to check out the code in:

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)

2013-09-09 09:28:05


to avoid the regional issues, there is no need to convert from a string. Suggest as follows:
Dim dt As Date
Dim t As Date
Dim finalDate As Date


dt = DateSerial(dteSystemTime.wYear, dteSystemTime.wMonth, dteSystemTime.wDay)
t = TimeSerial(dteSystemTime.wHour, dteSystemTime.wMinute, dteSystemTime.wSecond)

finalDate = dt + t

LocalTimeToUTC = finalDate

2013-05-21 11:21:50

Other Dave

The last part of this caused us trouble when we tried a global roll-out. As the first Dave suggested - the ordering of Day and Month shown will only work on computers with US Date formats selected.

I suggest using the following last line instead - which will work with any date format:
LocalTimeToUTC = CDate(dteSystemTime.wYear & "-" & _
dteSystemTime.wMonth & "-" & _
dteSystemTime.wDay & " " & _
dteSystemTime.wHour & ":" & _
dteSystemTime.wMinute & ":" & _

2013-02-01 06:12:53


The construction at the end of the LocalTimeToUTC function needs to take account of regional dd/mm ordering - for example, in the UK, the first part of the assignment needs to be:

LocalTimeToUTC = CDate( _
dteSystemTime.wDay & "/" & _
dteSystemTime.wMonth & "/" & _
dteSystemTime.wYear & " " & _ ... etc.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.