|
Macro Programing
I do a payroll for my company on excel, and everyone but me is living in the
stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
So if ... it was just straight equations, it would look sort of like this:
=SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
try sumif, to make you feel better, I gave a newer person in our area a
spreadsheet to organize, sort and do some simple addition on and bring to me, mostly as a skills test. 4 hours later I decided to check on her since it was a 20 minute job. She said she was almost done, I asked how she had approached it, and she proceeded to take out her calculator and show me the calculations she did for about 1200 lines then typed in the answer. DOH! -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
Excel won't accept it like that, you can't use the greater than or less than
signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
I tried sumif, it told me that The Formula you typed contains an error.
Thanks for the suggestion though. -- ¿Confused? "John Bundy" wrote: try sumif, to make you feel better, I gave a newer person in our area a spreadsheet to organize, sort and do some simple addition on and bring to me, mostly as a skills test. 4 hours later I decided to check on her since it was a 20 minute job. She said she was almost done, I asked how she had approached it, and she proceeded to take out her calculator and show me the calculations she did for about 1200 lines then typed in the answer. DOH! -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
When A1 contains 32 and A2 contains 42, the result of =SUM(A1<40.01,A2<40.01)
is 1. This is because A1<40.01 logically TRUE and equates to 1. A2<40.01 is FALSE and equates to 0. Is this what you were trying to do? "¿Confused?" wrote: I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
I meant, why VBA. That is quite different to what you had at the start, but
Sandy seems to have got it now. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
Hi I believe that each week is sperate so you want:
standard time: =min(a1,40)+min(a2,40) (32+40) overtime =a1+a2-[cell above] (32+42-72) how are your cells formatted as time hh:mm or numbers how do you deal with seconds and minutes? if it is formatted as time format the answers as custom [h] or [h]:mm [h]:mm:ss so that it will show total hours and not days and hours. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "¿Confused?" wrote: Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
That would work!!! Except, I have one more confusing thing to throw in there.
If my hours, which are just numbers, are like this: week one total hours are 36, and week two total hours are 42, I need to be able to input up to 40 hours a week, but if one week doesn't have 40 hours, and the other has over 40 hours, I need to have it add it so that the total would not be 78, but 76. Otherwise that first equation would have worked. The second one works and is a godsend though! Thank you!! -- ¿Confused? "Sandy Mann" wrote: For the total working hours try: =MIN(SUM(A1:A2),72) for the overtime hours: =MAX(0,SUM(A1:A2)-72) Assuming that your *hours* are numbers and not XL times -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
I think I see - I though that your working week was 36 hours butI assume
from what you say it could be up to 40 hours. If that is the case then try: Normal hours: =SUM(MIN(A1,40),MIN(A2,40)) For overtime use: =SUM(A1:A2)-B2 In my spreadsheet I had the first formula in B2 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... That would work!!! Except, I have one more confusing thing to throw in there. If my hours, which are just numbers, are like this: week one total hours are 36, and week two total hours are 42, I need to be able to input up to 40 hours a week, but if one week doesn't have 40 hours, and the other has over 40 hours, I need to have it add it so that the total would not be 78, but 76. Otherwise that first equation would have worked. The second one works and is a godsend though! Thank you!! -- ¿Confused? "Sandy Mann" wrote: For the total working hours try: =MIN(SUM(A1:A2),72) for the overtime hours: =MAX(0,SUM(A1:A2)-72) Assuming that your *hours* are numbers and not XL times -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
That works!!! Thank you so very Much. This should help take out all those
nasty human errors that cause people to get less money than they deserve. Again, Thank you!!! -- ¿Confused? "Sandy Mann" wrote: I think I see - I though that your working week was 36 hours butI assume from what you say it could be up to 40 hours. If that is the case then try: Normal hours: =SUM(MIN(A1,40),MIN(A2,40)) For overtime use: =SUM(A1:A2)-B2 In my spreadsheet I had the first formula in B2 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... That would work!!! Except, I have one more confusing thing to throw in there. If my hours, which are just numbers, are like this: week one total hours are 36, and week two total hours are 42, I need to be able to input up to 40 hours a week, but if one week doesn't have 40 hours, and the other has over 40 hours, I need to have it add it so that the total would not be 78, but 76. Otherwise that first equation would have worked. The second one works and is a godsend though! Thank you!! -- ¿Confused? "Sandy Mann" wrote: For the total working hours try: =MIN(SUM(A1:A2),72) for the overtime hours: =MAX(0,SUM(A1:A2)-72) Assuming that your *hours* are numbers and not XL times -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
You are very welcome. It means of course that if someone works 10 hours the
first week and 42 hours the next week they still get 2 hours overtime - where do I get an application form? <g -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... That works!!! Thank you so very Much. This should help take out all those nasty human errors that cause people to get less money than they deserve. Again, Thank you!!! -- ¿Confused? "Sandy Mann" wrote: I think I see - I though that your working week was 36 hours butI assume from what you say it could be up to 40 hours. If that is the case then try: Normal hours: =SUM(MIN(A1,40),MIN(A2,40)) For overtime use: =SUM(A1:A2)-B2 In my spreadsheet I had the first formula in B2 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... That would work!!! Except, I have one more confusing thing to throw in there. If my hours, which are just numbers, are like this: week one total hours are 36, and week two total hours are 42, I need to be able to input up to 40 hours a week, but if one week doesn't have 40 hours, and the other has over 40 hours, I need to have it add it so that the total would not be 78, but 76. Otherwise that first equation would have worked. The second one works and is a godsend though! Thank you!! -- ¿Confused? "Sandy Mann" wrote: For the total working hours try: =MIN(SUM(A1:A2),72) for the overtime hours: =MAX(0,SUM(A1:A2)-72) Assuming that your *hours* are numbers and not XL times -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
Macro Programing
Yes, it's great, and if your ever Salt Lake City, Utah, feel free to let me
know @ . -- ¿Confused? "Sandy Mann" wrote: You are very welcome. It means of course that if someone works 10 hours the first week and 42 hours the next week they still get 2 hours overtime - where do I get an application form? <g -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... That works!!! Thank you so very Much. This should help take out all those nasty human errors that cause people to get less money than they deserve. Again, Thank you!!! -- ¿Confused? "Sandy Mann" wrote: I think I see - I though that your working week was 36 hours butI assume from what you say it could be up to 40 hours. If that is the case then try: Normal hours: =SUM(MIN(A1,40),MIN(A2,40)) For overtime use: =SUM(A1:A2)-B2 In my spreadsheet I had the first formula in B2 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... That would work!!! Except, I have one more confusing thing to throw in there. If my hours, which are just numbers, are like this: week one total hours are 36, and week two total hours are 42, I need to be able to input up to 40 hours a week, but if one week doesn't have 40 hours, and the other has over 40 hours, I need to have it add it so that the total would not be 78, but 76. Otherwise that first equation would have worked. The second one works and is a godsend though! Thank you!! -- ¿Confused? "Sandy Mann" wrote: For the total working hours try: =MIN(SUM(A1:A2),72) for the overtime hours: =MAX(0,SUM(A1:A2)-72) Assuming that your *hours* are numbers and not XL times -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "¿Confused?" wrote in message ... Excel won't accept it like that, you can't use the greater than or less than signs. I'm trying to make it calculate hours on payroll, where if you had a total of 32 hours for week one in cell A1, and a total of 42 hours for week two in cell A2, that it would take the total hours < 40.01 for both weeks and add them together, so that the total would be 72 hours. The next part of that is that I would also need an equation that would take the 2 hours extra and put them in a different cell, so that it would show the overtime hours worked. In reality I need two equations one for cell A3 which would be regular hours worked : =SUM(A1<40.01,A2<40.01) and cell A4 which would be overtime hours worked : =SUM(A140.01,A240.01) -- ¿Confused? "Bob Phillips" wrote: So if ... it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01) ..., why isn't it straight equations? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "¿Confused?" wrote in message ... I do a payroll for my company on excel, and everyone but me is living in the stone age. They still haven't figured out =Sum(A1,A2). Anyways, I'm trying to write a macro to help me make less mistakes. If it was just straight equations, it would look sort of like this: =SUM(A1<40.01,A2<40.01). If anyone can help, I would appreciate it! -- ¿Confused? |
| All times are GMT +1. The time now is 06:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com