I have a time sheet that was working great, until I needed to figure night shift hours.
I have columns for clock in/out and lunch in/out. I also need columns to show the number of regular (normal pay) hours and the number of OT hours.
C = clock in, D = clock out lunch, E = clock in lunch, F = clock out.
If statements were used to designate 8 hours for the Regular column:
=IF(((D12-C12)+(F12-E12))*24>8,8,((D12-C12)+(F12-E12))*24)
And also for the OT column:
=IF(((D12-C12)+(F12-E12))*24>8,((D12-C12)+(F12-E12))*24-8,0)
These work great for the way the sheet is setup, but do not play nicely with night shift hours. I need to have a formula that works either way so that I can just plug in the times. I have searched around a bit and tried a few things, but none seem to work in both cases. I'm sure there's a nested +1 or something way to do this, but it eludes me.
Anyone have ideas?
I have columns for clock in/out and lunch in/out. I also need columns to show the number of regular (normal pay) hours and the number of OT hours.
C = clock in, D = clock out lunch, E = clock in lunch, F = clock out.
If statements were used to designate 8 hours for the Regular column:
=IF(((D12-C12)+(F12-E12))*24>8,8,((D12-C12)+(F12-E12))*24)
And also for the OT column:
=IF(((D12-C12)+(F12-E12))*24>8,((D12-C12)+(F12-E12))*24-8,0)
These work great for the way the sheet is setup, but do not play nicely with night shift hours. I need to have a formula that works either way so that I can just plug in the times. I have searched around a bit and tried a few things, but none seem to work in both cases. I'm sure there's a nested +1 or something way to do this, but it eludes me.
Anyone have ideas?