excel - Calculate time elapsed between cells -
i'm looking way calculate time elapsed (passed) between 2 reoccurring events. excel sheet looks this.
cell cell b cell c 1 14-aug-17 8:59:13 pm quota recovery 2 14-aug-17 8:56:12 pm quota violation 3 14-aug-17 6:00:12 quota recovery 4 14-aug-17 5:36:12 quota violation 5 14-aug-17 4:00:12 quota recovery 6 14-aug-17 3:51:12 quota violation
something simple b1-b2 give me desired result 1 entry have close thousands cells need perform same calculation on.
is possible automatically calculate , add time between each violation , recovery whole excel sheet give me total amount of time quota violated?
=sumproduct(((a1:a6)+(b1:b6))*(c1:c6="quota recovery"))-sumproduct(((a1:a6)+(b1:b6))*(c1:c6="quota violation"))
similar pnuts comment, using array function , bypassing helper cell. since array formula, make sure define range need.
Comments
Post a Comment