Help with formula with multiple conditions

reandre68

New Member
Joined
Nov 30, 2016
Messages
2
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?

Thanking you in advance.

Great forum by the way :)

Regards
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top