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)

Search JabSto ::

Custom Search