Not even sure how to title this one...


New Member
Apr 14, 2010
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.


(Using Excel 2003 on this project)
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...