Basic Overtime/Timesheet Formula

[visibleman]

Hi all,
I have a spreadsheet with columns B (Date), C (Time In, 24hrs), D (Time Out, 24hrs), E (Over/Under Time) and a cell that displays the total amount of overtime.

Does anyone know of a formula that will calculate over/undertime (over/under the standard 8hr day) and another that will sum column E to give a total?

From reading around i've managed to be able to display the overtime (both in time format and decimal format) but from what i gather excel doesn't handle negative time (ie: undertime), hence why you have to use decimals instead. It's the negative time that i get stuck with as if i've left work 5minutes early, the decimal displays -0.45, yet 20minutes over is displayed as 0.20 (which doesn't seem to make sense to me).

The other problem i have is that sometimes i can work a until the early hours (8am until 2am the next morning) and this obviously causes errors.

Any help will be greatly appreciated,
Dom

[robert111]

enter the times as dd/mm/yy hh:mm format, then subtracting earlier from later will give you a NUMBER which is days in decimal, just multiply that by 24 to give hours.

If you only recognise, say 15 minute intervals, set up a lookup table to convert, say, 8.63 hours to 8 hours 30 minutes

[robert111]

then compare your answer with a standard shift length, presumably 8 hours