Solved: Timesheet Weekly Totals Help

[canselmi]

This is going to be difficult to explain. Here goes. I have a summary sheet with accumulated time for the week. For simplicity I'm using A1:A4 for this example:

Cell A1 = Hours worked (hh:mm)
Cell A2 = Paid time off (hh:mm)

Cell A3 = Total Regular Time (hh:mm)

Cell A4 = Total Overtime hours (hh:mm)

Here's the issue. Only "Worked" hours over 40 hours is considered Overtime, unless there's paid time off. An employee could actually have a total of 44 hours, but took 4 hours of Paid time off. So all 44 hours would be paid at regular time. Another example would be an employee worked 45 hours, but 4 hours were taken as paid time off, so:

Cell A3 (Regular Time) should equal 44 hours (but shown in minutes)
Cell A4 (Overtime) should equal 1 hour (but shown in minutes)

I don't think a formula will can do what I need and would like to see if vba can do it.

[Aussiebear]

This would be a piece of cake to Bob... if he was awake.

[Aussiebear]

This is as close as I can come. It may give you an idea.

[Bob Phillips]

Formulae do it stnding on their heads

Total mins regular

=(MIN(A1,1+TIME(16,0,0))+A2)*24*60

Total mins overtime

=(((A1-A2)*24>40)*((A1-A2)*24*60-2400))

[Bob Phillips]

The second can be simplified

=MAX(0,(A1-A2)*24-40)*60

[Aussiebear]

Geez Bob, a shot across the bow would have been nice...... I got to return this ship to the owners in the morning ...... how am I going to explain the the hole amidships?