Date function or Formula

=INDEX(TREND({25;27},{5;6},ROW(1:31)),DAY(TODAY()))

Oh, yeah... in order to preserve the integrity of this formula you'd better make the row reference absolute! So, the formula really should be: =INDEX(TREND({25;27},{5;6},ROW($1:$31)),DAY(D2))

Thank you very much for your input. One more question. I thought I could take it from here but I am still having a problem. Here is what I'm trying to achieve. I own 60 properties and trying to add late charges starting on the 5th of the month with a charge of $25 on the 5th and then $2.00 a day afterwards. So on the 5th it would be $25 and 6th $27 etc.....
Thank you very much for your help, you have taken a load off my shoulder. I have got the entire program wrote except this column.

Calculate your late charge using:

=INDEX(TREND({25;27},{5;6},ROW($5:$31)),TODAY()-(TEXT(TODAY(),"m/yy")-1))

If your tenants don't pay before the end of the month in which the rent is due you'll need to modify this formula by replacing the 2nd TODAY() function with a cell reference to the date that the rent is due. You'll also need to expand the late charge array, [e.g., ROW($5:$360)].

Thanks a million Mark.

John

Mark, I need to create a function in the below that will not calculate if I enter a Value in a certain cell. Say if they pay on the 4th of the month and enter that into b4 cell. I need the formula below not to calculate any value. I hate to keep asking these questions, but I am lost with this type of function.

Thanks

Use:

=IF(B4,INDEX(TREND({25;27},{5;6},ROW($5:$31)),TODAY()-(TEXT(TODAY(),"m/yy")-1)),)

Oops! In my haste I got it bassackwards... Use:

=IF(B4,,INDEX(TREND({25;27},{5;6},ROW($5:$31)),TODAY()-(TEXT(TODAY(),"m/yy")-1)))

Mark I need to name my first born after you.. I hate to keep asking you questions, but it seems you keep openning new doors. We just about have it I think. When I add the payment to b4, the cell with the formula you gave me for late charges calculates to 0. Can we keep the amount of the late charge when we enter payment? I would like the late charge to keep its' value up to the point I enter the payment into b4. If you are tired of the questions, don't hesitate to say.

Thanks,

John

John, either I don't understand your question or
it is a contradiction. In an earlier posting you
requested "...a function... that will not
calculate if I enter a Value in a certain cell."
Now you're asking, "Can we keep the amount of the
late charge when we enter payment?"

What am I missing here?

Mark, is it possible to keep the value of the late charge in the formulated cell? Before the late charge would keep adding whether I enter a payment or not. I would like for it to stop calculating at the time I enter a payment. With the formula now the late charge goes to 0 after the payment is entered which does not show how much the late charge was for that month. Is this any clearer? (say on the 8th tenant has a late charge of $31 and makes a payment of $531 on the 8th. Now the cell reads 0 after the payment is entered. Can we keep the $31 late charge in the cell and not add a additonal amount to it?)

John, not knowing how your worksheet is organized
I recommend that you readup on the Help topic for
the IF worksheet function, and tailor it to suit
your needs.

Thanks for all your help on this Mark.

John