CODEWITHSUNDEEP
exceldatediff
WixLove
WixLove

Reputation: 73

DateDif based on criteria

I have data that look like this:

enter image description here

I'd like, based on ID to get the DateDif in days of Initials and any other reassessments (Annuals and Semiannuals). Column D shows what I'd like to see while column E has notes on which cells the DateDif should work on.

I don't know where to start with this..do I use DateDif or Ifs or? Thank you for reviewing.

Upvotes: 1

Views: 767

Answers (2)

user4039065
user4039065

Reputation:

You can use the INDEX function to retrieve the correct minuend and subtrahend of the subtraction formula.

        DATEDIFF staggered

The formula in D2 is,

=IF(B2="initial", "", C2-INDEX(C$1:C1, MAX(INDEX(ROW($1:1)*(B$1:B1="initial"), , ))))

Fill down as necessary.

The formula looks to column A for the last occurrence of the word 'Initial' according to the row it is currently on and subtracts the date from that row from the date in the current row.

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6206

Your value for D4 is wrong, it should be 289

Starting in D2

=IF(A2<>A1,"",C2-VLOOKUP(A2,A:C,3,0))

If A <> the value above it then it is nothing otherwise take the value in C and minus the initial value for that ID (Done using VLookup and 0 at the end will give an exact match)

Upvotes: 1

Related Questions