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


with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
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.


--
Regards,

Peo Sjoblom



"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Jan T." wrote in message
...
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" skrev i melding
...
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