Not even sure how to title this one...

JStreck

New Member
Joined
Apr 14, 2010
Messages
26
I've tried several versions of an IF, IF(AND), IF(OR), and every combination in between, as well as different iterations of a VLOOKUP function to come up with a way to populate a cell with preset values based on multiple conditions of a time frame in an adjacent range.

I am making a calculator which calculates time worked, but deducts break times. The standard shift time is 6:45 to 15:00. Presumably, any start time at least one hour prior to 6:45 would be OT, and require a 10 or 20 minute break. Equally, any end time at least one hour beyond 15:00 would be OT, and would require a 10 or 20 minute break.

Lets assume that any time frame between 9 and 10.25 hours earns a 10 minute break, and any time frame between 10.26 and 12.25 hours earns a 20 minute break.

However, the OT is capped at 12.25 hours, and the max break time is 20 minutes.

The tricky part is that the OT time may be accrued in any combination of coming in before 6:45 and staying beyond 15:00.

In cell A1 I have the start time which can range from 2:45 to 10:45 in 15 min. increments; i.e. 2:45, 6:15, 5:30, etc.

In cell A2 I have the end time which presumably would range from 10:45 to 19:00 in the same format

In cell H1 I want to populate the predetermined value (minutes) for break time based on the length of time worked, but also based on conditions of start and end time.

For instance, if the employee came in 1 hour early, but stayed 3 hours late, the cell should populate 20. If the employee came in 2 hours early and left on time, the cell should populate 10. As you can see, there are many scenarios in which an employee may accrue OT, and I cannot seem to come up with a formula or function to accurately analyze the conditions and populate the predetermined value.

I hope this makes sense... Thanks for looking.

-JStreck

(Using Excel 2003 on this project)
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,728
Messages
5,833,337
Members
430,205
Latest member
chang2807

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
Top