Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculating pay rates for different times of the day
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculating pay rates for different times of the day
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 On Nov 8, 12:58 am, davidesw wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculating pay rates for different times of the day
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. "Pete_UK" wrote: 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 On Nov 8, 12:58 am, davidesw wrote: 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculating pay rates for different times of the day
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? -- David Biddulph "davidesw" wrote in message ... 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. "Pete_UK" wrote: 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 On Nov 8, 12:58 am, davidesw wrote: 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculating pay rates for different times of the day
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. "David Biddulph" wrote: 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? -- David Biddulph "davidesw" wrote in message ... 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. "Pete_UK" wrote: 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 On Nov 8, 12:58 am, davidesw wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating rates using sumproduct | Excel Worksheet Functions | |||
Calculating Compounded Growth Rates | Excel Worksheet Functions | |||
Calculating Per Diem Rates | Excel Worksheet Functions | |||
Calculating non-financial rates | Excel Worksheet Functions | |||
Need help with conditional rates and roster times for payroll | Excel Discussion (Misc queries) |