Microsoft Office Tutorials and References
In Depth Information
=E17-E18
The times in H17:H19 may display time values that exceed 24 hours, so these cells use a custom number
format:
[h]:mm
The workbook shown in Figure 6-9, time sheet.xlsm, is available at this book's website.
Converting from military time
Military time is expressed as a four-digit number from 0000 to 2359. For example, 1:00 AM is expressed as
0100 hours, and 3:30 PM is expressed as 1530 hours. The following formula converts such a number (assumed
to appear in cell A1) to a standard time:
=TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2))
The formula returns an incorrect result if the contents of cell A1 do not contain four digits. The following for-
mula 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 (rep-
resenting hours), this formula returns 09:15:00 (9 hours, 15 minutes):
=A1/24
To convert decimal minutes to a time, divide the decimal minutes 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 seconds 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): Search JabSto ::

Custom Search