timesheet
 

Hi, am trying to finalise a time sheet and can't figure out how to make it
"automatic". Here's what I'm trying to achieve:
In F5 I have "Total hours worked"
I'd like to take the figure at F5 and automatically split it into the
following:

"On Weekdays"

H5 - Ordinary time (a maximum of 8 hours)
I5 - Time and a half (a maximum of 3 hours)
J5 - Double time (any hours worked after both the above)

"On Weekends"

I10 - Time and a half (1st 3 hours, they are paid a minimum of 3 hours)
J10 - Double time (after 1st 3 hours)

Many thanks
Andy

timesheet
 

Presumably you have dates in there somewhere that you can use to
determine which of those hours are worked at weekends and which were
on weekdays? Perhaps you could describe your data layout more fully.

Pete

On Sep 23, 11:20*am, "Andy Bolger" wrote:
Hi, am trying to finalise a time sheet and can't figure out how to make it
"automatic". Here's what I'm trying to achieve:
In F5 I have "Total hours worked"
I'd like to take the figure at F5 and automatically split it into the
following:

"On Weekdays"

H5 *- Ordinary time (a maximum of 8 hours)
I5 - Time and a half (a maximum of 3 hours)
J5 - Double time (any hours worked after both the above)

"On Weekends"

I10 - Time and a half (1st 3 hours, they are paid a minimum of 3 hours)
J10 - Double time (after 1st 3 hours)

Many thanks
Andy

timesheet
 

From your post I deducted that overtime doesn't depend on that worked hours
were done day or night. If so, try these formulae:

"On Weekdays"

H5: =MIN(8,F5)
I5: =MAX(0,MIN(3,F5-8))
J5 =MAX(0,F5-11)

"On Weekends"

I10: =IF(F5=0,0,MIN(3,MAX(3,F5)))
J10: =MAX(0,F5-3)

Regards,
Stefi


€žAndy Bolger€� ezt Ã*rta:


Hi, am trying to finalise a time sheet and can't figure out how to make it
"automatic". Here's what I'm trying to achieve:
In F5 I have "Total hours worked"
I'd like to take the figure at F5 and automatically split it into the
following:

"On Weekdays"

H5 - Ordinary time (a maximum of 8 hours)
I5 - Time and a half (a maximum of 3 hours)
J5 - Double time (any hours worked after both the above)

"On Weekends"

I10 - Time and a half (1st 3 hours, they are paid a minimum of 3 hours)
J10 - Double time (after 1st 3 hours)

Many thanks
Andy