MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date function or Formula


Posted by John on January 05, 2001 3:08 PM

What function or formula would I used if I wanted to add for example $25 at the 5th of
the month then $27 on the 6th and $29 on the 7th and so on? Any help is
greatly appreciated and thank you in advance.

John


Posted by Mark W. on January 06, 2001 3:43 PM

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

Posted by Mark W. on January 06, 2001 3:58 PM

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))

Posted by John on January 07, 2001 8:06 AM


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.

Posted by Mark W. on January 07, 2001 9:34 AM

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)].

Posted by John on January 07, 2001 10:11 AM

Thanks a million Mark.


John

Posted by John on January 08, 2001 6:22 AM

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

Posted by Mark W. on January 08, 2001 9:14 AM

Use:

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

Posted by Mark W. on January 08, 2001 9:51 AM

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)))

Posted by John on January 08, 2001 3:20 PM

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

Posted by Mark W. on January 09, 2001 7:53 AM

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?

Posted by John on January 09, 2001 12:09 PM

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?)

Posted by Mark W. on January 09, 2001 2:15 PM

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.

Posted by John on January 09, 2001 3:39 PM

Thanks for all your help on this Mark.


John