Might Be Multiple "IF" Issue

**kthrynileen** · 12-15-2012, 06:55 PM

Hello! I made a time-sheet in Excel that calculates overtime and I'm trying to accomodate the various types of overtime. I have it split out into various columns:
Regular rate - up to 8 hours for each day
Daily overtime 1.5 - time and a half for anything over 8 hours for each day
Daily overtime 2 - double time for anything over 12 hours for each day
7th consecutive day 1.5 - time and a half for first 8 hours of 7th consecutive day
7th consecutive day 2 - double time for anything over 8 hours on the 7th consecutive day
Weekly overtime 1.5 - time and a half for hours greater than 40 for the week (after all other OT has been considered)

The only ones I can't get to work the way I'd like are the ones for the 7th day of the week... If the employee has worked 7 consecutive days, it needs to calculate time and a half for the first 8 hours, and double time for anything over that.

I'm thinking it would be an IF formula that will look at the hours input to each of the previous days and if all of them are greater than zero, the hours for the 7th day will appear in that cell... I would put a formula in the cell for regular hours indicating that if the 7th Day OT cell shows greater than zero than the regular cell would be zero.

I'm not sure how to write the formula that will look at the previous 6 days sequentially and turn the proper result.

Does that make sense to anyone? Any suggestions much appreciated!!

**dredwolf** · 12-15-2012, 07:09 PM

If you could upload a sample workbook showing how your data is set up, and a few samples of what you expect to see, it would make answering the post a lot easier...ohterwise about all we can do is offer generalities, or formula's based on a quick WB we create that may not resemble your data at all

Add a File - click advanced (next to quick post), scroll down until you see "manage attachments", click that and select "add files" (top right corner). click "select files" find your file, select file, click "upload", click 'done" bottom right. click "submit reply"

Hope this helps

**FDibbins** · 12-15-2012, 07:26 PM

as suggested, upload a sample workbook with all confidential info removed.

also, take a look at using if(sum(current-cell:6-cells-back)>0,time*1.5,time)

**kthrynileen** · 12-15-2012, 08:55 PM

Thank you for the quick responses! I spent some more time with this and came up with a solution but I'm not sure if my formulas are unnecessarily complex. I'm attaching the finished product with some sample hours plugged in.

kp

**dredwolf** · 12-15-2012, 09:21 PM

is the value in c22 correct? creates a negative pay situation..and BTW, thank you for uploading a sample sheet, this definitely is different than what I was thinking about from your initial post

**FDibbins** · 12-15-2012, 09:42 PM

to begin with, try this in F21...
=IF(COUNTIF(B15:B21,">0")>6,IF(B21<8,B21,8),0)
next, a few questions...
1. would it make things easier if you applied the OT factor when calculating the hours worked in each of you're columns? eg D19, instead of 3, 4.5 =IF(B19>12,4*1.5,IF(B19>8,(B19-8)*1.5,0))

that way, you can just add up the hours to column J and apply the "regular" rate $ 35.00

2. is there a reason you have that gap between row 21 and 25? the reason i ask is that would it be possible for some1 to work that 7-day stretch from 12/3 to 12/9? that gap makes a calc for that situ more difficult

**kthrynileen** · 12-16-2012, 06:11 AM

H22 shows the number of regular hours (summed from C15 through C23) that are over 40 and eligible for OT pay after all other OT has been considered... C22 backs out the hours from the regular column so that the do not get added in twice. The same formula is set up in cell C32 for the sum of C25 through C31.

**FDibbins** · 12-16-2012, 07:42 PM

ok try this in F21...
=IF(COUNTIF(B15:B21,">0")>6,IF(B21<8,B21,8),0)
and this in G21...
=IF(COUNTIF(B15:B21,">0")>6,IF(B21>8,(B21-8),0),0)

**kthrynileen** · 12-17-2012, 05:18 PM

hello! thank you for your help!!

We need to see the actual number of hours worked (by day and by week) so that is the reason the columns calculate that way. I'm removing the subtotals in column J because the weekly overtime at the end (J22) complicates things and doing the daily subtotal is actually not necessary for our purposes - the data in J36 - J42 is all we need in that respect

The gap between rows 21 and 25 is to separate the weeks... otherwise we wouldn't be able to calculate the 7th day OT properly. Each week is considered separately so I've left room to calculate the total weekly hours and back out the duplicate weekly overtime hours if necessary.