Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
I am on salary (weekly) but always work alot more than 40 hrs per week.
How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Hi
Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
thx,
here is more needed data. hours per wk fluctuate from 45-65, therefore I do not have an o/t rate. and all my o/t needs to be calculated at time and a half, not double. Can you help? Thanks again. "Michael M" wrote: Hi Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
OK
TRy =(A1-40)*A3+(40*A3*.666666) with your total hours in A1 and your time and half payrate in A3 This obviously puts all of pay result into the one location Regards Michael M "help in CA" wrote: thx, here is more needed data. hours per wk fluctuate from 45-65, therefore I do not have an o/t rate. and all my o/t needs to be calculated at time and a half, not double. Can you help? Thanks again. "Michael M" wrote: Hi Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
I handle payroll for 50 employees. I set up a spreadsheet that handles this
for me giving me a way to double check the info entered into the payroll system before I process. In Row 1 Set up the following columns: Column A-Name; Column B-Reg Hours; Column C-OT Hrs; Column D- Rate of Pay; Column E- Reg Total; Column F-OT Total; Column G-Gross If you want you can change the Name column to date so that it becomes a database of hours worked on a per pay period basis. You will manually enter the weekly info into columns A-C. Starting the first week in row 2 do the following: In Column D enter your current rate of pay. Column E formula =B2*D2 Column F formula =sum(C2*D2)*1.5 for time and a half or ...)*2 for double time Column G formula =E2+F2 To have a database on a week by week basis you can copy columns D-G down several rows and just enter columns A-C weekly. Hope this helps "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
thx again,
unfortunatelly this only solves the problem with the time and a half formula. However the ? still remains since I have no set o/t rate per say. Example: wkly pay is always $600, hours worked is always different from wk to wk I am looking for a formula that will actually tell me on a wk by wk basis what exactly is my hourly rate is, taking into account all o/t hours worked. And keeping in mind that my weekly salary is always $600. SORRY FOR THE CONFUSION. "Michael M" wrote: OK TRy =(A1-40)*A3+(40*A3*.666666) with your total hours in A1 and your time and half payrate in A3 This obviously puts all of pay result into the one location Regards Michael M "help in CA" wrote: thx, here is more needed data. hours per wk fluctuate from 45-65, therefore I do not have an o/t rate. and all my o/t needs to be calculated at time and a half, not double. Can you help? Thanks again. "Michael M" wrote: Hi Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Hi help in CA,
Try this formula in B1 =600/(((A1-40)*1.5)+40) and format it to 2 decimal places. In A1 put your total hours say 65 which gives you an effective payrate for that week of $7.74 per hour. HTH Martin |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
I'm confused - you're saying that regardless of how many hours you work, your
pay is always $600 If so then: HRS PAY HRLY RATE =PAY/HRS 65 600 9.230769231 45 600 13.33333333 27 600 22.22222222 53 600 11.32075472 Is that what you're looking for?? "help in CA" wrote: thx again, unfortunatelly this only solves the problem with the time and a half formula. However the ? still remains since I have no set o/t rate per say. Example: wkly pay is always $600, hours worked is always different from wk to wk I am looking for a formula that will actually tell me on a wk by wk basis what exactly is my hourly rate is, taking into account all o/t hours worked. And keeping in mind that my weekly salary is always $600. SORRY FOR THE CONFUSION. "Michael M" wrote: OK TRy =(A1-40)*A3+(40*A3*.666666) with your total hours in A1 and your time and half payrate in A3 This obviously puts all of pay result into the one location Regards Michael M "help in CA" wrote: thx, here is more needed data. hours per wk fluctuate from 45-65, therefore I do not have an o/t rate. and all my o/t needs to be calculated at time and a half, not double. Can you help? Thanks again. "Michael M" wrote: Hi Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Just because I'm a nit picker.....
If they work <40 hrs the formula crashes. Biff "MartinW" wrote in message ... Hi help in CA, Try this formula in B1 =600/(((A1-40)*1.5)+40) and format it to 2 decimal places. In A1 put your total hours say 65 which gives you an effective payrate for that week of $7.74 per hour. HTH Martin |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Martin,
Thank You very ,very much for your help, this was exactly the answer I was looking for. Many Thanks, "MartinW" wrote: Hi help in CA, Try this formula in B1 =600/(((A1-40)*1.5)+40) and format it to 2 decimal places. In A1 put your total hours say 65 which gives you an effective payrate for that week of $7.74 per hour. HTH Martin |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Thank you,
This is very useful for hourly employees, unfortunatelly I am salaried. fyi, I have the answer now. Thanks again. "NancyJeanne" wrote: I handle payroll for 50 employees. I set up a spreadsheet that handles this for me giving me a way to double check the info entered into the payroll system before I process. In Row 1 Set up the following columns: Column A-Name; Column B-Reg Hours; Column C-OT Hrs; Column D- Rate of Pay; Column E- Reg Total; Column F-OT Total; Column G-Gross If you want you can change the Name column to date so that it becomes a database of hours worked on a per pay period basis. You will manually enter the weekly info into columns A-C. Starting the first week in row 2 do the following: In Column D enter your current rate of pay. Column E formula =B2*D2 Column F formula =sum(C2*D2)*1.5 for time and a half or ...)*2 for double time Column G formula =E2+F2 To have a database on a week by week basis you can copy columns D-G down several rows and just enter columns A-C weekly. Hope this helps "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
True Biff, but I don't think the OP is too concerned about working
less hours. He just wants to know how much his boss is screwing him for when he works overtime!! ;-) Regards Martin |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Glad I could help.
Regards Martin |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Yes, because he is salaried.
Steve On Thu, 03 Aug 2006 05:08:02 +0100, NancyJeanne wrote: I'm confused - you're saying that regardless of how many hours you work, your pay is always $600 If so then: HRS PAY HRLY RATE =PAY/HRS 65 600 9.230769231 45 600 13.33333333 27 600 22.22222222 53 600 11.32075472 Is that what you're looking for?? "help in CA" wrote: thx again, unfortunatelly this only solves the problem with the time and a half formula. However the ? still remains since I have no set o/t rate per say. Example: wkly pay is always $600, hours worked is always different from wk to wk I am looking for a formula that will actually tell me on a wk by wk basis what exactly is my hourly rate is, taking into account all o/t hours worked. And keeping in mind that my weekly salary is always $600. SORRY FOR THE CONFUSION. "Michael M" wrote: OK TRy =(A1-40)*A3+(40*A3*.666666) with your total hours in A1 and your time and half payrate in A3 This obviously puts all of pay result into the one location Regards Michael M "help in CA" wrote: thx, here is more needed data. hours per wk fluctuate from 45-65, therefore I do not have an o/t rate. and all my o/t needs to be calculated at time and a half, not double. Can you help? Thanks again. "Michael M" wrote: Hi Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. -- Steve (3) |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
He just wants to know how much his boss is screwing
him for when he works overtime!! ;-) Yeah, those 65 hr weeks look dismal! I'd be looking for another job! Biff "MartinW" wrote in message ... True Biff, but I don't think the OP is too concerned about working less hours. He just wants to know how much his boss is screwing him for when he works overtime!! ;-) Regards Martin |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Take your Annual salary (A1) and divide it by 2080 manhours. This will give
you your rate of pay hourly (A2). Take your Hourly pay rate (A2) and multiple that by 1.50 this will give you your of pay per overtime hour (A3). Make 2 columns for each week ending pay period (Sunday). One coumn for your regualr hours and the other for your overtime hours. Multiply your overtime (A3) pay rate times the overtime hour you work will give you the amount of gross pay your missing out on. "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
help in CA wrote:
Martin, Thank You very ,very much for your help, this was exactly the answer I was looking for. Many Thanks, "MartinW" wrote: Hi help in CA, Try this formula in B1 =600/(((A1-40)*1.5)+40) and format it to 2 decimal places. In A1 put your total hours say 65 which gives you an effective payrate for that week of $7.74 per hour. HTH Martin You might want to check this link. There are certain requirements that an employee must meet to be exempt from overtime. You may meet the requirements but then again maybe not. You might just be getting a raw deal. http://www.ehso.com/cssdol/dolsalariedexempt.php gls858 |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
But what happens if he leaves early for a school meeting with his kids
teachers, or he's feeling "not so well", and takes a half day off, or doesn't come in at all? Is his pay *reduced* by hours missed? That we don't know! Two sides to every coin.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... He just wants to know how much his boss is screwing him for when he works overtime!! ;-) Yeah, those 65 hr weeks look dismal! I'd be looking for another job! Biff "MartinW" wrote in message ... True Biff, but I don't think the OP is too concerned about working less hours. He just wants to know how much his boss is screwing him for when he works overtime!! ;-) Regards Martin |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
Although I appreciate your concern, maybe I should tell you that weekly
salary is actually twice the disclosed amount. regards, help in CA "Biff" wrote: He just wants to know how much his boss is screwing him for when he works overtime!! ;-) Yeah, those 65 hr weeks look dismal! I'd be looking for another job! Biff "MartinW" wrote in message ... True Biff, but I don't think the OP is too concerned about working less hours. He just wants to know how much his boss is screwing him for when he works overtime!! ;-) Regards Martin |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
"help in CA" wrote..
Although I appreciate your concern, maybe I should tell you that weekly salary is actually twice the disclosed amount. I'd still be looking for another job! <bg I used to have a job like that. 10 - ?? hrs a day. I told the "boss": F.U. pal, pay me for my time or I'm outta here! "He" paid me! <VVBG Biff |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I'm a salaried employee, ?how to calculate actual rate of pay
If A1 has the number of hours you worked, and B1 has your hourly rate, and a
standard workweek is 40 hours, and OT = 1.5 times your hourly rate, then: =IF(A1<=40,A1*B1,(A1-40)*B1*1.5+(A1*B1)). Dave "Tom" wrote: Take your Annual salary (A1) and divide it by 2080 manhours. This will give you your rate of pay hourly (A2). Take your Hourly pay rate (A2) and multiple that by 1.50 this will give you your of pay per overtime hour (A3). Make 2 columns for each week ending pay period (Sunday). One coumn for your regualr hours and the other for your overtime hours. Multiply your overtime (A3) pay rate times the overtime hour you work will give you the amount of gross pay your missing out on. "help in CA" wrote: I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bank cal for interest on cd | Excel Worksheet Functions | |||
ROUNDDOWN but calculate actual numbers | Excel Worksheet Functions | |||
XNPV vs. NPV(quarterly) different results | Excel Worksheet Functions | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions | |||
How do I calculate interest on actual date? | Excel Worksheet Functions |