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

I've two columns as follows.

```
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

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.

source to share

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`

below:

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

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`

.

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.

source to share