Finding date of last payment in excel sheet

dloskot

New Member
Joined
Oct 18, 2015
Messages
43
I am on the board of charity that does micro loans in Africa and we used excel sheet to keep track of loan repayments. I would like to find the date of the last payment. Column A has the date and Column C has the payment. All the payment cells are pre filled with zeros. I think a CSE formula could be used but I have not been able to find the right formula to do this. I am hoping some here can help me write the correct formula. Below is an example of that a repayment sheet would look like. In this example I would like to have a formula that would return the date of 23-Dec-14 as that was the date of the last payment as shown in Column C.

A B C D E F G H
1 Payment Date Weekly Amt Payment Past Due Paid to Date Loan Balance Savings Saving Bal
2 2-Dec-14 19,300 38,600 0 38,600 461,400 0 0
3 9-Dec-14 19,300 10,000 9,300 48,600 451,400 0 0
4 16-Dec-14 19,300 0 28,600 48,600 451,400 0 0
5 23-Dec-14 19,300 15,000 47,900 48,600 451,400 0 0
6 30-Dec-14 19,300 0 67,200 48,600 451,400 0 0

I hope someone can help me with this.

Doug
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try...
Rich (BB code):

=LOOKUP(9.99999999999999E+307,
   1/ISNUMBER(1/C2:INDEX(C:C,MATCH(9.99999999999999E+307,C:C,1))),
   A2:INDEX(A:A,MATCH(9.99999999999999E+307,C:C,1)))
 
Last edited:
Upvote 0
Sorry I am new to posting on this forum and I messed up the example so hopefully this me easier to read.

A B C D E F G H
1 Payment Date Weekly Amt Payment Past Due Paid to Date Loan Balance Savings Savings Bal
2 2-Dec-14 19,300 38,600 0 38,600 461,400 0 0
3 9-Dec-14 19,300 10,000 9,300 48,600 451,400 0 0
4 16-Dec-14 19,300 0 28,600 48,600 451,400 0 0
5 23-Dec-14 19,300 19,000 47,900 48,600 451,400 0 0
6 30-Dec-14 19,300 0 67,200 48,600 451,400 0 0
 
Upvote 0
This didn't quite work, it gave me the last cell with a 0 in it which is the last row in the sheet. I need it to fine the last cell that is non zero. If it helps there are 53 rows running from 4 to 56.
 
Upvote 0
This didn't quite work, it gave me the last cell with a 0 in it which is the last row in the sheet. I need it to fine the last cell that is non zero. If it helps there are 53 rows running from 4 to 56.

I have edited the formula. So try it again. By the way, it's a dynamic formula, that is, if you add new records, it will update automatically.
 
Upvote 0
Does this work for you?
Excel Workbook
ABC
1PaymentDateWeekly
22-Dec-1419,30038,600
39-Dec-1419,30010,000
416-Dec-1419,3000
523-Dec-1419,30015,000
630-Dec-1419,3000
7
8Last Payment23-Dec-14
Sheet3
 
Upvote 0
JoMo: Yes it work perfectly once I adjusted it for the actual size of the table. Thanks you very much
 
Upvote 0
I'd opt for a dynamic approach...

~
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
date last payment
2​
2-Dec-14​
19,300​
38,600​
23-Dec-14​
3​
9-Dec-14​
19,300​
10,000​
4​
16-Dec-14​
19,300​
0​
5​
23-Dec-14​
19,300​
15,000​
6​
30-Dec-14​
19,300​
0​
7​

J2:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/C2:INDEX(C:C,MATCH(9.99999999999999E+307,C:C,1))),A2:INDEX(A:A,MATCH(9.99999999999999E+307,C:C,1)))
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top