Microsoft Office Tutorials and References

In Depth Information

**Converting from military time**

from the time in column G to get the total hours worked after lunch. I use
IF
functions to

accommodate graveyard shift cases that span midnight — for example, an employee may

start work at 10:00 p.m. and begin lunch at 2:00 a.m. Without the
IF
function, the formula

returns a negative result.

16

The following formula in cell H17 calculates the weekly total by summing the daily totals in

column H:

=SUM(H8:H14)

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 ﬁ nail formula, in cell H19, simply subtracts the regular hours from the total hours to

yield the overtime hours:

=E17-E18

The times in the range H17:H19 and cell C23 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

converts 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 the
TIMEVALUE
function to express the result in terms of a time:

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