May 22 2019 08:45 AM
I have the need to calculate the accumulating time and then the subtotal when a value in another column changes. My example would be Column A is date/time; Column B contains one of the following "Start", "Update" and "Stop". I need to sum the time if Column B is "Start" and "Update" and subtotal when it is "Stop" and then start the sum again on the next "Start". Also if I get two "Start"s in a row flag the row of data and either continue the suming or give the user the choice to continue suming or force a subtotal or at least check to see if there was a missing "Stop".
May 22 2019 09:13 AM
@jspry , for the model like this
cumulative time could be calculated as
=IF(($B2="Start")*NOT( (COUNTIF($B1:B$2,"Stop")<>COUNTIF($B1:B$2,"Start"))*(COUNTIF($B1:B$2,"Start")>1)*($B2="Start") ),0,$A2-N($A1)+N($C1))
and Start without previous Stop highlighted with conditional formatting rule with formula
=(COUNTIF($B1:B$2,"Stop")<>COUNTIF($B1:B$2,"Start"))*(COUNTIF($B1:B$2,"Start")>1)*($B2="Start")
applied to the column B
May 22 2019 09:17 AM