Search code examples
google-sheetsdatediffdate-difference

Google Sheets Automate Days Calculation for different Buy Date and Sell Date

I have a google sheet where Column A has Buy Date and Column L has Sell Date. Column B to K has several other data. There are multiple buys on different dates but selling is on a single date.

What I want is the difference between the buy date as sell date. Buy date will be the 1st date it was purchased and rest other buy dates of the same product is ignored for that calculation. Here is a sample sheet to better understand.

https://docs.google.com/spreadsheets/d/1EC9mRXDV6WYqc0SOBn0tHeIpDm33jMForPgXVEphKKA/edit?usp=sharing

I do not want use scripts or have any circular reference errors for this purpose.

Solution

  • You may try:

    =map(B2:B,L2:L,A2:A,lambda(Σ,Δ,Ξ,if(Δ="",,index(let(
                    Γ,if((B2:B=Σ)*len(L2:L),row(B2:B),),
                    Λ,xlookup(row(Σ)-1,Γ,Γ,,-1)+1,
     days(Δ,xlookup(Σ,indirect("B"&Λ):Σ,indirect("A"&Λ):Ξ,)))))))

    enter image description here