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):