Forum Discussion
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 |
=IF(B2="Invoice",LOOKUP(2,1/("Payment"=$B$2:B2),$C$2:C2)-C2,"")
This formula returns the expected results in my sheet.
=IF(B2="Invoice",LOOKUP(2,1/("Payment"=$B$2:B2),$C$2:C2)-C2,"")
This formula returns the expected results in my sheet.
- You have saved me from a lot of manual work. Thank you!
