Excel 2010 Bible

Chapter 12: Working with Dates and Times

277

This worksheet assumes that hours in excess of 40 hours in a week are considered overtime hours.
The worksheet contains a cell named Overtime, in cell C23. This cell contains a formula that
returns 40:00. If your standard workweek consists of something other than 40 hours, you can
change this cell.

The following formula (in cell H18) calculates regular (nonovertime) hours. This formula returns
the smaller of two values: the total hours or the overtime hours.

=MIN(E17,Overtime)

The final formula, in cell H19, simply subtracts the regular hours from the total hours to yield the
overtime hours.

=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

Converting from military time .................................................................................

Military time is expressed as a four-digit number from 0000 to 2359. For example, 1:00 a.m. is
expressed as 0100 hours, and 3:30 p.m. is expressed as 1530 hours. The following formula con-
verts such a number (assumed to be 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 formula corrects the problem, and it returns a valid time for any military time value from
0 to 2359 :

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

Following is a simpler formula that uses the TEXT function to return a formatted string, and then
it 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 con-
tains 9.25 (representing hours), this formula returns 09:15:00 (nine hours, 15 minutes):

=A1/24