# Max and Min days of credit - FIFO

#### costata

##### New Member
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/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:

### 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
post the result table you want please

#### jsb1921

##### Board Regular
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
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/2019 2/21/2019 70277​ 0​ ​ 3​ 3/13/2019 5/12/2019 49736​ 0​ ​ 4​ 3/19/2019 4/4/2019 47069​ 0​ ​ 5​ 4/25/2019 5/18/2019 0​ 20527​ ​ 6​ 5/16/2019 8/12/2019 0​ 20390​ ​ 7​ 6/13/2019 9/2/2019 33659​ 0​ ​ 8​ 6/28/2019 8/11/2019 11501​ 0​ ​ 9​ 8/23/2019 11/17/2019 32789​ 0​ ​ 10​ 8/27/2019 10/15/2019 20807​ 0​ ​ 11​ 8/31/2019 10/9/2019 16006​ 0​ ​ 12​ 9/17/2019 11/9/2019 15107​ 0​ ​ 13​ 9/22/2019 10/24/2019 0​ 4219​ ​ 14​ 9/29/2019 10/26/2019 0​ 31043​ 292​ 15​ 10/3/2019 10/12/2019 39662​ 0​ ​ 16​ 11/28/2019 2/26/2020 28509​ 0​ ​ 17​ 12/15/2019 2/6/2020 0​ 38899​ ​ 18​ 12/23/2019 1/29/2020 26429​ 0​ ​ 19​ 1/9/2020 3/25/2020 28819​ 0​ ​ 20​ 1/26/2020 3/18/2020 14504​ 0​ ​ 21​ 1/29/2020 2/26/2020 10366​ 0​ ​ 22​ 3/18/2020 6/23/2020 9952​ 0​ ​ 23​ 4/28/2020 7/11/2020 13868​ 0​ ​ 24​ 5/3/2020 5/28/2020 44974​ 0​ ​ 25​ 5/4/2020 7/23/2020 4354​ 0​ ​ 26​ 5/11/2020 7/28/2020 0​ 3842​ ​ 27​ 6/3/2020 8/11/2020 0​ 29771​ 517​ 28​ 6/4/2020 7/2/2020 10897​ 0​ ​ 29​ 7/8/2020 9/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
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:

#### costata

##### New Member
Exactly what I needed. Thank you!

Replies
1
Views
226
Replies
17
Views
539
Replies
3
Views
148
Replies
11
Views
263
Replies
2
Views
182

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.

### Which adblocker are you using?

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

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