Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Days Late
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Days Late
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Intrest and Late Fees | Excel Discussion (Misc queries) | |||
calculating time for late hours | Excel Worksheet Functions | |||
Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
30, 60, 90 days late and due within 14 days | Excel Discussion (Misc queries) |