Greetings colleagues - I'm stuck and could use some help. I have a checklist that needs to calculate the Start and End time (hh:mm) given the first start time and duration in minutes for each step in my checklist. All of my columns are Text/Num. I read countless posts and got the Row@row-1 set up so a helper column will pull the end time from the row before. I need that End time from row above to be next row's Start time. I need to be able to remove rows without jacking up the formulas - so they will still calculate correctly (hence using Row@row) and not throw errors if Duration=0 or blank.
I end up with circular reference starting in row 2 if I convert SM or End to a column formula or even copy the formula to subsequent rows. Strangely, when I do copy the formula down, the value in Index2 (which was End1's value, changes to the circular reference error, but it was populating End1 just fine when only Rows 1-2 were set.
Formulas are:
Start: =INDEX@row
SM (convert Start hh:mm to minutes): =IF(Duration@row = "", "", (VALUE(LEFT(Start@row, FIND(":", Start@row) - 1)) * 60) + VALUE(RIGHT(Start@row, 2)))
EM (add Duration to SM): =IF(Duration@row = "", "", IF((SM@row + Duration@row) > 1440, (SM@row + Duration@row) - 1440, (SM@row + Duration@row)))
End (convert EM to hh:mm): =IF(Duration@row = 0, "", FLOOR(EM@row / 60, 1) + ":" + IF(MOD(EM@row, 60) < 10, "0" + MOD(EM@row, 60), MOD(EM@row, 60)))
Index: Row 1 is pulling the checklist start time field in Summary panel, Row 2 is =INDEX(End:End, Row@row - 1, 1), copied to the bottom.
I've tried all manner of tricks to try to workaround this issue - adding more helper columns to break up HH and MM for Start and End, then converting to minutes, etc. They all ended up with that circular reference when pulling Index to Start. Adding Duration to End gave me a weird result: 19:00.0050. Trying to leverage TIME function was not helpful either. Is there a way to convert Index to a plain number that I can then pull into Start, such as Paste Special works (but using a formula)? VALUE formula doesn't seem to do that.
Appreciate any help this community can offer!
