Finding two values ​​in a column and calculating the difference between adjacent cells

I've two columns as follows.

enter image description here

A              B            C
1   1/9/2016  12:57:48 PM       Text A  
2   1/9/2016 1:04:48 PM     Text A  
3   1/9/2016 1:05:04 PM     Text A  
4   1/9/2016 1:05:14 PM     Text A  
5   1/9/2016 1:05:15 PM     Text B      A5 – A1
6   1/9/2016 1:05:38 PM     Text A  
7   1/11/2016 9:57:12 AM        Text A  
8   1/15/2016 10:42:55 AM       Text A  
9   1/15/2016 10:45:21 AM       Text B      A9 – A6
10  1/15/2016 10:58:12 AM       Text A  


Query: I need the difference of Cell: A5 and Cell: A1 in cell C5, A9 - A6 in C9, etc. So basically, we have to look at column B to change the text value and subtract the corresponding values ​​in column A. Hopefully my explanation of the problem is clear. If not, let me know. I can help to understand better if possible.


source to share

3 answers

Could you use another column? if this solution might look like:

let D be an extra column

  • in the formula D1: = A1
  • in the formula D2: = IF (B1 = "Text B", A2, D1)

    • drag formula from D2
  • in C2 puts the formula: = IF (B2 = "Text B", A2-D1, "")

    • drag formula from C2


Here are two small changes to the C2 formula in @ Agata Powalka's answer, which is good:

=IF(B2 = "Text B", IF(B1 = "Text B", A2, A2-D2), "")


This handles the case where a row Text B

occurs immediately below another, where it will display whatever is in the column A

. I also changed A2-D1

to A2-D2

just for clarity; it doesn't change the results.



In Google Sheets, this is a little tricky, but can only be done with formulas. I am assuming there is no title and text to search for 'B'

, but rather A:A

is date and B:B

is text. Add a column D

and set D1


     transpose(split("1 "&join(" ", arrayformula(if($B:$B="B", row($B:$B), ""))), " ")), 
     countif($B$1:$B1, "B"), 


The above formula will result in the latest date that has text 'B'

. Now copy D1

and drag down. Now the result column C

is trivial. Enter below C1

and drag down.

=if(B1="B", A1-D1, "")


An example of a file is here , and below is a screenshot of it. I set the column format C

to duration

and highlighted some dates in the column A


enter image description here

If you prefer a custom function or run a script, the tenth line of GAS can do the job as well. I personally prefer formulas, even if they are disgusting.



All Articles