• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

Attachments

  • Chandoo.xlsx
    8.8 KB · Views: 10
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.
Code:
=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:
Code:
=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.
 
A slight variation using 365, that will avoid relative referencing,
Code:
= 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.
 

Attachments

  • Chandoo.xlsx
    12.6 KB · Views: 6
Last edited:
Back
Top