Calculating the number of days to payment

Vegas01

New Member
Joined
Jun 15, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Nov 6, 2013
Messages
736
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
Joined
Jun 15, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Nov 6, 2013
Messages
736
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,""),"")
 
Solution

Vegas01

New Member
Joined
Jun 15, 2021
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Thanks for that it now works, Stay safe.
 

Forum statistics

Threads
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.
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