Finding date of last payment in excel sheet

[dloskot]

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

 

[dloskot]

JoeMO: Sorry as I tested this more it didn't always give the correct date. I am not sure why I tried to see if there was a pattern in the data but I could see one. Would it matter if I had a wookbook with multiple sheets. I was putting the formula on each sheet separately. I do have several payments then zeros 0 for no payment then payments etc. Your formula wouldn't always find the last payment. Any other thoughts?

 

[dloskot]

Aladin: This formula doesn't work for me. It seems to always come back with 0-Jan-00
Doug

 

[Aladin Akyurek]

Aladin: This formula doesn't work for me. It seems to always come back with 0-Jan-00
Doug

We have dates in column A and payments in column C, right?

 

[dloskot]

Aladin: Sorry this formula doesn't work for me. It comes back with 0-Jan-00 for all cases.
Doug

 

[dloskot]

Yes that is correct. Dates in Column A and payment amount in Column C. Dates are one week apart. Payment might or might not be made each week so you could have three payments, then no payment for 1 or more weeks then a payment. That pattern will happen over the 57 rows in the sheet.

 

[Aladin Akyurek]

Aladin: Sorry this formula doesn't work for me. It comes back with 0-Jan-00 for all cases.
Doug

What do you mean by 'for all cases"? Don't we have just one case? That is, return the date of the last non-zero payment?

The formula of post #2 does just that: It return the date that corresponds to the last non-zero payment. A result of 0-Jan-00 as you mention is only possible when there is a non-zero payment with a corresponding empty date cell.

By the way, we can have a better readable formula if you do the following:

Define...

1. BigNum as referring to:

=9.99999999999999E+307

2. Lrow as referring to:

=MATCH(BigNum,Sheet1!$A:$A,1)

As you can see, this assumes that the payment data is located in Sheet1 (Adjust to suit.).

3. dates as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

4. payments as referring to:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

5. Now you can invoke outside the data area:

=LOOKUP(BigNum,1/ISNUMBER(1/payments),dates)

Format the formula cell as date.

 

[dloskot]

Aladin: I appreciate all your help and you clearly understand these complex formula better than I do. When I say it doesn't work in all cases I mean I have a couple of hundred repayment sheets. The formula that JoeMo provided works correctly on some sheet but not others. When I enter your formula the first time I get an excel message stating "Excel cannot calculate a formula. There is a circular reference in an open workbook, but the reference that caused it can not be listed for you. Try editing the last formula you entered, or removing it with the undo command." I have not tried Defining the elements as you suggested in your last post. I don't know if there is a way or if it is allowed but I could provide you with one of the workbooks that has 10 to 15 repayment sheets in it I need the formula to work on each separate repayment sheet in the workbook. Again thanks for all your help I would really like to figure this of if possible.
Doug

 

[JoeMo]

Aladin: I appreciate all your help and you clearly understand these complex formula better than I do. When I say it doesn't work in all cases I mean I have a couple of hundred repayment sheets. The formula that JoeMo provided works correctly on some sheet but not others. When I enter your formula the first time I get an excel message stating "Excel cannot calculate a formula. There is a circular reference in an open workbook, but the reference that caused it can not be listed for you. Try editing the last formula you entered, or removing it with the undo command." I have not tried Defining the elements as you suggested in your last post. I don't know if there is a way or if it is allowed but I could provide you with one of the workbooks that has 10 to 15 repayment sheets in it I need the formula to work on each separate repayment sheet in the workbook. Again thanks for all your help I would really like to figure this of if possible.
Doug

Can you post some sample data for a case where the formula I posted does not work?

 

[Aladin Akyurek]

Aladin: I appreciate all your help and you clearly understand these complex formula better than I do. When I say it doesn't work in all cases I mean I have a couple of hundred repayment sheets. The formula that JoeMo provided works correctly on some sheet but not others. When I enter your formula the first time I get an excel message stating "Excel cannot calculate a formula. There is a circular reference in an open workbook, but the reference that caused it can not be listed for you. Try editing the last formula you entered, or removing it with the undo command." I have not tried Defining the elements as you suggested in your last post. I don't know if there is a way or if it is allowed but I could provide you with one of the workbooks that has 10 to 15 repayment sheets in it I need the formula to work on each separate repayment sheet in the workbook. Again thanks for all your help I would really like to figure this of if possible.
Doug

I have no clue what you are doing...

If you want to run the formula for each sheet, you need to do so separately for each sheet... Defined range names do not hold for 100 sheets. Just for one sheet.

It's unclear what you are up to. Are you trying to get the last non-zero payment date from each sheet?

 

[dloskot]

Sure I can provide sample data, but how do I do that? My posting permissions say I can't not post attachments?