Calculating the number of days to payment

Vegas01

New Member
Hi everyone, seeing if anyone can help with a solution to my problem. I need to work out on the invoice how many days it took to get paid. the results we are looking for are in column O. Thanks in advance

Test data.xlsx
ABCDEFGHIJKLMNO
1 Date Type Inv Number Inv Amt DR/CR Pay Amt DR/CR 2 Acc Balance DR/CR 3 Creditors No Week invoice received Result that is needed
21/06/2021Inv1000491,047.90CR1,047.90CRQ1040049
311/05/2021Inv71292833.00CR33.00CRQ1042046 2
412/05/2021O/Bal33.00CRQ1042046 1
513/05/2021Pay002462layerEFT33.00-Q10420462
63/02/2021Inv096462243.10CR243.10CRQ1091532 1
73/02/2021O/Bal243.10CRQ1091532
84/02/2021Pay002554layerEFT243.10-Q10915321
919/02/2021Inv0098771,219.63CR1,219.63CRQ1091534 6
1024/02/2021O/Bal1,219.63CRQ1091535
1125/02/2021Pay002560layerEFT1,219.63-Q10915356
1212/01/2021Inv00935535.00CR35.00CRQ1100029 2
1313/01/2021O/Bal35.00CRQ1100029
1414/01/2021Pay002548layerEFT35.00-Q11000292
1512/01/2021Inv31352284.32CR84.32CRQ3000229 2
1613/01/2021O/Bal84.32CRQ3000229
1714/01/2021Pay002548layerEFT84.32-Q30002292
1814/01/2021Inv318149183.94CR183.94CRQ3000229 7
1920/01/2021O/Bal183.94CRQ3000230
2021/01/2021Pay002550layerEFT183.94-Q30002307
2131/05/2021Inv400550362.24CR362.24CRQ3000249
2210/03/2021Inv1393881,346.40CR1,346.40CRQ3000337 1
2310/03/2021O/Bal1,346.40CRQ3000337
2411/03/2021Pay002564layerEFT1,346.40-Q30003371
2523/12/2020Inv001011540.48CR11,216.12Q3001126 7
2623/12/2020Inv001197257.34CR10,958.78Q3001126 7
2723/12/2020Inv045664458.69CR10,500.09Q3001126 7
2823/12/2020Inv045665613.26CR9,886.83Q3001126 7
2923/12/2020Inv045666140.52CR9,746.31Q3001126 7
3023/12/2020Inv0456672,385.07CR7,361.24Q3001126 7
3123/12/2020Inv0456751,548.50CR5,812.74Q3001126 7
3223/12/2020Inv045676413.40CR5,399.34Q3001126 7
3323/12/2020Inv045677831.08CR4,568.26Q3001126 7
3423/12/2020Inv045678716.98CR3,851.28Q3001126 7
3523/12/2020Inv045679779.73CR3,071.55Q3001126 7
3623/12/2020Inv045680264.56CR2,806.99Q3001126 7
3723/12/2020Inv0456821,166.09CR1,640.90Q3001126 7
3823/12/2020Inv045683215.19CR1,425.71Q3001126 7
3923/12/2020Inv045684196.56CR1,229.15Q3001126 7
4023/12/2020Inv045685694.38CR534.77Q3001126 7
4123/12/2020Inv045686534.77CR-Q3001126 7
4230/12/2020Pay015213PAYMENT87.42CR87.42CRQ30011277
4330/12/2020Pay015221PAYMENT11,844.0211,756.60Q3001127
445/01/2021Inv03791497.00CR-Q3001128 1
456/01/2021Pay015233PAYMENT97.0097.00Q30011281
4611/01/2021Inv045687715.64CR3,651.35Q3001129 2
4711/01/2021Inv045689669.77CR2,981.58Q3001129 2
4811/01/2021Inv04569199.14CR2,882.44Q3001129 2
4911/01/2021Inv045692646.69CR2,235.75Q3001129 2
5011/01/2021Inv0456931,466.81CR768.94Q3001129 2
5111/01/2021Inv045694120.27CR648.67Q3001129 2
5211/01/2021Inv045696345.84CR302.83Q3001129 2
5311/01/2021Inv045698302.83CR-Q3001129 2
5412/01/2021Inv001080168.38CR4,366.99Q3001129 1
5513/01/2021Pay015244PAYMENT4,535.374,535.37Q30011292
5618/01/2021Inv0141602,529.16CR5,543.11Q3001130 2
5718/01/2021Inv045607279.69CR4,341.86Q3001130 2
5818/01/2021Inv045663149.49CR4,192.37Q3001130 2
5918/01/2021Inv056874509.74CR629.26Q3001130 2
6018/01/2021Inv070325509.74CR119.52Q3001130 2
6118/01/2021Inv070361119.52CR-Q3001130 2
6219/01/2021Inv037902921.56CR4,621.55Q3001130 1
6319/01/2021Inv0456901,266.93CR2,925.44Q3001130 1
6419/01/2021Inv0456971,443.58CR1,481.86Q3001130 1
6519/01/2021Inv045700342.86CR1,139.00Q3001130 1
6620/01/2021Pay015274PAYMENT8,072.278,072.27Q30011302
Sheet1
Cell Formulas
RangeFormula
M2:M66M2=VLOOKUP(A2,'[QLD - Payment time report - Creditors.xlsx]Daily Breakdown'!A:D,3,FALSE)
N2:N66N2=IF(B2="Pay",IFERROR(A2-INDEX(A:A,AGGREGATE(15,6,ROW(\$A\$1:\$A1)/((\$L\$1:\$L1=L1)*(\$B\$1:\$B1="Inv")*(ROW(\$H\$1:\$H1)>MAX((ROW(\$H\$1:\$H1)*(\$H\$1:\$H1>0))))),1)),""),"")
O9,O15,O12O9=+A11-A9

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

kvsrinivasamurthy

Well-known Member
In R3 then copy down

=IF(\$B3="Inv",IFERROR(INDEX(\$A:\$A,AGGREGATE(15,6,ROW(\$A4:\$A\$67)/((\$B4:\$B\$67="Pay")*(\$H4:\$H\$67=\$F3)),1))-\$A3,""),"")

Vegas01

New Member
In R3 then copy down

=IF(\$B3="Inv",IFERROR(INDEX(\$A:\$A,AGGREGATE(15,6,ROW(\$A4:\$A\$67)/((\$B4:\$B\$67="Pay")*(\$H4:\$H\$67=\$F3)),1))-\$A3,""),"")
Thanks a lot for that, that works where there is 1 invoice but does not worked when a payment is paying a number of invoices. ie in rows 25 to 41.

Do you think there is a solution or is this problem impossible to solve? Thanks in advance for your help.

kvsrinivasamurthy

Well-known Member
Try this . Working Ok.
In R3 and down.

=IF(AND(\$B3="Inv",INDEX(\$A:\$A,AGGREGATE(15,6,ROW(\$A4:\$A\$67)/((\$B4:\$B\$67="Pay")),1))>=\$A3),IFERROR(INDEX(\$A:\$A,AGGREGATE(15,6,ROW(\$A4:\$A\$67)/((\$B4:\$B\$67="Pay")),1))-\$A3,""),"")

Vegas01

New Member
Thanks for that it now works, Stay safe.

kvsrinivasamurthy

Well-known Member
Thanks for feedback

Replies
3
Views
127
Replies
2
Views
158
Replies
2
Views
111
Replies
9
Views
338
Replies
3
Views
703

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

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.

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