Writing IF (AND) Statement to Calculate Payroll Standard and Over Time

Sorry, that's not what I'm looking for. An employee can't work 98 regular time hours in a week and 10 overtime hours. If they did work 108 hours lol, it would be 40 hours regular time (capped at 40) and the rest would be overtime, so 68 overtime hours.

The numbers I wrote in R3:S9 and R12:S18 of my sheet in the first post is what I'm looking for. I manually wrote those numbers in but instead I need code to be in those cells that return those numbers instead of having to manually do the math each time.

Standard hours

=MIN(40,R10)

OT

=MAX(0,R10+S10-40)

BlindAlley and JohnTopley -- Hey, thanks but I'm not looking for the code. I think the code to achieve the result I'm looking for has to be maybe an IF, AND or OR statement, maybe something complex like that which takes into account every hour worked in the week and if there have already been 35 hours worked earlier on in the week, let's say Mon-Thur, then the employee works 10 on Friday but at another jobsite, then it puts 5 hours in the standard column and 5 hours in the overtime column under the SUMS heading on the right. I'm not sure if I'm explaining this clearly lol.

In R3 and copy down to R9

=IF(SUM($R$2:R2)+SUMIF($D$2:$Q$2,"ST",$D3:$Q3)>40,40-SUM($R$2:R2),SUMIF($D$2:$Q$2,"ST",$D3:$Q3))

in S3 and copy down to S9

=SUMIF($D$2:$Q$2,"OT",$D3:$Q3)+(SUMIF($D$2:$Q$2,"ST",$D3:$Q3)-$R3)

Repeat for each employee

Range D3:Q9 formatted as 0.0;;;@ which "hides" to zeros.

For Friday ST=10 which takes total hours > 40 so 2 hours allocated to ST and balance of 8 to OT.

JohnTopley -- So far that's the best code I've seen. It's almost what I'm looking for. It's great, don't get me wrong. But do you see employee 3 at the bottom where he has 8 and 4 on Monday and 8 and 4 on Thursday? If I were to apply those hours in your code, he would get paid for 16 RT hours and 8 OT hours. But the way my job works is if an employee works 8 and 4 then 8 and 4, that's only 24 hours so he'll only get 24 regular time hours paid. See cell R22 and R23.

So the code is super close but the only thing is, if the employee works 10 for Mon at jobsite1, then 10 at jobsite 2 on Tuesday, then 10 at jobsite 3 on Wednesday, then 10 at jobsite 4 on thursday, that will make 40 hours, 10 at each site on different days -- so the problem with the code is if the employee went back to jobsite 1 on Friday, it would kick jobsite #4's hours to OT when it's the friday time that's overtime since jobsite 4 is below jobsite 1 and add the Friday hours in jobsite 1 to the regular time column which shouldn't happen. It should be 10 RT and 10 OT for jobsite 1 in this instance. Please help. Is this a hard fix?

As you mentioned John, I honestly think this problem is being overcooked, having worked for a GC before with the same 40 hour restrictions, the hours were listed as straight time and Extra time only. The calcs were only done at the end of the week to assign normal hours and OT hours. We also added columns to apply hours to each job site so that these could be calculated too via INDEX MATCH formulas on another tab.

BlindAlley -- Looks beautiful but still not what I'm looking for. Your styling is much better than mine! Yes, the ST and XH underneath the days of the week really stand for straight time and extra time and yes, at the end of the week is when normal hours and OT is recorded -- you've said it much better than I tried explaining it. But the problem with the code is, cells S3:S9 does not equal cell S10. The problem we are having here at work is while typing in straight time and extra time hours in throughout the week, we are hoping that the code put in cells R3:S9 will automatically allocate where the straight time is for each job (not ever exceeding 40 hours) and which jobs have the overtime.

Just a note -- the worry isn't really figuring out how many standard hours and overtime hours to pay an employee but really how many standard hours and overtime hours each employee has worked at each jobsite at the end of the week. If an employee works 50 hours at jobsite7 Mon through Thur then 10 hours at jobsite1 (even though jobsite 1 is above jobsite7) then at the end of the week, the employee should have 8 standard hours on friday, 2 extra hours on Friday and in column S they should have 10 overtime hours instead of 8 and 2 for ST and XH because they've already worked 40 for the week, you know?

I've attached what cells R3:S10, R14:R21 and R25:S32 should look like. Please view attachment. I just don't know how to write the code so the cells do this.
Please don't give up lol.

JohnTopley -- Hey man. Your sheet would work too except for I need the standard time and OT hours broken down at the end of the week. Somehow the code has to say that an employee has already worked 40 hours, therefore anything more will be put into the OT column. So if an employee worked Saturday only 6 hours already having worked 40 hours, the 6 would go in the standard column for the day, but for the week at the end, it would go in the OT column.

Anyway, I edited your document and attached it to what I'm looking for. I just wrote over the code in some places.

LOL! Okay cool.

Originally Posted by JohnTopley

Like BlindAlley



However, there are two possible scenarios:

1. For any Job Number on any day, if the daily hours are > 8 the ST =8 and the balance is logged as OT against that job number on that day. This appears to the case in your early postings.

Once the TOTAL ST weekly hours worked is > 40, ALL subsequent hours for any job number are logged as OT.

2. OT is not recorded against any job number until the TOTAL hours > 40. this is the scenario in my last posting so all the OT was allocated to the job(s) on Friday (as per example you posted).

Which do you want?

See the attached for an example of the above.

In either case, I am not sure that a formulaic solution is possible so VBA would be required.

When I try typing in 8 hours for Saturday if the employee went back to work at jobsite1, it should add those hours automatically in the overtime column at the end of the week since they've already worked past 40 but it's still puts them in the regular time column at the end of the week.

Like someone said earlier, the columns during the week underneath the headers Sunday through Saturday really stand for regular time and extra time. All extra time numbers shouldn't automatically be categorized as overtime at the end of the week because if someone worked 10 hours in a day then quit, they're only getting paid for 10 regular time hours. So the hours put in all columns under the day headers during the week should be treated as if it's all regular time. The hard part is trying to decifer at what point do the hours start being added to the overtime column at the end of the week. For example, if I worked 36 hours Mon through Thurs then 8 hours on Friday at a job site I hadn't worked at all week, I would mark 8 under the standard column in Friday but at the end of the week, I'd write 4 for regular time for friday and 4 for overtime.

Another tricky thing I'm not sure how to do is when an employee works at two job sites in a day, which jobsite gets billed the overtime hours if an employee has already worked 36 hours then on Friday, they work 4 hours at jobsite 6 and 4 hours at jobsite 1. Obviously one would get billed for 4 regular and the other for 4 overtime. I think in cases like this, whichever jobsite is higher (lower number row) would take the regular hours first.

So...

Are we to work on Scenario 2 (Week 2) in my last post: ALL hours are treated as standard hours until 40 hours is reached and hours > 40 are overtime?

In your example where we need to allocate some ST on the Friday, the first jobsite (first row) gets any balance of the 40 hours ST (0.5) and ALL remaining hours are defined as OT.

Even with this, I am not sure if it can be done by a formula.

Brain hurting!!!

Originally Posted by JohnTopley

So...

Are we to work on Scenario 2 (Week 2) in my last post: ALL hours are treated as standard hours until 40 hours is reached and hours > 40 are overtime?

In your example where we need to allocate some ST on the Friday, the first jobsite (first row) gets any balance of the 40 hours ST (0.5) and ALL remaining hours are defined as OT.

Even with this, I am not sure if it can be done by a formula.

Brain hurting!!!

Okay I see what you're saying. Yes, your week 2 scenario would be amazing. Sorry, I had already thought you typed code in but I just noticed the red numbers were typed in there. So yeah, were you put 8.5 in Cell N16, it would be amazing to push the .5 to and the 8 to the OT since they has already worked 39.5. Then yeah, putting 10 hours in another jobsite on the same day would move that 10 to OT (I'm talking about cells N18 and S18. Then hypothetically if they worked Saturday at jobsite 55555 for 8 hours, those 8 hours would be put in cell S14 as overtime. I feel like we're on the same page now, wooo!

I cannot see how this can be done without using VBA so you might to consider re-posting on the VBA/Macro forum with a clear explanation the logic required.

If you do this, cross-reference this thread in case anyone on this forum provides a solution (VBA or otherwise).

Originally Posted by JohnTopley

I cannot see how this can be done without using VBA so you might to consider re-posting on the VBA/Macro forum with a clear explanation the logic required.

If you do this, cross-reference this thread in case anyone on this forum provides a solution (VBA or otherwise).

Okay, I'll try that. Thank you.

I haven't given up completely but perhaps a fresh pair of eyes is needed. I'll try and keep track of your progress.

Originally Posted by JohnTopley

I haven't given up completely but perhaps a fresh pair of eyes is needed. I'll try and keep track of your progress.

Yeah, thank you. I was hoping you wouldn't give up but if the brain starts hurting, it's all good. I don't mind manually figuring these numbers out every 2 weeks lol.

Attached is VBA module:

Change data in time sheet and then click RUN. Try it for a number of scenarios to check the computation is correct.

If it is, I will add to the code to loop through multiple time sheets.

Please Login or Register  to view this content.

Originally Posted by JohnTopley

Attached is VBA module:

Change data in time sheet and then click RUN. Try it for a number of scenarios to check the computation is correct.

If it is, I will add to the code to loop through multiple time sheets.

Please Login or Register  to view this content.

Holy crap how did you do that? It's perfect except for Saturday not working into the formula. When I try to write hours into Saturday and hit run, it doesn't work.

So attached is my actual payroll sheet before I edit it to write the employee and jobsite names but I usually have around 25-30 employees. After we get Saturday to work into the code, how would I got about taking that macro and placing it into my sheet for every record? Also, does every record have to have an independent button because it would be awesome if there were only 1 button, that when clicked after all hours are put in, it runs for every record.

Also, I've noticed you ran it for just single regular time and not extra time so Sheet1 which I named Week 1 has regular time and extra time columns. If the code can't be made to fit that sheet, then no biggie, I can add both the regular and extra time together before typing it into the cells -- for that, I created Sheet2 (named Week 2) for copying your code if it can't be modified.

Again, that was amazing. I was losing hope. Lol.

Originally Posted by JohnTopley

Yes: little error in code which missed Saturday.

Test with attached which loops through several time sheets so there is only one button to execute all sheets..

I try and look at your latest posting tomorrow (but it maybe Friday as I am out for most of tomorrow).

Quick glance at your actual payroll means a few changes: can I combine the Regular time and Extra time (so you still enter them separately) so the calculation mirrors the current macro?

I tested it in multiple scenarios and it's beautiful. It works!

Cool! -- I'll await your reply after viewing my latest attachment.

Originally Posted by JohnTopley

Yes: little error in code which missed Saturday.

Test with attached which loops through several time sheets so there is only one button to execute all sheets..

I try and look at your latest posting tomorrow (but it maybe Friday as I am out for most of tomorrow).

Quick glance at your actual payroll means a few changes: can I combine the Regular time and Extra time (so you still enter them separately) so the calculation mirrors the current macro?

Do you mean combine them as in merge the cells? Or as in treat each cell the same as if there are 14 days in a week? BC that's perfectly fine. The only reason we have RT and XT is to mirror the daily reports we get from the supervisors but essentially, regular and extra time is the same thing.

I haven't started the new coding (will do tomorrow Friday) but I have a question:

in the SUM columns: are the all XT values to added in to the (unlabelled) OT column as per your original file(s) OR only added in , together with any ST hours when TOTAL hours worked exceeds 40?

If, for a given day, we have ST=8, XT=2 then do I put 10 into ST SUM or 8 into ST Sum and 2 into OT. from my previous postings I assume the former, so OT is only the TOTAL HOURS less 40.

So if we have 4 days worked with above Daily Hours then TOTAL HOURS (ST) =40, OT=0.

See attached;

Please Login or Register  to view this content.

Added tab "Dashboard" to selected Week and then RUN

Week1 and Week 2 tabs are as your Week 1: tab Week x is the original Week 2 (not used)

Originally Posted by JohnTopley

See attached;

Please Login or Register  to view this content.

Added tab "Dashboard" to selected Week and then RUN

Week1 and Week 2 tabs are as your Week 1: tab Week x is the original Week 2 (not used)

John -- Hey. This is awesome but what if I want to add jobs? The when I click RUN, the macro breaks and won't add hours for jobsite 11 or 12 and so on.

As it stands the macro is "hard-coded" for 10 jobs; I have added code (untested) to count number of job sites *(which must be same for all employees).

Please Login or Register  to view this content.

Amended Employees 1 and 2 in WEEK2 to have 13 job sites.

Hey -- can we use the file I attached instead? It works well and I even tested it with copying an employee and inserting copied cells then running the VBA and it works. The only things is I need to figure out how to make each employee have 15 jobs.

Also, is it too much of a hassle to just eliminate the XT columns altogether? I don't want to delete column X where the OT is calculated because that's crucial but maybe just the XT columns (columns J, L, M, P, R, T and V) so if someone worked 12 hours and where we used to put them as 8 and 4 for 4 extra hours, we will just put 12. If that's too much of a hassle to eliminate those columns because it requires editing the VBA then don't worry about it. But we will still need for each employee to have 15 jobsites. Thank you.

Post #31 allows you to have an unspecified number of job sites: BUT all employees must have the same number of sites.

Insert additional sites for Employee 1 and then copy/paste for all other employees.

This is the problem I think will be easier to accomplish. Basically, I need the XT columns deleted and the code updated to only reference the TIME columns. As it stands now, I can enter hours into the TIME columns and run the macro and everything works beautifully for the XT columns aren't being used. They're just taking up space and it bothers me to hide them one by one. I'd rather delete them but then the code messes up.

Hey John. I put 3 different examples. So bascially, an employee can work at any jobsite each week and sometimes at 2 or 3 in the same day. He/she can also go back to jobsites later in the week. The end result should have their hours in the right, distributed in standard time and overtime columns. The hours that go in the overtime column are any hours over 40. We are trying to make it easier for us to decipher which jobs are being billed the overtime hours. If an employee works 30 hours at jobsite 1 on Monday, those 30 go in the regular time column. If they work 10 hours at jobsite 2, then 10 goes in the regular time column at the end of the week. Then if they go back to jobsite 1 on Wednesday and work an additional 5 hours (35 total for jobsite 1 for that week), it should be 30 in the standard time column and 5 in the OT column because he's already worked over 40 hours for that week.

Yes sir that works perfectly it seems. Awesome. Can we put it altogether now so that I can begin using it?

So what needs to be done: no change is required to the macro.

Okay never mind. I cleaned it up now.

2 things I'm wondering if they can be done:

1 - See at the top in cell I3 where I manually wrote a date? For the 6 cells that follow, I just reference the cell before it and added "+1" in the code so I only have to write the date for the start of the week in cell I3. But I'm wondering in cell P3 or PQ3, would I be able to reference the values in cells I3 and O3 so that when I type the start date in I3, the weeks range is already displayed in cells PQ3? I tried to type something like =value(i3) to value(o3) but code in excel doesn't work that way lol.

2 - On the RUN sheet, would it be possible to add a clear all button/macro that would clear all values in cells I5 to O773 in both sheets and reset the values in columns P and Q back to 0? I supposed I can save a blank sheet as a template and each 2 weeks when we're doing payroll, we can save as so this isn't a big deal.

Sorry, I'm just seeing this. That's very good work. I tried it and it works perfect. Thank you!

Thanks to JeteMc:

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.