Invoiced date & amount to payment date and amount

[totemos]

Hi all,
Hoping to find a solution, to something. Maybe it has already been solved but searching i couldnt find something relative to my problem.

There are data in four columns, seperated by invoice date, invoice amount, payment date and payment amount.

I want to calculate how many days payment have been delayed considering that payments amount is not the excact amount of the invoice. It can be less or more than the invoice amount.

For example :

Invoice Date	Amount	Payment Date	Amount	Days
01/3/2022	5000
03/03/2022	10000
15/04/2022	13000
10/05/2022	8000

Consider the following parameters :

1. Customer have open payment terms for example 100 days from invoice date and then he has to pay cash.
2. I want to be able to predict in quick time what money he has to deposit in order to pass the open terms days that we have agreed.
3. Amount of payments doesnt relate to the amount of the invoice, could be less or more.
4. So, what i need it to be able to calculate haw many days is the delay for each invoice.

regards

Ioannis

 

[etaf]

how about

=IF(A3="","",IF(C3="",TODAY()-A3, IF(D3<>B3,C3-A3,"")))

Book1
	A	B	C	D	E
1
2	nvoice Date	Amount	Payment Date	Amount	Days
3	3/1/22	5000	4/1/22	6000	31
4	3/3/22	10000	4/1/22	900	29
5	4/15/22	13000	5/17/22	13000
6	5/10/22	8000			51
7
Sheet1

Cell Formulas
Range	Formula
E3:E6	E3	=IF(A3="","",IF(C3="",TODAY()-A3, IF(D3<>B3,C3-A3,"")))

you could test the invoice date and only show today() - invoice if over 100 days not paid

a few things possible to do

 

[totemos]

Correct approach but there is a flaw.

Because the payment amount its not the excact amount of the invoice, some times it leaves an amount unpaid or over paid.

The unpaid amount must be transferred to the next payment as also the overpaid amount will reduce next payment invoice amount.

 

[etaf]

is this what you mean

Or
not sure i see that needed in the original request

can you give some examples of how that would be shown,
if you want to alter an entry - so if you make an invoice for 6000, BUT they owe 1000 from sometime before , and now you want excel to change the 6000 entered into 7000 you will probably need VBA to do that - which is not my area

Book1
	A	B	C	D	E	F
1
2	nvoice Date	Amount	Payment Date	Amount	Debit/Credit	Days
3	3/1/22	5000	4/1/22	6000	($1,000.00)	31
4	3/3/22	10000	4/1/22	900	$8,100.00	29
5	4/15/22	13000	5/17/22	13000	$8,100.00
6	5/10/22	8000			$16,100.00	51
Sheet1

Cell Formulas
Range	Formula
E3:E6	E3	=IF(A3="","",SUM($B$3:B3)-SUM($D$3:D3))
F3:F6	F3	=IF(A3="","",IF(C3="",TODAY()-A3, IF(D3<>B3,C3-A3,"")))

may want to change the formatting to show RED / Black the other way round

 

[totemos]

Yes the point is to calculate what amount is left from payment and relate to the next payment. According to this the calculated days differ.

 

[etaf]

how do the days differ?
can you give examples of expected results and why

 

[totemos]

for example. invoice date 01/01/2022 invoice amount 1000$
Payment 1) 800 $ on date 30/03/2022
Payment 2) 200 $ (or more if there is newer invoice amount) 30/04/2022

1rst payment has 88 days payment
2nd payment has 119 days.

Imagine that you agree with a customer that he can buy from your store with 100 days open Credit.

Then you ask him to pay, but he makes partial payments that it must not exceed 100 days due to the agreement.

So you must check to always be less than 100 days.

Hope im explaining ok

 

[etaf]

ok,
so i have added your example

Book1
	A	B	C	D	E	F	G
1
2	Invoice Date	Amount	Payment Date	Amount	Debit/Credit		Days
3	1/1/22	1000	3/30/22	800	$200.00		88
4			4/30/22	200	$0.00
5	New date may occur	new amount
6
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(A3="","",SUM($B$3:B3)-SUM($D$3:D3))
G3	G3	=IF(A3="","",IF(C3="",TODAY()-A3, IF(D3<>B3,C3-A3,"")))
E4	E4	=SUM($B$3:B4)-SUM($D$3:D4)
F4	F4	=IF(A4="","",IF(C4="",TODAY()-A4, IF(D4<>B4,C4-A4,"")))

So now , there is no invoice for the 200

how do i then use a date

lets take another example

Book1
	A	B	C	D	E	F	G
1
2	Invoice Date	Amount	Payment Date	Amount	Debit/Credit		Days
3	1/1/22	1000	3/30/22	800	$200.00		88
4			4/30/22	10	$190.00
5			5/2/22	10	$180.00
6			5/4/22	10	$170.00
7			5/6/22	10	$160.00
8	new invoice				$160.00
Sheet1

Cell Formulas
Range	Formula
G3	G3	=IF(A3="","",IF(C3="",TODAY()-A3, IF(D3<>B3,C3-A3,"")))
F4	F4	=IF(A4="","",IF(C4="",TODAY()-A4, IF(D4<>B4,C4-A4,"")))
E3,E8	E3	=IF(A3="","",SUM($B$3:B3)-SUM($D$3:D3))
E4:E7	E4	=SUM($B$3:B4)-SUM($D$3:D4)

do i just look for the latest invoice date and use that -
will invoices dates be sequential

and use a more complicated TEXT - but simply something like

Book1
	A	B	C	D	E	F	G
1
2	Invoice Date	Amount	Payment Date	Amount	Debit/Credit		Days
3	1/1/22	1000	3/30/22	800	$200.00		88
4			4/30/22	10	$190.00		119
5			5/2/22	10	$180.00		121
6			5/4/22	10	$170.00		123
7			5/6/22	10	$160.00		125
8	6/1/22				$160.00
Sheet1

Cell Formulas
Range	Formula
F4	F4	=IF(A4="","",IF(C4="",TODAY()-A4, IF(D4<>B4,C4-A4,"")))
G3:G7	G3	=C3-MAX($A$3,A3)
E3,E8	E3	=IF(A3="","",SUM($B$3:B3)-SUM($D$3:D3))
E4:E7	E4	=SUM($B$3:B4)-SUM($D$3:D4)

 

[totemos]

I cannot upload an spreadsheet so i have attached a photo.

 

[totemos]

To answer to your question next date is or the next payment date or i must propose to the customer that you have also a remaining amount that must be paid due to this date in order to be below the agreed days.