# Help with formula with multiple conditions

#### reandre68

##### New Member
Hi guys,

I'm helping my wife with a spreadsheet to calculate hours of work for her employees and I'm trying to create a formula with multiple conditions to calculate the correct overtime.

Basically I have columns for time in and time out, then a column that verifies the actual amount of hours worked (9) against the amount of hours an employee should work (8) and then deducts 1 hour for lunch.

Then I have 2 other columns with SUMIF functions, where the first calculates the amount of overtime like this: SUMIF('HOURS/DAY' ;">"&HOURS TO WORK;'HOURS/DAY'−8), which should ideally return a value of 0 hours (which it does). The second column calculates the hours owed if worked less than the prescribed 8 hours like this: SUMIF('HOURS/DAY';"<"&HOURS TO WORK;8−'HOURS/DAY'), which should ideally return the value if I deduct the 8 prescribed hours from the actual hours worked (again, which it does).

Where I get stuck is on the next column where I'm trying to calculate the overtime at 1.5 like usual, but when an employee owes her hours, the extra hours worked does not count as 1.5, but as a standard hour, until all the hours owed are worked back, then from 0 hours owed and onward, overtime gets calculated at 1.5

So what I have so far is something along the lines of this: IF(OT TOTAL<"0";OT TOTAL+BANK;IF(OT TOTAL 4≥"0";OT TOTAL+BANK 5×1.5;OT TOTAL 4−IOU))

I hope this makes sense.

So I am using nested IF functions to try and handle more than 1 condition. If the overtime total is <0, then only count the extra hours worked at 1. If =>0 then count the extra hours at 1.5. Else, if the hours worked is less than the prescribed 8 for the day, just deduct more hours from the IOU column.

Where I really get worried is where I also need to make sure that if an employee, for example, owes 2 hours and he works 3 extra for the day, only the first 2 (-2 up to 0) counts as x1, but the last of the 3 (0 up to 1) should count as x1.5. Is that even possible?

Great forum by the way

Regards

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Caribeiro77

##### Well-known Member
Welcome to the board.
It would be great if you could provide us a small example of your data..
You can use MR HTML Maker( see on my signature how to do this)

Replies
0
Views
259
Replies
1
Views
596
Replies
3
Views
1K
Replies
1
Views
711
Replies
5
Views
377

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back