Forum Discussion
excel
hello how do i make a function in Excel minus a certain number of twelve working hours? i need to know an employee's overtime hours
Let's say start time is in B2 and end time in C2.
If regular working hours are up to 12 hours, the formula for regular working time is
=MIN(MOD(C2-B1,1),TIME(12,0,0))
and that for overtime
=MAX(MOD(C2-B2,1)-TIME(12,0,0),0)
Format the cells with these formulas as h:mm.
If you want the result as decimal hours, for example 8.5 instead of 8:30, use
=24*MIN(MOD(C2-B1,1),TIME(12,0,0))
and
=24*MAX(MOD(C2-B2,1)-TIME(12,0,0),0)
and format the cells with these formulas as General or as Number.