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
1 Reply
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.
