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 & Cell: A1 in the cell C5, A9 – A6 in C9 and so on..
So basically, we have to look at the Column B for a change in the text value – and subtract the corresponding column A values. Hope my explanation of the problem is understandable. If not – do let me know. I can help to understand in a better way if possible.
Could you use one more column? if so the solution may look like this:
let D be additional column
 in D1 put formula: =A1

in D2 put formula: =IF(B1=”Text B”, A2, D1)
 drag down formula from D2

in C2 put formula: =IF(B2=”Text B”, A2D1, “”)
 drag down formula from C2
Answer：
Here are 2 small changes to the C2 formula in @Agata Powalka’s answer, which is good:
=IF(B2 = "Text B", IF(B1 = "Text B", A2, A2D2), "")
This handles the case when a Text B
row occurs immediately below another, where it will display whatever’s in column A
. I also changed A2D1
to A2D2
just for clarity; it doesn’t change the results.
Answer：
In Google Sheets, it’s a bit tricky but can be done with formulae only. I Assumed there’s no header and text to find is 'B'
, and A:A
is date and B:B
is text. Add a column D
and set D1
to below:
=index(
$A:$A,
index(
transpose(split("1 "&join(" ", arrayformula(if($B:$B="B", row($B:$B), ""))), " ")),
countif($B$1:$B1, "B"),
1
)
)
Above formula will result the last date which has text 'B'
. Now copy D1
and drag down. Now result column C
is trivial. Type below on C1
and drag down.
=if(B1="B", A1D1, "")
Sample file is here and below is its screenshot. I set the format of column C
to duration
and emphasized some dates on column A
.
If you prefer custom function or running a script, dozens line of GAS can do this job too. Personally, I prefer formulae, even if it’s kind of ugly.
Tags: excelexcel