Overtime formulas

[Jan T.]

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.

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[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]

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$44)>37.5/24,0,IF(SUM($D$4:$D$10)>37.5/24,MIN(D4,7.5/24),D4))
50% Formula:
=IF(SUM($D$44)>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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Jan.Thorstensen]

Hi Jan,


That is what I get.


change the formulas to:

0% Formula:
=IF(SUM($D$44)>37.5/24,0,IF(SUM($D$4:$D$10)>37.5/24,MIN(D4,7.5/24),D4))
50% Formula:
=IF(SUM($D$44)>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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk










- 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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Hi Jan,


That is what I get.


change the formulas to:

0% Formula:
=IF(SUM($D$44)>37.5/24,0,IF(SUM($D$4:$D$10)>37.5/24,MIN(D4,7.5/24),D4))
50% Formula:
=IF(SUM($D$44)>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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk










- 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

 

[Jan T.]

Hi, again. Sorry about my English and my explanation.
You are right that in order to get any overtime (50% or 100%)
employees have to work at least 37,5 hrs in one week before
getting any extra paid. So skipping Wednesday (because they
have a contract for 80% part time on a regular basis) he or she
would have to work at least 7.5 hrs (f.ex. on Saturday) before
diserving any 50% extra. If they then work more, they get until 4 hrs
with 50%, every hour after this, will be rewarded with 100% but
only if we are talking about the same day. If she or he then works
overtime also on Sunday, starting with 42 hrs earlier this week,
she or he will start with 50% for the first 4 hrs. After theese
the overtime will be 100% extra for the rest of the overtime that
Sunday.

I just have to thank you so much for your patient. It's almost midnight
here, so I will have to get to bed. I will check out your formulas as
soon as I get a chance.

(Isn't there a way to have the formulas
"translated" automatically if I copy yours into a international worksheet
and then paste this into a regular worksheet or something like that?
Well I can't even get that to work. What do I do wrong?)

Thanks again a million! I am really looking forward to a good solution
and I think I soon will see it. /

Jan

 

[Sandy Mann]

Hi Jan,

I just have to thank you so much for your patient. It's almost midnight
here,

I assume that you are East of me and therefore use semi-colons in your
formulas. I sent a spreadsheet to your hotmail address, (it is not a good
idea to post a real e-mail address in a public forum because you will be
targeted with spam so I have "munged" your address below with X's). The
spreadsheet I sent did not bounce so I assume that it arrived but you just
could not use it.

I will therefore send another using semi-colons and the equals signs
replaced with "xyz" To activate the sheet select the range E4:H50, (should
be already selected in the spreadsheet), and then select Edit > Replace >
Find What: enter xyz
Replace With: enter =
then click the Replace All button

The names will obviously not be the same but I hope that you can figure it
out for the above.

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Peo Sjoblom]

It shouldn't matter, I have Swedish Excel (semicolon delimiters) in a
virtual Swedish Windows XP on my pc as well as US Windows XP and Office
2007,
if I email myself a workbook from either regional setting it translates
perfectly between the 2 versions.

 

[Sandy Mann]

Thank you for the information Peo. In that case I assume that it may be the
function names being different in the Swedish XL that Jan was talking
about - surely it does not translate the function names as well. (I have
been assuming from the surname "Thorstensen" that Jan included in one post
he/she is Swedish).

To Jan:
If that is the case then I assume that the Replace technique can be used
before the equals' signs are replaced.

--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Peo Sjoblom]

The "sen" at the end of his name probably means he is either Danish,
Norwegian or maybe even Icelandic, Swedes use "son". Regardless it
translates function names effortlessly as well. The only problems I ever had
was if one builds text strings like {"a","b","c"} in array formulas which in
US UK can be used with either semicolon or commas but in Swedish is used
with \ replacing the commas as a delimiter.


Peo

 

[Sandy Mann]

Thank you again Peo.

Norwegian or maybe even Icelandic, Swedes use "son". Regardless it
translates function names effortlessly as well.

Thinking about it after a night's sleep, I assume that the *names* of
functions that I type in are compiled into machine code of 1's & 0's when
the Workbook is saved, which is the same code regardless of what *language*
I typed in. The receiving XL will translate the machine code into its
resident language and thus *magically* achieve the translation regardless of
the different languages/versions.

--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Jan T.]

Hi, again.
I finally made it... Am I good or what?!

In Column E for 0%:
=MAX(0,MIN(D2+MIN(37.5-SUM($D$22),0),D2))

In Column F; 50%
=(E2<7.5)*MIN(4,D2-E2)

In Column G; 100%
=(F2=4)*(D2-(E2+F2))

Thank you Sandy for helping me on my way. And, by the way I am Norwegian,
(and I know very many jokes about Swedish people. But I will not start with
that, because Peo can probably the same jokes about the Norwegians ;-)

Function names and dilimiters are translated perfectly by Excel. When
pasting it
from plain text is another thing. If I copied my formula from my Norwegian
Excel
into plain text, it will look like this:

0%:
=STØRST(0;MIN(D2+MIN(37,5-SUMMER($D$22);0);D2))

Sandy, you probably helped me more than you know. Thanks again!

Regards
Jan

 

[Sandy Mann]

Hi Jan,

I am very glad that you got it all sorted out.

Forgive me for being curious about your nationality but your midnight being
one hour ahead of mine just got me wondering.

--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Jan T.]

No problem Sandy at all.

BTW, actually the 50% should be simpler than the previous suggestion.
Like this:
=MIN(4,D2-E2)

and not >> =(E2<7.5)*MIN(4,D2-E2)

This evaluation is not neccasary because it makes no difference. However in
the next Column the evaluation (F2=4) does matter; >> In Column G; 100%
Well, I guess this is were this threads ends. Thanks and by for now!

Regards
Jan