Microsoft Office Tutorials and References

In Depth Information

Figure 6-10:
Using a formula to create a series of incremental times.

Converting between time zones

You may receive a worksheet that contains dates and times in Greenwich Mean Time (GMT, sometimes re-

ferred to as
Zulu time
), and you may need to convert these values to local time. To convert dates and times into

local times, you need to determine the difference in hours between the two time zones. For example, to convert

GMT times to U.S. Central Standard Time (CST), the hour conversion factor is –6.

You can't use the TIME function with a negative argument, so you need to take a different approach. One hour

equals 1⁄24 of a day, so you can divide the time conversion factor by 24 and then add it to the time.

Figure 6-11 shows a worksheet set up to convert dates and times (expressed in GMT) to local times. Cell B1

contains the hour conversion factor (–5 hours for U.S. Eastern Standard Time; EST). The formula in B4, which

copies down the column, is

=A4+($B$1/24)

You can download the workbook shown in Figure 6-11, gmt conversion.xlsx, from this

book's website.

This formula effectively adds
x
hours to the date and time in column A. If cell B1 contains a negative hour

value, the value subtracts from the date and time in column A. Note that, in some cases, this also affects the

date.