Hi,
After many attempts, no success yet. I wish to calculate the Max and Min days of credit (counting from the following month) by FIFO, for each outbound payment.
How should I approach it?
I will very much appreciate any assistance.
My table looks like that:
In the 'Max days of credit' column I tried the formula:
=IF(AND(J3>J2, D3>0, $B3>ADDRESS(J3,9)), $B3-ADDRESS(J3,9),IF(AND(J3>J2, D3>0, $B3<=ADDRESS(J3,9)), $B3-ADDRESS(J3,1), ""))
After many attempts, no success yet. I wish to calculate the Max and Min days of credit (counting from the following month) by FIFO, for each outbound payment.
How should I approach it?
I will very much appreciate any assistance.
My table looks like that:
opening balances: | 25000 | ||||||||
invoice date (day of event) | payment day | debt amount | payment amount | cluster outbound | cluster inbound | next month | count closed invoices | max days of credit | min days of credit |
1/7/2019 | 2/21/2019 | 70277 | 0 | 70277 | 0 | 2/1/2019 | 3 | ||
3/13/2019 | 5/12/2019 | 49736 | 0 | 120013 | 0 | 4/1/2019 | 3 | ||
3/19/2019 | 4/4/2019 | 47069 | 0 | 167082 | 0 | 4/1/2019 | 3 | ||
4/25/2019 | 5/18/2019 | 0 | 20527 | 167082 | 20527 | 5/1/2019 | 3 | ||
5/16/2019 | 8/12/2019 | 0 | 20390 | 167082 | 40917 | 6/1/2019 | 3 | ||
6/13/2019 | 9/2/2019 | 33659 | 0 | 200741 | 40917 | 7/1/2019 | 3 | ||
6/28/2019 | 8/11/2019 | 11501 | 0 | 212242 | 40917 | 7/1/2019 | 3 | ||
8/23/2019 | 11/17/2019 | 32789 | 0 | 245031 | 40917 | 9/1/2019 | 3 | ||
8/27/2019 | 10/15/2019 | 20807 | 0 | 265838 | 40917 | 9/1/2019 | 3 | ||
8/31/2019 | 10/9/2019 | 16006 | 0 | 281844 | 40917 | 9/1/2019 | 3 | ||
9/17/2019 | 11/9/2019 | 15107 | 0 | 296951 | 40917 | 10/1/2019 | 3 | ||
9/22/2019 | 10/24/2019 | 0 | 4219 | 296951 | 45136 | 10/1/2019 | 3 | ||
9/29/2019 | 10/26/2019 | 0 | 31043 | 296951 | 76179 | 10/1/2019 | 4 | #VALUE! | |
10/3/2019 | 10/12/2019 | 39662 | 0 | 336613 | 76179 | 11/1/2019 | 4 | ||
11/28/2019 | 2/26/2020 | 28509 | 0 | 365122 | 76179 | 12/1/2019 | 4 | ||
12/15/2019 | 2/6/2020 | 0 | 38899 | 365122 | 115078 | 1/1/2020 | 4 | ||
12/23/2019 | 1/29/2020 | 26429 | 0 | 391551 | 115078 | 1/1/2020 | 4 |
In the 'Max days of credit' column I tried the formula:
=IF(AND(J3>J2, D3>0, $B3>ADDRESS(J3,9)), $B3-ADDRESS(J3,9),IF(AND(J3>J2, D3>0, $B3<=ADDRESS(J3,9)), $B3-ADDRESS(J3,1), ""))