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.
Search JabSto ::

Custom Search