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)
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:
