Timesheet

[gport]

I have 6 worksheets in my workbook
Sheets 1-5 is the time sheet that looks like this
In out In out Ttl
8:00 12:00 11:00 5:00 8 (formula here to
calculate)

Then the sixth is a weekly total that looks like this

Name Reg Hr OT Total
45 (Formula Calculates)

Now I would like for excel to auto seperate the 45 hours
to 40 in Rer Hr column and 5 in OT column, but if there is
no overtime put a 0 (Or nothing) in OT column and whatever
the reg hrs are in the Reg Hr column.

Is this possible?

 

[Peo Sjoblom]

A2 = in, B2 out, c2 in and D2 out, to get the hours for this day

=(D2-A2-(C2-B2))*24

format as general

for the totals non OT

=MIN(SUM(F2:F6),40)

for OT

=MAX(SUM(F2:F6)-40,0)

if you want to use time formats

format as [hh}:mm

=D2-A2-(C2-B2)

totals non OT

=MIN(SUM(F2:F6),"40:00")

OT

=MAX(SUM(F2:F6)-"40:00","00:00")

 

[c]

On the 6th worksheet, is it only the Total column that has
a formula and is the formula pointing to the other
worksheets? If yes, what you can do is in the OT column,
do an if formula. In the "OT" column (for example is col
C), in cell C2 (which is the cell directly below the OT
column header, use this formula "=if(d2>40, 40-d2,0)".
This will perform the formula if the value on it's "total"
column is greater than 40. Then in cell b2 (cell below
the Reg Hrs header, just do total minus OT.

 

[ray]

In the OT column (assuming c2) =if(d2>40,+d2-40,0)
In the reg column +d2-c2

 

[Guest]

Thanks to All... very helpful!