Date subtraction

[gg]

I’m trying to create an equation that finds the difference between two dates. There are four columns: date, name, text1, text2; they are filtered bydate, oldest to newest. I need to find out if name equals text2, then whatis the date; then I need to search for the next time that name appears in the column and equals text1, and tell me that date. Then I want to know thedifference in those two dates.

Here’s an example:
10/12/2011 Bill C PC
10/20/2011 Jane C NA
12/22/2011 Jane A NA
02/10/2012 Bill A NA

So, if row 1 contains Bill’s name in column 2 and PC in column 4, what isthe date in column one? Then find the next row that contains Bill’s nameand the letter A in column 3, find the date in that row, and give the difference between the two. Ideally it would return the difference as the number of days, so this would return 121.

 

[isabelle]

hi gg,

=SUMPRODUCT(--($A$2:$A$20)*($B$2:$B$20="Bill")*($C$2:$C$20="A"))-SUMPRODUCT(--($A$2:$A$20)*($B$2:$B$20="Bill")*($D$2:$D$20="PC"))

isabelle



Le 2012-12-13 14:44, gg a écrit :

I’m trying to create an equation that finds the difference between two dates.

There are four columns: date, name, text1, text2; they are filtered by
date, oldest to newest.

I need to find out if name equals text2, then what is the date; then I
need to search for the next time that name appears

in the column and equals text1, and tell me that date. Then I want to
know the difference in those two dates.

Here’s an example:
10/12/2011 Bill C PC
10/20/2011 Jane C NA
12/22/2011 Jane A NA
02/10/2012 Bill A NA

So, if row 1 contains Bill’s name in column 2 and PC in column 4, what is the date in column one?

Then find the next row that contains Bill’s name and the letter A in
column 3, find the date in that row,

and give the difference between the two. Ideally it would return the
difference as the number of days, so this would return 121.