How do I add time without creating a circular reference?

[jayclinton]

Please see the attached. When I am done I will clean up the sheet, but the issue I am having is I need to add any of the times starting from T4 down to the I (End Time) column. The downtime column (J) will be manually altered by either dropdown, or manual entry, so I can't put a formula there because this sheet is dynamic and will be reused. Is there a way to keep the formula I have in column I and add the corresponding cell from T? I always want to add Column T to column I.

The purpose of this is employee break times. If the Start Time and End Time of the production run falls within their break time AB11:AB13 then that will add 0:40 min to the run time. I was having issues with the time in column T so I had to figure out a way so there wasn't a date attached. So that is why that is there.

So since their first break is at 10:50 AM, I7 should look at T7 and add what's there to the current time extending that from 11:00 to 11:40. So hopefully that makes sense.


Any help would be appreciated. My brain has shut down. I'm completely stuck.

[6StringJazzer]

Your description gives some insight as to how your sheet works but I can't figure out what your question is.

This looks like a question: "Is there a way to keep the formula I have in column I and add the corresponding cell from T?" but put the result where? Please give an example of a cell you want to change and what result you want to see in it.

[jayclinton]

I need my result in Column I. I need to add on to that formula. So for example, I7 would also add in the 0:40 from T7. So H7 would say 10:00 AM, I7 would say 11:40. I still need the current formula there to account for two other options.

[6StringJazzer]

So as you say in your title, that creates a circular reference. So there is probably a flaw in the thinking behind the desired logic.

What is the meaning of the data in column T?

Another question that will help me understand your data and this design: Why are columns R and S needed? Column R is simply the time found in column H of the same row. For example, R7 is

=TIME(HOUR(H7),MINUTE(H7),SECOND(H7))

but this is exactly the same as

=H7

so it seems like it would be simpler for the formula in column T7 to refer directly to H7, instead of using R7 as a pass-through.

Same discussion for column S.

[jayclinton]

Column T is looking to see if Columns H and I fall within the employees break schedule. If it does then 40 minutes are added to the production run thus extending the time it would take to do each run (each row). I added Columns R & S because my formula in T wasn't calculating once I exceeded one day's worth. I don't know, but I assumed there was a date behind the scenes associated with it. So I tried to figure out a way to not have a date and did columns R & S. That worked for me so eventually I will hide them unless there is a better way. I tried all different kinds of formatting only to get the same result.

Ideally I just wanted to add the + T7 and the end of the formula in I7 for example. If that wasn't a circular reference it would do what I need. =IF(A7="FC",H7+$R$1,IF(A7="SW",H7+$R$2,H7+$S$1+T7)))

[AliGW]

Jayclinton - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[6StringJazzer]

Column T is looking to see if Columns H and I fall within the employees break schedule. If it does then 40 minutes are added to the production run thus extending the time it would take to do each run (each row).

This is your problem. This description is a circular description, and so you get a circular reference when you try to do it in Excel. Here is what you are saying:

"If the beginning and end time are within the break schedule, add 40 minutes to the end time."

You can't use the end time to determine what the end time should be.

What you can do is create another column for something like "adjusted end time" that would be I + T. Then use that value in the formula in the following row for H.

[jayclinton]

Thanks Jazzer! I couldn't wrap my head around it, but I got it now. Your suggestions works great. Thank you for your time on this.