Excel 2010 Bible

Part II: Working with Formulas and Functions

278

To convert decimal minutes to a time, divide the decimal hours by 1,440 (the number of minutes

in a day). For example, if cell A1 contains 500 (representing minutes), the following formula

returns 08:20:00 (eight hours, 20 minutes):

=A1/1440

To convert decimal seconds to a time, divide the decimal hours by 86,400 (the number of seconds

in a day). For example, if cell A1 contains 65,000 (representing seconds), the following formula

returns 18:03:20 (18 hours, three minutes, and 20 seconds):

=A1/86400

Adding hours, minutes, or seconds to a time ...........................................................

You can use the TIME function to add any number of hours, minutes, or seconds to a time. For

example, assume that cell A1 contains a time. The following formula adds 2 hours and 30 minutes

to that time and displays the result:

=A1+TIME(2,30,0)

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

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

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

=A1+TIME(0,10,0)

FIGURE 12.10

Using a formula to create a series of incremental times.