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 fi 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"))
Search JabSto ::




Custom Search