How can I find the previous date/value with LOOKUP function based on a criteria?

Question

I've been scratching my head round this thing.

I need to perfom the difference between dates, the current date minus the preceding date, while matching an ID (in this case a plate).

I've found a workaround building up the following formula:

Diff = =+IF([@Matrícula]=[@Matrícula];([@[date&time]]-XLOOKUP([@Matrícula];[Matrícula];[date&time];0;0;1));"")

The problem lies (I believe) with the xLookup part: it fixes the first log, then it subtracts the current value from there.

date&time = =+[@Fecha]+[@Hora]

An expected result would be:

I've thought of an Index column or a MAX function into it, but I'm still a newbie...

Thanks for any comment and help.

Answer 1

Following your idea of using Xlookup, I would use it in a reverse search like this, checking for a matching Matricula where the row number is lower. Assumes dates are in ascending order:

=IFERROR([@[Date&Time]]-XLOOKUP(1,([Matricula]=[@Matricula])
   *(ROW([Matricula])<ROW([@Matricula])),[Date&Time],,0,-1),"")

Answer 2

Time Differences in an Excel Table

• Format the Diff column as [h]:mm:ss.

=LET(mdata,INDEX([Matricula],1):[@Matricula],
        fdata,INDEX([Fecha],1):[@Fecha],
        hdata,INDEX([Hora],1):[@Hora],
    d,TAKE(FILTER(fdata+hdata,mdata=[@Matricula]),-2),
    IFERROR(INDEX(d,2)-INDEX(d,1),))