Help to find out time difference of two occurrence of a value

[vijith.tharammal]

Could any one help me to find the time difference between adjacent occurrence of a value in excel.

 

[p45cal]

This formula in cell E5 will return the Time Difference to previous Purchase (your cell I4) as opposed to time difference between adjacent occurrence because this formula doesn't care about the order of the data in columns C & D; it will find the time of the most recent purchase of the same product before the date/time on that row.

=IF(MAXIFS($D$5:$D$10,$D$5:$D$10,"<" & $D5,$C$5:$C$10,$C5)=0,"First purchase",D5-MAXIFS($D$5:$D$10,$D$5:$D$10,"<" & $D5,$C$5:$C$10,$C5))

Your expected results show that you have confused month and day in column D, swapping those around returns your expected results.
If you want 0 instead of "First Purchase" then replace that in the formula (use a zero without the quote marks), but be aware that that would imply no time between that purchase and the non-existent previous purchase.
If you have a more recent version of Excel then you can try:

=LET(AllDtes,$D$5:$D$10,dte,$D5,max,MAXIFS(AllDtes,AllDtes,"<"&dte,$C$5:$C$10,$C5),IF(max=0,"First purchase",dte-max))

which makes it easier to adjust the formula for different ranges on the sheet, there being only one ref per range instead of multiple refs to the same range.

Both of these formulae can be copied down.

 

[Peter Bartholomew]

A slight variation using 365, that will avoid relative referencing,

= LET(
      priorDate, MAXIFS(purchaseDate, purchaseDate, "<"&purchaseDate, product, product),
      IF(priorDate, purchaseDate - priorDate, 0))
  )

Note: This 'corrected' version uses the chronological prior values rather than relying on the row ordering.

 

[p45cal]

So nice when people say thank you.
Looks like I was the April fool.