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

# Aging

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.

 Date Transaction Type Due Date Aging 11/04/2022 Payment 11/04/2022 10/26/2022 Invoice 11/09/2022 -5 12/20/2022 Payment 12/20/2022 12/06/2022 Invoice 12/20/2022 0 02/18/2022 Payment 02/18/2022 01/26/2022 Invoice 01/26/2022 23 02/14/2022 Invoice 02/14/2022 4 02/02/2022 Invoice 02/02/2022 16
2 Replies
best response confirmed by RPINEDA2 (Copper Contributor)
Solution

# Re: Aging

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

This formula returns the expected results in my sheet.

# Re: Aging

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

# Re: Aging

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

This formula returns the expected results in my sheet.