Rental fees

[Guest]

Hi folks - I am somewhat saavy, but formulas are rather new to me. I
supervise a homeless shelter which charges $7/night. I am trying to
"modernize" it with a spreadsheet for fees.

I need a way to calculate based on "today's date" minus "entry date" (which
I have done with a successful calculation.)

I also have a column in the formula which adds payments made against the
total due (based on the current date and the entry date above.)

Question: How can I do this showing the date of each payment made (as
opposed to just manually adding the dollar amount of each payment to a
running total.)

Example: If client A pays $7.00 on Monday and $21.00 on Tuesday (paying
ahead), how can I "date" each of these payments?

Is this something achievable in Excel?

Thx in advance to all who reply. Also if someone has a better way to do
this type of function(s), please: I'm all ears.

Regards,
Marshall

 

[Guest]

=(today()-A1)*7

would give you the fee owed if A1 holds the date of entry

You can divide a payment by 7 to get the number of days paid.

=B1/7 where B1 holds the amount paid.

You could set up formulas to spread out the payments against dates, but it
would require a bit more knowledge of how you are laying out your sheet.

An example formula which may have application here or not.
if B1 held the amount paid (21), then in B2
=IF(B$1>=(ROW()-1)*7,7,0)
and drag fill down to B6. . This would put 7 in B2, B3, B4 and zero in B5,
B6.

 

[Guest]

To keep your total of amounts paid from getting messed up, you'll need to do
this with a helper column. You can start these formulas in the appropriate
rows anywhere after the first entry of date/amount paid. Let's say your
first entries are in row 2, and you have Column A showing the date "paid up
through" and column B showing the amount paid on a given date. Then we would
use column C (or any other unused column) for our helper column.
In A3 put this formula:
=IF(C3>0,A2+1,"")
That is going to add 1 day to the date in the cell above it if there is a
value greater than zero in our helper column.
Over in C3, put this formula:
=IF(B3>7,B3,IF(C2>7,C2-7,0))
That formula first looks to see if a payment has been entered in column B,
and if one has been entered there it just copies it. But if no payment has
been made on this day, it looks at the cell just above it to see if there is
a value greater than one-day's payment and if that's true, it subtracts 7
from it and shows the remaining 'credit'. The cells below it will do the
same until it falls below $7 - and at the same time, the formula in column A
is checking it just as if it were a paid amount in column B.

If you extend those formula far enough down the sheet, then they'll just
keep on working, and it won't matter if you overwrite the formula in column A
with a date when someone comes in and just pays 1 day's fee.