Forum Discussion

jspry's avatar
jspry
Copper Contributor
May 22, 2019

total and subtotal time

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 "Sta...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 22, 2019

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