More complex overtime question

Any hours past 8 in a day is paid at 1.5
Any hours past 12 is paid at 2.0, except on Sat and Sun
Any hours over 40 in a week.
On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
is 2.0.

I am having the most trouble with combining the 40 hour work rule and the
sixth consecutive day rule. Can anyone help with the formula's?

Hi Kim,
Can you give some more info on how your spreadsheet is set up?
where/how is the date and number of hours stored?
- Search

"Kim DuBray" wrote:

> Any hours past 8 in a day is paid at 1.5
> Any hours past 12 is paid at 2.0, except on Sat and Sun
> Any hours over 40 in a week.
> On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
> If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
> the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
> paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
> is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
> is 2.0.
>
> I am having the most trouble with combining the 40 hour work rule and the
> sixth consecutive day rule. Can anyone help with the formula's?

"Search33" wrote:

> Hi Kim,
> Can you give some more info on how your spreadsheet is set up?
> where/how is the date and number of hours stored?
> - Search
>
> "Kim DuBray" wrote:
>
> > Any hours past 8 in a day is paid at 1.5
> > Any hours past 12 is paid at 2.0, except on Sat and Sun
> > Any hours over 40 in a week.
> > On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
> > If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
> > the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
> > paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
> > is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
> > is 2.0.
> >
> > I am having the most trouble with combining the 40 hour work rule and the
> > sixth consecutive day rule. Can anyone help with the formula's?

Currently I am using numbers with a sample as follows

A B C D E F G H
I J
1 REG HR MAX/OT MAX/WK MAX 8 12 40
2 IN OUT IN OUT TL HRS WRKD HRS REG
HRS OT HRS

3 MON DATE 5 11 12 18 12 12
8 4
4 TUES
5 WED
6 THUR
7 FRI
8 SAT
9 SUN
The formula for mon-fri reg hrs is- min($g$1,g3)
The formula for ot is- if((g3>8,min($h$1,g3-i3),0)
The formula for Sat reg is- if(h7:h3)<40,min(g1,40-sum(h7:h3),0)

The Sat ot is giving me trouble under certain scenarios. This is confusing,
is there a way to attach the spreadsheet?


"Kim DuBray" wrote:

>
>
> "Search33" wrote:
>
> > Hi Kim,
> > Can you give some more info on how your spreadsheet is set up?
> > where/how is the date and number of hours stored?
> > - Search
> >
> > "Kim DuBray" wrote:
> >
> > > Any hours past 8 in a day is paid at 1.5
> > > Any hours past 12 is paid at 2.0, except on Sat and Sun
> > > Any hours over 40 in a week.
> > > On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
> > > If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
> > > the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
> > > paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
> > > is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
> > > is 2.0.
> > >
> > > I am having the most trouble with combining the 40 hour work rule and the
> > > sixth consecutive day rule. Can anyone help with the formula's?

That example didn't format the way it should have. It is confusing.
Basically the reg hrs calc on mon - fri is the min of ttl hrs worked and 8
(the standard max reg hrs per day). the ot calc on mon-fri is- if ttl hrs per
day is greater than 8 then the minimum of ttl hrs- reg hrs or the standard ot
max per day (4) is the result. Double time is ttl hrs less reg hrs less ot
hrs.

The ot for sat is where it is tricky. For sat reg hrs if the prev worked
hrs for mon-fri are less than 40 then reg hrs is the lesser of ttl hrs and
40-sum of prev worked hrs. The sat ot calc is- ttl hrs - reg hrs up to a max
of 12ot hrs. If the prev worked hrs are greater than 40 then the ot calc is
the lesser of 12 and the ttl hours worked.

Is there a way to attach my timesheet for you to view?

"Kim DuBray" wrote:

> Currently I am using numbers with a sample as follows
>
> A B C D E F G H
> I J
> 1 REG HR MAX/OT MAX/WK MAX 8 12 40
> 2 IN OUT IN OUT TL HRS WRKD HRS REG
> HRS OT HRS
>
> 3 MON DATE 5 11 12 18 12 12
> 8 4
> 4 TUES
> 5 WED
> 6 THUR
> 7 FRI
> 8 SAT
> 9 SUN
> The formula for mon-fri reg hrs is- min($g$1,g3)
> The formula for ot is- if((g3>8,min($h$1,g3-i3),0)
> The formula for Sat reg is- if(h7:h3)<40,min(g1,40-sum(h7:h3),0)
>
> The Sat ot is giving me trouble under certain scenarios. This is confusing,
> is there a way to attach the spreadsheet?
>
>
> "Kim DuBray" wrote:
>
> >
> >
> > "Search33" wrote:
> >
> > > Hi Kim,
> > > Can you give some more info on how your spreadsheet is set up?
> > > where/how is the date and number of hours stored?
> > > - Search
> > >
> > > "Kim DuBray" wrote:
> > >
> > > > Any hours past 8 in a day is paid at 1.5
> > > > Any hours past 12 is paid at 2.0, except on Sat and Sun
> > > > Any hours over 40 in a week.
> > > > On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
> > > > If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
> > > > the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
> > > > paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
> > > > is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
> > > > is 2.0.
> > > >
> > > > I am having the most trouble with combining the 40 hour work rule and the
> > > > sixth consecutive day rule. Can anyone help with the formula's?

If you would like, you can e-mail me your spreadsheet
Search33_98(at)hotmail.com

"Kim DuBray" wrote:

> That example didn't format the way it should have. It is confusing.
> Basically the reg hrs calc on mon - fri is the min of ttl hrs worked and 8
> (the standard max reg hrs per day). the ot calc on mon-fri is- if ttl hrs per
> day is greater than 8 then the minimum of ttl hrs- reg hrs or the standard ot
> max per day (4) is the result. Double time is ttl hrs less reg hrs less ot
> hrs.
>
> The ot for sat is where it is tricky. For sat reg hrs if the prev worked
> hrs for mon-fri are less than 40 then reg hrs is the lesser of ttl hrs and
> 40-sum of prev worked hrs. The sat ot calc is- ttl hrs - reg hrs up to a max
> of 12ot hrs. If the prev worked hrs are greater than 40 then the ot calc is
> the lesser of 12 and the ttl hours worked.
>
> Is there a way to attach my timesheet for you to view?
>
> "Kim DuBray" wrote:
>
> > Currently I am using numbers with a sample as follows
> >
> > A B C D E F G H
> > I J
> > 1 REG HR MAX/OT MAX/WK MAX 8 12 40
> > 2 IN OUT IN OUT TL HRS WRKD HRS REG
> > HRS OT HRS
> >
> > 3 MON DATE 5 11 12 18 12 12
> > 8 4
> > 4 TUES
> > 5 WED
> > 6 THUR
> > 7 FRI
> > 8 SAT
> > 9 SUN
> > The formula for mon-fri reg hrs is- min($g$1,g3)
> > The formula for ot is- if((g3>8,min($h$1,g3-i3),0)
> > The formula for Sat reg is- if(h7:h3)<40,min(g1,40-sum(h7:h3),0)
> >
> > The Sat ot is giving me trouble under certain scenarios. This is confusing,
> > is there a way to attach the spreadsheet?
> >
> >
> > "Kim DuBray" wrote:
> >
> > >
> > >
> > > "Search33" wrote:
> > >
> > > > Hi Kim,
> > > > Can you give some more info on how your spreadsheet is set up?
> > > > where/how is the date and number of hours stored?
> > > > - Search
> > > >
> > > > "Kim DuBray" wrote:
> > > >
> > > > > Any hours past 8 in a day is paid at 1.5
> > > > > Any hours past 12 is paid at 2.0, except on Sat and Sun
> > > > > Any hours over 40 in a week.
> > > > > On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
> > > > > If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
> > > > > the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
> > > > > paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
> > > > > is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
> > > > > is 2.0.
> > > > >
> > > > > I am having the most trouble with combining the 40 hour work rule and the
> > > > > sixth consecutive day rule. Can anyone help with the formula's?

I've emailed you. Let me know if you don't get it, please.

"Kim DuBray" wrote:

> Any hours past 8 in a day is paid at 1.5
> Any hours past 12 is paid at 2.0, except on Sat and Sun
> Any hours over 40 in a week.
> On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5.
> If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until
> the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are
> paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it
> is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8
> is 2.0.
>
> I am having the most trouble with combining the 40 hour work rule and the
> sixth consecutive day rule. Can anyone help with the formula's?