# 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
2
Views
69
Replies
2
Views
63
Replies
9
Views
278
Replies
3
Views
498
Replies
33
Views
1K

1,147,962
Messages
5,744,057
Members
423,843
Latest member
alex2022

### 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