Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

The formula returns an incorrect result if the contents of cell A1 do not contain four digits. The

following formula corrects the problem and returns a valid time for any military time value from

0 to 2359:

=TIMEVALUE(LEFT(TEXT(A1,”0000”),2)&”:”&RIGHT(A1,2))

The following is a simpler formula that uses the TEXT function to return a formatted string and

then uses the TIMEVALUE function to express the result in terms of a time:

=TIMEVALUE(TEXT(A1,”00\:00”))

Converting decimal hours, minutes, or seconds to a time

To convert decimal hours to a time, divide the decimal hours by 24. For example, if cell A1

contains 9.25 (representing hours), this formula returns 09:15:00 (9 hours, 15 minutes):

=A1/24

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 (8 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, 3 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 two hours and 30

minutes to that time and displays the result:

=A1+TIME(2,30,0)