Formula help required please.

[pobrien31]

Hi,

Please can someone help me please.

I am trying to set up (what I initially though to be simple!!!) som
formulas for hours worked, overtime rates etc....

To start with there are 2 different rates depending on when the perso
starts.

Between 06:00 and 18:00 the rate is £7.95 per hour.
Between 18:00 and 06:00 the rate is £9.95 per hour.

There is a 1 hour break (to be deducted from the total hours worked).
Overtime is paid after 8 hours at a rate 1.5 x the respective rates a
above.

The problem occurs because the person can start at 15:00 and finish a
03:00 for example.

So, they will have 3 hours at the day rate and the remainder at th
night rate. The 1 hour break is deducted from the higher rate, i.e. th
night rate.

I'm just stuggling to put this into something like workable.

Any and all help would be most appreciated.

You reply via this forum or feel free to e-mail a
'(e-mail address removed).

Regards,

Patrick

 

[Andy Brown]

Hi Pat.

Please can someone help me please.

A consultant, probably. FWIW, here's a couple of tips/comments.

With eg: Start Time in A2 & End Time in B2, you'll need in C2

=B1-A1+(A1<B1)

to cover the "spans midnight" option. Or it may be =B1-A1+(A1>B1), I can
never remember.

If you're repeatedly entering a lot of times, it might be worth defining an
AutoCorrection of ".." for ":00" ; it's much easier to key "18.." than
"18:00".

There seem to be six possible scenarios for each attendance, assuming no-one
works > 24 hours, in terms of Start Time/End Time -- >6, <18 ; <6, <18 ; <6,

18 ; >6, >18 ; >18, <6 ; >18, >6.

One thing I didn't get was "The 1 hour break is deducted from the higher
rate". If they do 9 hours during day rate span, then the break comes off the
higher rate, so they get 9 hours at flat rate? Why would they do that?

HTH,
Andy

 

[Andy Brown]

However, this link may help -

http://www.cpearson.com/excel/overtime.htm

Rgds,
Andy

 

[Frank Kabel]

Hi
thats tricky. if A1 stores the starting time and B1 the end time try
the following (assumption: no one works more than 24 hours)
0. Create heper columns for both types of times
1. To calculate the time for 7.95 per hour try the following
(assumption: cell C1)
=MIN(B1,TIME(18,0,0))-MAX(A1*(A1<B1),TIME(6,0,0))+(A1>B1)*(TIME(18,0,0)
-MIN(TIME(18,0,0),A1))
2. To calculate the time for the 9.95 try (in D1)
=max((A1>B1)+B1-A1-C1,0)
3. To calculate the hours including break time in E1 (7.95) and F1
(9.95)
E1: =C1-if(D1<time(1,0,0),time(1,0,0)-D1,0)
F1: =MAX(D1-time(1,0,0),0)
4. Calculate the costs (G1):
=E1*7.95+F1*9.95

Thats a little bit unclear for me is the overtime issue. Which rate do
you use if an employee works from 15:00 - 02:00??

HTH

Frank

 

[Frank Kabel]

Hi Pat.


A consultant, probably.

Regards
Frank

 

[pobrien31]

Hi Andy,

Thanks for that. It wasn't trying to be confusing with the 1 hour break
scenario, but I can see what you mean!!!

Obviously if they do a normal day, i.e. between 06:00 and 18:00 then the
break will be deducted from the day rate. The same goes for the night rate.
The confusion for me and the question I had to ask also was when they
started at 15:00. I have since been told that as drivers then they don't
have a break until after their first 4.5 hours of working. As per the laws
here in the UK.So it naturally come of their night rate anyway!!!

Many thanks once again.

Take care,

Patrick.

 

[pobrien31]

Hi Frank, thanks for that!!!

The overtime is not an issue, if they do overtime then it will be done in
the night rate period anyway, therefore they get 1.5 times the night rate.

Andy was confused as to the 1 hour break. That would also be taken from the
night rate as they don't get their first break until 4.5 hours after the
start of shift, which puts them in the night rate anyway.

Thanks again for all your help.

Regards,

Pat.