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.

Automatically Converting to GMT

Written by Allen Wyatt (last updated October 20, 2020)
This tip applies to Excel 2007, 2010, 2013, and 2016


12

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:

=IF(AND(B7>=$E$1,B19<=$E$2),B7+7/24,B7+8/24)

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:

=localtimetoutc(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.

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

MORE FROM ALLEN

Adding a Little Animation to Your Life

Tired of the same old boring Excel look? You can add some life to your worksheets by introducing some animation. Here's ...

Discover More

Setting Web Fonts

If you intend to generate a Web page from your document, you need to be concerned with the fonts that Word will use. ...

Discover More

Filtering Based on Comparing Two Cells

Filtering can be very helpful in allowing you to see only those data records that meet certain criteria. In this tip you ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Entering Large Time Values

If you need to input humongous times into a worksheet, you may run into a problem if you need to enter times greater than ...

Discover More

Converting UTC Times to Local Times

Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your ...

Discover More

Dealing with Large Numbers of Seconds

When adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 five more than 3?

2021-12-07 18:16:28

Nick T

I can't get your function to work correctly. I am in Pacific zone with DST. The function is not giving any DST values (+7) only PST (+8). Any other settings needed?


2020-10-27 09:39:46

Neil Mark

GMT is not an acronym, it's the initials of Greenwich Mean Time.


2020-10-20 08:51:40

Rich Furner

GMT stands for Greenwich Mean Time, not Meridian time. Also, the time in England is GMT only during the winter months. During the summer months it is BST (British Summer Time) = GMT + 1 = UTC + 1. So, today, our time in England is not GMT. It is GMT + 1.


2018-10-15 11:37:19

Andreas Scholz

Hi Allen!

Super post btw.

But I have a question: what do I need to change, in your code, in order to change a UTC/GMT variable into GMT+1?

Do you have any suggestions or do I need to use SystemTimeToTzSpecificLocalTime or something like it...

Best regards

Andreas


2017-04-10 06:12:24

Kari

Where did 'B19' come from in the formulas above? What is supposed to be contained in B19, I thought the date was in B7?
"=IF(AND(B7>=$E$1,B19<=$E$2),B7+7/24,B7+8/24)"


2017-04-09 17:22:36

Ross Marsden

To perform conversion to or from UTC, I use a lookup table to obtain the UTC offset.
The table contains the Daylight Saving change time (col 1) and the new offset (col 2).

A formula like =LocalTime+time(vlookup(LocalTime,DLSChangeTable,2,TRUE),0,0) does the change.

I only need to remember to keep my DLSChangeTable tilled in with plenty of years behind and ahead depending on the dates I need to be converting.


2017-04-09 17:09:52

Ross Marsden

Actually UTC is universal, and the times in the time zones are constructs. ;-)


2017-04-09 10:37:39

Mike

GMT or UTC are constructs. The time in Greenwich as this is written is BST - British Summer Time.


2017-04-08 14:28:18

Jerry

That should have been "day 0, January 0, 1900"! :)


2017-04-08 14:26:03

Jerry

"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."
I don't believe this statement is accurate, unless this has changed in the newer versions of Excel (I have Excel 2010). When I enter 10:15 into a cell and convert the entry to "General" format, I see 0.427083333333333, and if I convert it to "Short date" format, I get 1/0/1900 10:15:00 AM. So the date is defaulting to day 0, January 1, 1900, unless I enter a specific date.


2017-04-08 11:50:29

Chris Finn

Actually, i believe GMT is short for Greenwich MEAN TIme.
The Mean bit refers to not correcting for the earth's tilt and it's elliptical orbit, so all days are the same mean length (leap seconds excepted).

Cheers,
Chris (in UK).


2017-04-08 08:50:22

Theo Deed

Greenwich Mean Time (GMT). But nowadays we use Coordinated Universal Time (UTC). Z is the time zone.


This Site

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.

Newest Tips
Subscribe

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.