Counting days since last payment

Hey folks,

Forum noob looking for help. I am trying to come up with a formula that will count the number of days since the last payment on a spreadsheet i'm working on. First column is my client, next is "Days without repayment," followed by a continually growing column for each business (bank) day (i.e., tomorrow, a new rightmost column will be created - "1/31/14"). I'd like that second column to return the number of days since a payment came in. (If payment received today, then 0; if paid yesterday, than 1 and so on), regardless of when the payments started or any payment gaps. The issue i am running into is that the data to be entered each day may be blank or entered as $0. But, essentially, I am looking to count the number of blank and/or $0 cells since a payment was received (not really a function of date since weekends will throw off the results).

Any help would be greatly appreciated.

Client Days Since Last Payment 1/23/14 1/24/14 1/27/14 1/28/14 1/29/14 1/30/14…
a 0 $12.00 $44.00 $392.72 $45.00 $392.72
b 1 $123.00 $123.00 $33.00 $7.00
c 2 $22.00 $0.00 $22.00
d 3 $4.00 $4.00 $-
e.... 4 $555.00 0 $- $- $-

Days without payment.xlsx

Thanks for the insight and help, FDibbins.

I've attached a mock up of what I am looking to do. Column B results have been manually entered but this is what I would like a formula for. Just count the number of $0 and/or blank cells since the last payment day to arrive at the numbers in Col B.

Any help would be greatly appreciated! Thanks in advance, Excel Wizards.

First off, thank you for taking the time to look at this, Tony.

I apologize for any confusion. Made a mistake. when i posted this. B6 should actually return "2" and B7 should return "3" since there are 2 and 3 blank cells, respectively.

Sorry about that. <wiping egg off face>

Sorry for not replying sooner. Thanks a lot for taking a further look at this, Tony. Unfortunately, though, your formula doesn't exactly work since everyday, a new column will be added. Thus, the following day, H2 would have 1/30/14, the day after would have I2 = 1/31/14...

Any ideas? Really struggling with this one.

Interesting formula, though. What exactly is the follow part:

COLUMNS(C3:G3)-LOOKUP(2,1/(C3:G3),COLUMN(C3:G3)-COLUMN(C3)+1)

Not very familiar with LOOKUP function and can't make heads or tails of it. Still trying to hone my excel chops!

That seems to work. Thank you, Tony. Much appreciated. And thanks for the explanation as well.