calculating pay rates for different times of the day

[davidesw]

hi there i am new to this so please dont shout at me,i'm using office 2007.
i'm after an easier way for me to calculate my pay i work odd shifts crossing
over time zones.
what i'm needing is to be able to calculate different pay rates.
i.e
pay rates from 0600 to 1800 are paid at £8 an hour after 8 hours going onto
£12 an hour, after 1800 this goes into night rate of £9 before 8 hours then
goes too £13.50 an hour for remainder.
Example:

14:00 to 03:00 -45 mins break = 12.25 hours, would work out like this
14:00 to 18:00 = 4*£8
18:00 to 22:00 = 4*£9
22:00 to 02:15 = 4.25* £13.50.

also this way round 01:45 to 15:45 running on night rate of £9 until 06:00
then going onto £8 until 8 hours are completed, then overtime of £12 there
after.
is there a simple way well once excel is set up to calculate this, instead
of me taking two hours every friday to work out. ready for monday for that
weeks pay, fed up with my wages always being wrong.

 

[Pete_UK]

Well, I don't think it will be a simple calculation, but it could be
done. It's getting late here, so I'll get back to you tomorrow.

One odd thing about your first example, though, is that your break
time is deducted from the most expensive period - I would have thought
that it should be deducted from the time period in which it occurred.
Also, will the break period always be 45 minutes taken away from
whatever your start and finish times will be?

Pete

 

[davidesw]

Hi pete, Yes the 45 minutes is always the same, i am a lorry driver in the uk
so always have to deduct 45 minutes from my pay.

and to the secound part yes the 45 is deducted from the time period sorry i
was not thinking when i did the example. my bad.
thanks for your quick response too, i have been trying for ages to get this
right.
two that i have used are, if=(a5=",",a5-0.75) which deduted me break but as
you say thats from the total hours, also used if=a6=",",a6-8) but this just
helped me deduct me 8 hours, i have been trying with serveral others before
admitting i cant do it, get me to put a computer together i can do, but this
well hands up i cant do it.

once again thankyou for your help.

 

[David Biddulph]

Neither if=(a5=",",a5-0.75) nor if=a6=",",a6-8) are valid syntax.
Try again with the exact formulae you have been using. Don't try to retype
them; copy from the formula bar and paste here.

Also, I don't think you've answered Pete's question as to when the break
period is taken. Will your input data have a start time & end time for your
shift, together with a start time and end time for the break, or if not how
do you decide which period to deduct the break time from?

 

[davidesw]

hi sorry was not clear my break is taken from the first part of the 8 hour
day.there is no set time to when i take it. so long as i can show start time
finish time minus break of 45 mins. and cost from start from anytime in the
24 hour cycle. "sorry this is hard to put into words" and i thank you for
being patient with me.
my start times are not always uniform 1 day it could be 13:00 start and
finish upto 15 hours later then it could be a 01:00 start again can be upto
15 hours later but no more than 3 times a week for 15 hours. the formulae
that i have used are pasted below. f4 as worked out as 14 hours work g4
minused the break giving me h4 over time. but this i was using as my model to
start then i need to break down further.
13:00 start end time 03:00
F4 g4 h4
14.00 13.25 5.25
=IF(,F4=",",F4-0.75)
=IF(,G4=",",G4-8)
thinking about it think its better i just use pen and paper. thought this
would be easier than breaking it down.
between 06:00 to 18:00 is day rate 18:00 to 06:00 is night rate, but start
time is different most days, but after 8 hours it goes into overtime.and if
and do hours cross from day to night rate and vis versa, then pay rate
changes again.
say i started at 13:00 03:00 this would break down to
13:00 to 18:00= 5*8 18:00 to 21:00= 2.25*9 21:00 to 03:00= 6*13.50.

the formulae i was trying to work out is i put into cells a to b start and
finish times then cell c would be break cell d x hours *rate (day/night) and
so on. this being for sunday through to monday.

being a big group thought some one might of had a formulae already. sorry i
have taken up your time. many thanks for your help.. david.