Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Aging

Copper Contributor

Hi,

 

I would like to have an aging on column D. 

 

For the below data I need to add the aging in column D for every invoice. This aging is in relation to the payment date that appears first above the invoice.

 

Below is sample. Is there a formula that I can use to drag down for all the data? Thanks for any suggestion.

 

DateTransaction TypeDue DateAging
11/04/2022Payment11/04/2022 
10/26/2022Invoice11/09/2022-5
12/20/2022Payment12/20/2022 
12/06/2022Invoice12/20/20220
02/18/2022Payment02/18/2022 
01/26/2022Invoice01/26/202223
02/14/2022Invoice02/14/20224
02/02/2022Invoice02/02/202216
2 Replies
best response confirmed by RPINEDA2 (Copper Contributor)
Solution

@RPINEDA2 

=IF(B2="Invoice",LOOKUP(2,1/("Payment"=$B$2:B2),$C$2:C2)-C2,"")

This formula returns the expected results in my sheet.

aging.png

You have saved me from a lot of manual work. Thank you!
1 best response

Accepted Solutions
best response confirmed by RPINEDA2 (Copper Contributor)
Solution

@RPINEDA2 

=IF(B2="Invoice",LOOKUP(2,1/("Payment"=$B$2:B2),$C$2:C2)-C2,"")

This formula returns the expected results in my sheet.

aging.png

View solution in original post