Max and Min days of credit - FIFO

costata

New Member
Joined
Mar 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:

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/20192/21/2019
70277​
0​
70277​
0​
2/1/2019​
3​
3/13/20195/12/2019
49736​
0​
120013​
0​
4/1/2019​
3​
3/19/20194/4/2019
47069​
0​
167082​
0​
4/1/2019​
3​
4/25/20195/18/2019
0​
20527​
167082​
20527​
5/1/2019​
3​
5/16/20198/12/2019
0​
20390​
167082​
40917​
6/1/2019​
3​
6/13/20199/2/2019
33659​
0​
200741​
40917​
7/1/2019​
3​
6/28/20198/11/2019
11501​
0​
212242​
40917​
7/1/2019​
3​
8/23/201911/17/2019
32789​
0​
245031​
40917​
9/1/2019​
3​
8/27/201910/15/2019
20807​
0​
265838​
40917​
9/1/2019​
3​
8/31/201910/9/2019
16006​
0​
281844​
40917​
9/1/2019​
3​
9/17/201911/9/2019
15107​
0​
296951​
40917​
10/1/2019​
3​
9/22/201910/24/2019
0​
4219​
296951​
45136​
10/1/2019​
3​
9/29/201910/26/2019
0​
31043​
296951​
76179​
10/1/2019​
4​
#VALUE!​
10/3/201910/12/2019
39662​
0​
336613​
76179​
11/1/2019​
4​
11/28/20192/26/2020
28509​
0​
365122​
76179​
12/1/2019​
4​
12/15/20192/6/2020
0​
38899​
365122​
115078​
1/1/2020​
4​
12/23/20191/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), ""))
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
post the result table you want please
 

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
It would help if you show the column names in your data table and/or result table you want. That would help us understand, why you use by J3>j2 d3>0 why you use address(j3,9) etc.
 

costata

New Member
Joined
Mar 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Sorry for being unclear. I'll try again.
Let's say I have this table:

A​
B​
C​
D​
G​
1​
invoice date (day of event)​
payment day​
debt amount​
payment amount​
Max days of payment​
2​
1/7/20192/21/2019
70277​
0​
3​
3/13/20195/12/2019
49736​
0​
4​
3/19/20194/4/2019
47069​
0​
5​
4/25/20195/18/2019
0​
20527​
6​
5/16/20198/12/2019
0​
20390​
7​
6/13/20199/2/2019
33659​
0​
8​
6/28/20198/11/2019
11501​
0​
9​
8/23/201911/17/2019
32789​
0​
10​
8/27/201910/15/2019
20807​
0​
11​
8/31/201910/9/2019
16006​
0​
12​
9/17/201911/9/2019
15107​
0​
13​
9/22/201910/24/2019
0​
4219​
14​
9/29/201910/26/2019
0​
31043​
292​
15​
10/3/201910/12/2019
39662​
0​
16​
11/28/20192/26/2020
28509​
0​
17​
12/15/20192/6/2020
0​
38899​
18​
12/23/20191/29/2020
26429​
0​
19​
1/9/20203/25/2020
28819​
0​
20​
1/26/20203/18/2020
14504​
0​
21​
1/29/20202/26/2020
10366​
0​
22​
3/18/20206/23/2020
9952​
0​
23​
4/28/20207/11/2020
13868​
0​
24​
5/3/20205/28/2020
44974​
0​
25​
5/4/20207/23/2020
4354​
0​
26​
5/11/20207/28/2020
0​
3842​
27​
6/3/20208/11/2020
0​
29771​
517​
28​
6/4/20207/2/2020
10897​
0​
29​
7/8/20209/24/2020
0​
27424​
555​

Once the invoice is fully covered by the payment, I wish to calculate how many days passed since the invoice date (column A), assuming FIFO.
for example, on 10/26/2019 the invoice dated 1/7/2019 is fully paid, hence it took 292 days total.
Similarly, I wish to calculate how many days passed from the invoice date to the first payment for the relevant invoice (but once I'll figure how to manage the earlier this one should be easy).

Tks for helping
 

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
Book1
ABCDEFGHIJKL
1op.debitop.creditinv.dtpmt.dtinv.amtpmt.amtcum.drcum.crrow.when.cumpmt.exceeds.inv.amt+prev.balcol k+1dt.pmt.fullCr.days
21,234.001/7/20192/21/201970,277.000.0071,511.000.00121310/26/19292
33/13/20195/12/201949,736.000.00120,013.000.00252608/11/20517
43/19/20194/4/201947,069.000.00167,082.000.00272809/24/20555
54/25/20195/18/20190.0020,527.00167,082.0020,527.00272809/24/20518
65/16/20198/12/20190.0020,390.00167,082.0040,917.00272809/24/20497
76/13/20199/2/201933,659.000.00200,741.0040,917.00282901/00/00 
86/28/20198/11/201911,501.000.00212,242.0040,917.00282901/00/00 
98/23/201911/17/201932,789.000.00245,031.0040,917.00282901/00/00 
108/27/201910/15/201920,807.000.00265,838.0040,917.00282901/00/00 
118/31/201910/9/201916,006.000.00281,844.0040,917.00282901/00/00 
129/17/201911/9/201915,107.000.00296,951.0040,917.00282901/00/00 
139/22/201910/24/20190.004,219.00296,951.0045,136.00282901/00/00 
149/29/201910/26/20190.0031,043.00296,951.0076,179.00282901/00/00 
1510/3/201910/12/201939,662.000.00336,613.0076,179.00282901/00/00 
1611/28/20192/26/202028,509.000.00365,122.0076,179.00282901/00/00 
1712/15/20192/6/20200.0038,899.00365,122.00115,078.00282901/00/00 
1812/23/20191/29/202026,429.000.00391,551.00115,078.00282901/00/00 
191/9/20203/25/202028,819.000.00420,370.00115,078.00282901/00/00 
201/26/20203/18/202014,504.000.00434,874.00115,078.00282901/00/00 
211/29/20202/26/202010,366.000.00445,240.00115,078.00282901/00/00 
223/18/20206/23/20209,952.000.00455,192.00115,078.00282901/00/00 
234/28/20207/11/202013,868.000.00469,060.00115,078.00282901/00/00 
245/3/20205/28/202044,974.000.00514,034.00115,078.00282901/00/00 
255/4/20207/23/20204,354.000.00518,388.00115,078.00282901/00/00 
265/11/20207/28/20200.003,842.00518,388.00118,920.00282901/00/00 
276/3/20208/11/20200.0029,771.00518,388.00148,691.00282901/00/00 
286/4/20207/2/202010,897.000.00529,285.00148,691.00282901/00/00 
297/8/20209/24/20200.0027,424.00529,285.00176,115.00282901/00/00 
Sheet5
Cell Formulas
RangeFormula
G2G2=E2+A2
H2H2=B2+F2
I2:I29I2=MATCH($G2,$H$2:$H$2000,1)
J2:J29J2=$I2+1
K2:K29K2=TEXT(INDEX($D$2:$D$1997,$J2),"MM/DD/YY")
L2:L29L2=IF(ISERROR(DATEDIF(C2,K2,"d")),"",DATEDIF(C2,K2,"d"))
G3G3=E2+E3
G5:H29,H3:H4,G4H3=H2+F3

it should work 99% of the time. if some one puts negative amt and column H cumulative credit is not in ascending order, match function might fail.
Pl mark this as a solution, if it helps you
 
Last edited:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top