Complicated If/And Formula with Circular Reference Help

[Greg_2]

Without going into too much detail of the process, the tool I am designing allows the supervisors of a net repair shop calculate how long each panel will take to complete, and what time the net will be completed at.

To calculate the time, the supervisor enters in information on each panel, and then it gets formatted as you see in the picture below. They enter the time of day repairs started on the net, and then the hours to complete for Panel 1 are added to that to get a completion time for Panel 1. Then the hours to complete for Panel 2 are added to the completion time for Panel 1 to get a completion time for Panel 2 etc.

\1

The trouble I am running into, is accounting for the daily breaks an employee has during the day. They have 3 fifteen minute breaks at 10am, 3pm and 5pm, and a thirty minute break at 12pm. The thing to keep in mind is that if a panel's hours to complete span more than one break, they all have to be added on to the time. For example, if Panel 3 is started at 9:48am, and will take "2:30" to complete, the completion time should be 1:03pm, not 12:18pm. This takes into account the 45 minutes of break between 9:48am and 12:18pm.

At the moment, I have a rather large If/And formula, structured so that if the previous cell is less than a break time start, and the time in the current cell is greater than a break start time, the appropriate amount of time will be added to the current cell. This of course creates a circular reference, which is where I am running into trouble.

Here is my current If structure:

=(IF(AND(T14<=Standards!$E$2,U14>=Standards!$E$5),U14+SUM(Standards!$G$2:Standards!$G$5),IF(AND(T14<=Standards!$E$2,U14>=Standards!$E$4),U14+SUM(Standards!$G$2:Standards!$G$4),IF(AND(T14<=Standards!$E$2,U14>=Standards!$E$3),U14+SUM(Standards!$G$2:Standards!$G$3),IF(AND(T14<=Standards!$E$2,U14>=Standards!$E$2),U14+ Standards!$G$2,IF(AND(T14<= Standards!$E$3,U14>= Standards!$E$5),U14+SUM(Standards!$G$2:Standards!$G$4),IF(AND(T14<= Standards!$E$3,U14>= Standards!$E$4),U14+SUM(Standards!$G$2:Standards!$G$3),IF(AND(T14<= Standards!$E$3,U14>= Standards!$E$3),U14+ Standards!$G$3,IF(AND(T14<= Standards!$E$4,U14>= Standards!$E$5),U14+SUM(Standards!$G$4:Standards!$G$5),IF(AND(T14<= Standards!$E$4,U14>= Standards!$E$4),U14+ Standards!$G$4,IF(AND(T14<= Standards!$E$5,U14>= Standards!$E$5),U14+Standards!$G$5, T14+U13)))))))))))

And this is what standards it is drawing from:

\1

If anyone has any ideas on how to accomplish this without a circular reference, or has advice on how best to set this circular reference up, your feedback would be appreciated.

Let me know if there is anything I left out, or something you would like explained further!

Edit: I also asked this question on Mr. Excel.

[arthurbr]

Hi and welcome to the board.
Please add your examples as sheets instead of pictures - Thx

[Greg_2]

Hi and welcome to the board.
Please add your examples as sheets instead of pictures - Thx

Here is the spreadsheet. Another restriction of the times in the "Completion Time" field is that they can only be between 7:00AM and 7:00PM, as that is what times the shift goes from. I left the code that I want to implement toward the bottom of the "Pred" sheet.

If anything doesn't make sense on any of the sheets, I'll explain further.


Thanks!
New Net Template.xlsm