jeudi 30 septembre 2021

Obtain days between dates in array of different ID's

I would like to get the days and total days between each date for only the same row IDs. So far, found some code that can help me get the days between each date and separately determine if each current row is the same or different from the previous row. I'm unable to join both in order to only get the corresponding days between each date corresponding to the same ID. Desired result

Code inside C2:

={"Days Since Previous Payment";ArrayFormula(IFNA(vlookup(row(A3:A),{query(filter(row(A3:A),B3:B>0),"Select * offset 1",0),filter(if({filter(A3:A,B3:B>0);0}*{0;filter(A3:A,B3:B>0)},{filter(A3:A,B3:B>0);0}-{0;filter(A3:A,B3:B>0)},),if({filter(A3:A,B3:B>0);0}*{0;filter(A3:A,B3:B>0)},{filter(A3:A,B3:B>0);0}-{0;filter(A3:A,B3:B>0)},)<>"")},2,0)))}

Code inside E3 and on until E18:

=ArrayFormula(IF(INDIRECT("B"&(ROW()))<>INDIRECT("B"&(ROW()-1)),INDIRECT("B"&(ROW())),INDIRECT("B"&(ROW()-1))))

Just in case, here is the dummy page: https://docs.google.com/spreadsheets/d/1HSZKq5JhrHRtl-XPQ6QJF1wNX4e2_5jmyjNK2pPZpDM/edit?usp=sharing

Aucun commentaire:

Enregistrer un commentaire