Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

You can use the TIME function to fill a range of cells with incremental times. Figure 6-9 shows a

worksheet with a series of times in ten-minute increments. Cell A1 contains a time that was

entered directly. Cell A2 contains the following formula, which was copied down the column:

=A1+TIME(0,10,0)

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

You can also use the Excel AutoFill feature to fill a range with times. For example, to

create a series of times with ten-minute increments, type 8:00 AM in cell A1 and 8:10

AM in cell A2. Select both cells, and then drag the fill handle (in the lower-right corner

of cell A2) down the column to create the series.

Converting between time zones

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

sometimes referred 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-10 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)