I'm having trouble figuring out a formula for calculating days late on a
payment when there is multiple payments.

For example:
Payment #1 is $5000 and due on Aug 01.
Payment #2 is $5000 and due on Sept 01.
Payment #3 is $5000 and due on Oct 01.
Payment #4 is $15000 and due on Oct 15.

I get several payments from Jul 15th to Nov 15 of many different values.

For example

Received Jul 15 $2000
Received Jul 25 $2000
Received Aug 15 $1000
Received Aug 30 $5000
Received Oct 05 $2000
Received Oct 08 $2000
Received Oct 16 $2000
Received Nov 15 $12000

Working this out manually,

payment #1 is 14 days late
payment #2 is 0 days late (ignore early payments)
payment #3 is 9 days late
payment #4 is 31 days late

All of the received dates are in column N and received amount in column S.

How can I tally the sums in N until is reaches or exceeds the first payment
amount and calculate the date difference between due and that date? I then
need to continue the same, but now looking for a total of both payment one
and two.

Suggestions?

I had a manual error, payment #3 is 15 days late.

"Jim" wrote:

I'm having trouble figuring out a formula for calculating days late on a
payment when there is multiple payments.

For example:
Payment #1 is $5000 and due on Aug 01.
Payment #2 is $5000 and due on Sept 01.
Payment #3 is $5000 and due on Oct 01.
Payment #4 is $15000 and due on Oct 15.

I get several payments from Jul 15th to Nov 15 of many different values.

For example

Received Jul 15 $2000
Received Jul 25 $2000
Received Aug 15 $1000
Received Aug 30 $5000
Received Oct 05 $2000
Received Oct 08 $2000
Received Oct 16 $2000
Received Nov 15 $12000

Working this out manually,

payment #1 is 14 days late
payment #2 is 0 days late (ignore early payments)
payment #3 is 9 days late
payment #4 is 31 days late

All of the received dates are in column N and received amount in column S.

How can I tally the sums in N until is reaches or exceeds the first payment
amount and calculate the date difference between due and that date? I then
need to continue the same, but now looking for a total of both payment one
and two.

Suggestions?