I do have a special case when it comes to calculate overtime, I think.

The case is this:
Company rules says that you are not supposed to have any extra % for
overtime
before you have 37.5 hrs a week. Then, if you work overtime you get 50% and
if
you work moore than 4 hours overtime one day you get a double50 %. Say
one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs regular
time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs
overtime.
This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra.

But when a person works only 80% or 4 days, he first have to work 7,5 hrs
overtime
that week to have full time job. After that, overtime is rewarded with 50%
extra or
100% extra if moore than 4 hrs same day.

What I want formulas for is: 0% 50% 100%
0% is for overtime hours up to 37.5 hours a week, then 50% for additional
overtime
and so on.

For full time workers working 8.5 hours overtime:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 0 4 1.5
5/2/2007 16:00 19:00 0 3 0

if part time (80%) this example should look like this:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 5.5 0 0
5/2/2007 16:00 19:00 2 1 0

The formulas must be able to handle both examples in one solution if
possible.

Any good suggestions for good formulas is very much appreaciated!

Regards
Jan T.

Jan,

With the Hours Worked for the day in Column D, 0% in Column E, 50% in Column
F, 100% in Column G and the days of the week, Monday to Sunday ,in Rows 4:10
then:

0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)
50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)
100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

and copy down to row 10.

It would be possible to miss out the Hours Worked column and calculate the
hours in the forumulas but it would make then considerably longer.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Jan T." wrote in message
...
I do have a special case when it comes to calculate overtime, I think.

The case is this:
Company rules says that you are not supposed to have any extra % for
overtime
before you have 37.5 hrs a week. Then, if you work overtime you get 50%
and if
you work moore than 4 hours overtime one day you get a double50 %. Say
one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs
regular
time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs
overtime.
This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra.

But when a person works only 80% or 4 days, he first have to work 7,5 hrs
overtime
that week to have full time job. After that, overtime is rewarded with 50%
extra or
100% extra if moore than 4 hrs same day.

What I want formulas for is: 0% 50% 100%
0% is for overtime hours up to 37.5 hours a week, then 50% for additional
overtime
and so on.

For full time workers working 8.5 hours overtime:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 0 4 1.5
5/2/2007 16:00 19:00 0 3 0

if part time (80%) this example should look like this:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 5.5 0 0
5/2/2007 16:00 19:00 2 1 0

The formulas must be able to handle both examples in one solution if
possible.

Any good suggestions for good formulas is very much appreaciated!

Regards
Jan T.

This works great the first rows, but when I come to one of the last rows
and I have already worked more than 37.5 hrs, it still returns some 0%
hours.

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra day,
that should give me 7.5 hrs with 0% extra.

Every hour worked after full time job (37.5 hrs) shall give at least 50%
extra pr hrs.
If I work 37.5 hours Monday through Friday, all overtime on Saturday will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on,
(because it is less this day than 4 hours = no 100% extra here).

Can this too be calculated in one formula?
Thanks very much for helping!

Jan




"Sandy Mann" skrev i melding
...

Jan,

With the Hours Worked for the day in Column D, 0% in Column E, 50% in
Column F, 100% in Column G and the days of the week, Monday to Sunday ,in
Rows 4:10 then:

0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)
50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)
100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

and copy down to row 10.

It would be possible to miss out the Hours Worked column and calculate the
hours in the forumulas but it would make then considerably longer.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Jan T." wrote in message
...
I do have a special case when it comes to calculate overtime, I think.

The case is this:
Company rules says that you are not supposed to have any extra % for
overtime
before you have 37.5 hrs a week. Then, if you work overtime you get 50%
and if
you work moore than 4 hours overtime one day you get a double50 %. Say
one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs
regular
time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs
overtime.
This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra.

But when a person works only 80% or 4 days, he first have to work 7,5 hrs
overtime
that week to have full time job. After that, overtime is rewarded with
50% extra or
100% extra if moore than 4 hrs same day.

What I want formulas for is: 0% 50% 100%
0% is for overtime hours up to 37.5 hours a week, then 50% for additional
overtime
and so on.

For full time workers working 8.5 hours overtime:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 0 4 1.5
5/2/2007 16:00 19:00 0 3 0

if part time (80%) this example should look like this:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 5.5 0 0
5/2/2007 16:00 19:00 2 1 0

The formulas must be able to handle both examples in one solution if
possible.

Any good suggestions for good formulas is very much appreaciated!

Regards
Jan T.

Hi Jan,

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra
day,
that should give me 7.5 hrs with 0% extra.

That is what I get.

If I work 37.5 hours Monday through Friday, all overtime on Saturday will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

change the formulas to:

0% Formula:
=IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4))
50% Formula:
=IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0))
100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

If it is still not right then post back or send me your e-mail address and I
will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Jan T." wrote in message
...
This works great the first rows, but when I come to one of the last rows
and I have already worked more than 37.5 hrs, it still returns some 0%
hours.

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra
day,
that should give me 7.5 hrs with 0% extra.

Every hour worked after full time job (37.5 hrs) shall give at least 50%
extra pr hrs.
If I work 37.5 hours Monday through Friday, all overtime on Saturday will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on,
(because it is less this day than 4 hours = no 100% extra here).

Can this too be calculated in one formula?
Thanks very much for helping!

Jan




"Sandy Mann" skrev i melding
...

Jan,

With the Hours Worked for the day in Column D, 0% in Column E, 50% in
Column F, 100% in Column G and the days of the week, Monday to Sunday ,in
Rows 4:10 then:

0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)
50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)
100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

and copy down to row 10.

It would be possible to miss out the Hours Worked column and calculate
the hours in the forumulas but it would make then considerably longer.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Jan T." wrote in message
...
I do have a special case when it comes to calculate overtime, I think.

The case is this:
Company rules says that you are not supposed to have any extra % for
overtime
before you have 37.5 hrs a week. Then, if you work overtime you get 50%
and if
you work moore than 4 hours overtime one day you get a double50 %. Say
one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs
regular
time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs
overtime.
This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra.

But when a person works only 80% or 4 days, he first have to work 7,5
hrs overtime
that week to have full time job. After that, overtime is rewarded with
50% extra or
100% extra if moore than 4 hrs same day.

What I want formulas for is: 0% 50% 100%
0% is for overtime hours up to 37.5 hours a week, then 50% for
additional overtime
and so on.

For full time workers working 8.5 hours overtime:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 0 4 1.5
5/2/2007 16:00 19:00 0 3 0

if part time (80%) this example should look like this:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 5.5 0 0
5/2/2007 16:00 19:00 2 1 0

The formulas must be able to handle both examples in one solution if
possible.

Any good suggestions for good formulas is very much appreaciated!

Regards
Jan T.

On 23 Mai, 01:30, "Sandy Mann" wrote:
Hi Jan,

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra
day,
that should give me 7.5 hrs with 0% extra.

That is what I get.

If I work 37.5 hours Monday through Friday, all overtime on Saturday will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

change the formulas to:

0% Formula:
=IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4))
50% Formula:
=IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0))
100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

If it is still not right then post back or send me your e-mail address and I
will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Jan T." wrote in message

...



This works great the first rows, but when I come to one of the last rows
and I have already worked more than 37.5 hrs, it still returns some 0%
hours.

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra
day,
that should give me 7.5 hrs with 0% extra.

Every hour worked after full time job (37.5 hrs) shall give at least 50%
extra pr hrs.
If I work 37.5 hours Monday through Friday, all overtime on Saturday will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so on,
(because it is less this day than 4 hours = no 100% extra here).

Can this too be calculated in one formula?
Thanks very much for helping!

Jan

"Sandy Mann" skrev i melding
...

Jan,

With the Hours Worked for the day in Column D, 0% in Column E, 50% in
Column F, 100% in Column G and the days of the week, Monday to Sunday ,in
Rows 4:10 then:

0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)
50% formiula =IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)
100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

and copy down to row 10.

It would be possible to miss out the Hours Worked column and calculate
the hours in the forumulas but it would make then considerably longer.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Jan T." wrote in message
...
I do have a special case when it comes to calculate overtime, I think.

The case is this:
Company rules says that you are not supposed to have any extra % for
overtime
before you have 37.5 hrs a week. Then, if you work overtime you get 50%
and if
you work moore than 4 hours overtime one day you get a double50 %. Say
one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs
regular
time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs
overtime.
This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra.

But when a person works only 80% or 4 days, he first have to work 7,5
hrs overtime
that week to have full time job. After that, overtime is rewarded with
50% extra or
100% extra if moore than 4 hrs same day.

What I want formulas for is: 0% 50% 100%
0% is for overtime hours up to 37.5 hours a week, then 50% for
additional overtime
and so on.

For full time workers working 8.5 hours overtime:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 0 4 1.5
5/2/2007 16:00 19:00 0 3 0

if part time (80%) this example should look like this:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 5.5 0 0
5/2/2007 16:00 19:00 2 1 0

The formulas must be able to handle both examples in one solution if
possible.

Any good suggestions for good formulas is very much appreaciated!

Regards
Jan T.- Skjul sitert tekst -

- Vis sitert tekst -



It is getting better. It seems like there is still something not right
though.

I tried different scenarios with Monday throug Friday 08:30 to 16:00.
Then
08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00.
For Saturday and Sunday to return correct answers, there should not be
any 0%. Further it should return 4 hrs with 50% both Saturday and
Sunday
and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on
Sunday.
This is a full time scenario.

For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not
Wednesday).
Then I put some overtime on Saturday, say from 08:30 to 21:00 and
Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday
(realising I have worked at least 37.5 hrs that week) and 4 hrs with
50%
and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and
1hrs with 100%.

I get wrong in first scenario on Sunday. It returns null 50% but it
should
return 4 hrs here, right?

Also if I work only from 08:30 to 15:00 on Friday, it should return 1
hrs
less 100% on Saturday I think. Any idéa?

Jan

Hi Jan,

I have changed the formulas to:

0%:
=IF(SUM($E$3:E3)1+TIME(13,30,0),0,IF(SUM($E$4:$E$ 10)1+TIME(13,30,0),MIN(E4,TIME(7,30,0)),E4))

50%:
=IF(SUM($E$3:$E$10)1+TIME(13,30,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),IF(SUM($E$4:$E$10)1+TIME(13 ,5,0),MIN(MAX(E4-F4,0),E4,TIME(4,0,0)),0))

100%: =IF(SUM($E$4:$E$10)1+TIME(13,30,0),E4-SUM(F4,G4),0)

But I am obviously still not understanding you:

For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not
Wednesday).

Also if I work only from 08:30 to 15:00 on Friday, it should return 1
hrs
less 100% on Saturday I think.

Why? Monday to Friday (Missing out Wednesday) totals 29:30
12:30 hours on Saturday brings you to 42 hours so surely the normal overtime
rules would apply?

The only way that reducing the hours on Friday would affect the overtime in
Satuday would be if Saturday's 0% hours were calculated to be only just
enough to make the total to date to be 37:30 with the rest paid as overtime
but you said:

Then I put some overtime on Saturday, say from 08:30 to 21:00 and
Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday

If Saturday's 0% hours were calculated to be just enough to bring the total
to date up to 37:30 then reducing the hours Monday - Friday would increase
the amount of hours required in Saturday's 0% thus reduce the overtime on
that day.

Anyway I will send you a sample sheet for you to have a look at.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


wrote in message
ps.com...
On 23 Mai, 01:30, "Sandy Mann" wrote:
Hi Jan,

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra
day,
that should give me 7.5 hrs with 0% extra.

That is what I get.

If I work 37.5 hours Monday through Friday, all overtime on Saturday
will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

change the formulas to:

0% Formula:
=IF(SUM($D$4:D4)37.5/24,0,IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4))
50% Formula:
=IF(SUM($D$4:D4)37.5,MIN(MAX(D4-7.5/24,0),D4,4/24),IF(SUM($D$4:$D$10)37.5*/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0))
100% Formula (as before): =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

If it is still not right then post back or send me your e-mail address and
I
will send a sample sheet tomorrow, (it's 12:30am here and I am off to bed)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Jan T." wrote in message

...



This works great the first rows, but when I come to one of the last rows
and I have already worked more than 37.5 hrs, it still returns some 0%
hours.

If I work 4 days a week (30hrs), and work overtime 7.5 hours one extra
day,
that should give me 7.5 hrs with 0% extra.

Every hour worked after full time job (37.5 hrs) shall give at least 50%
extra pr hrs.
If I work 37.5 hours Monday through Friday, all overtime on Saturday
will
give 50% for the first 4 hours and then 100% for the next say 3,5 hrs if
I work a extra day, 7.5 hrs.

If I continued on Sunday 3 hours, that should be 3 hrs with 50% and so
on,
(because it is less this day than 4 hours = no 100% extra here).

Can this too be calculated in one formula?
Thanks very much for helping!

Jan

"Sandy Mann" skrev i melding
...

Jan,

With the Hours Worked for the day in Column D, 0% in Column E, 50% in
Column F, 100% in Column G and the days of the week, Monday to Sunday
,in
Rows 4:10 then:

0% formula =IF(SUM($D$4:$D$10)37.5/24,MIN(D4,7.5/24),D4)
50% formiula
=IF(SUM($D$4:$D$10)37.5/24,MIN(MAX(D4-7.5/24,0),D4,4/24),0)
100% formula =IF(SUM($D$4:$D$10)37.5/24,D4-SUM(E4,F4),0)

and copy down to row 10.

It would be possible to miss out the Hours Worked column and calculate
the hours in the forumulas but it would make then considerably longer.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

"Jan T." wrote in message
...
I do have a special case when it comes to calculate overtime, I think.

The case is this:
Company rules says that you are not supposed to have any extra % for
overtime
before you have 37.5 hrs a week. Then, if you work overtime you get
50%
and if
you work moore than 4 hours overtime one day you get a double50 %. Say
one person works from 08:00 to 16:00 (minus 30 min. lunch) = 7.5 hrs
regular
time. He then works overtime from 16:00 to 21:30 which makes 5.5 hrs
overtime.
This will give him 4 hours with 50% extra and 1.5 hrs with 100% extra.

But when a person works only 80% or 4 days, he first have to work 7,5
hrs overtime
that week to have full time job. After that, overtime is rewarded with
50% extra or
100% extra if moore than 4 hrs same day.

What I want formulas for is: 0% 50% 100%
0% is for overtime hours up to 37.5 hours a week, then 50% for
additional overtime
and so on.

For full time workers working 8.5 hours overtime:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 0 4 1.5
5/2/2007 16:00 19:00 0 3 0

if part time (80%) this example should look like this:
WorkDate FromTime ToTime 0% 50% 100%
5/2/2007 16:00 21:30 5.5 0 0
5/2/2007 16:00 19:00 2 1 0

The formulas must be able to handle both examples in one solution if
possible.

Any good suggestions for good formulas is very much appreaciated!

Regards
Jan T.- Skjul sitert tekst -

- Vis sitert tekst -



It is getting better. It seems like there is still something not right
though.

I tried different scenarios with Monday throug Friday 08:30 to 16:00.
Then
08:30 to 21:00 on Saturday and Sunday from 10:00 to 15:00.
For Saturday and Sunday to return correct answers, there should not be
any 0%. Further it should return 4 hrs with 50% both Saturday and
Sunday
and 100% for the rest that is 8,5 hrs 100% on Saturday and 1hrs on
Sunday.
This is a full time scenario.

For a part time scenario, I work Mon, Tue, Thirs and Fri. (Not
Wednesday).
Then I put some overtime on Saturday, say from 08:30 to 21:00 and
Sunday from 10:00 to 15:00. Now 0% should return 7.5 hrs on Saturday
(realising I have worked at least 37.5 hrs that week) and 4 hrs with
50%
and 1 hrs with 100%. On Sunday, it should return 4hrs with 50% and
1hrs with 100%.

I get wrong in first scenario on Sunday. It returns null 50% but it
should
return 4 hrs here, right?

Also if I work only from 08:30 to 15:00 on Friday, it should return 1
hrs
less 100% on Saturday I think. Any idéa?

Jan