Calculating a running tally of time

Gwendolene Day

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!

Mark.poole

@Gwendolene Day

What is happening is you are indirectly referencing your Index column to itself starting with line 2 because it is pulling information following a path to the Start column. Which directly references the Index column. Thus blocking the rest of your formulas. To fix this issue try moving the formula to a new column. This will remove the circular reference as you are no longer indirectly reverencing itself.