• 0

# Solved Excel: Formula Rent\$ = Date

• 0

My knowledge of excel is limited.
I want to set up a simple spreadsheet template for rent received. I have tenants who may chose to pay rent weekly, fortnightly or monthly.
Start with a cell that says their rent is xxx.xx (\$300) pw
Say their rent is \$300 per week.
In column A I enter date rent received.
The period starts on say 1st March 2010 (to be shown as 01/03/2010 european format) and \$300 would pay their rent up to 07/03/2010. So that when I enter 300 in cell B1 it shows up as (paid to) 07/03/2010 in the adjoining cell C1. If they paid 320.50 it would show up as (a credit) of 20.50 in the next adjoining cell D1 and if they paid 280 it would show as -20 in D1.
If they paid 620 on 01/03/2010 it would show rent paid to 14/03/2010 C1 and a 20 credit in D1.
Also in colum E a running total of colum D which would show if they had any accumulated credit or debit.

Share

### 1 Answer

1. Hi,

Just doing this with formulas is rather limiting.
The following will give you some of the features you want, and it works in OpenOffice.

I left row 1 & 2 empty to leave room for some more headings if required.

Rows 3 & 4 are headings
Each Tenant takes up two rows, so that date of payment is on one row and amount paid is on the next row.

To make this work each payment is recorded along with the payment date. I have used columns P to HO for the payment record. For someone paying weekly this would last for 4 years.

Enter the following text in cells. If I have two cells for one text, merge the cells and centre the text, and enter the text once.

Row 3
B3 Name C3 Address D3 Weekly
E3/F3 First Payment
G3/H3 Start of tenancy
I3/J3 Last payment
K3/L3 Balance
M3/N3 Paid until
O3 Payment
Row 4
D4 Rate E4 \$ F4 Date G4 Date H4 Day I4 \$
J4 Date K4 \$ L4 Days M4 Week of N4 Day O4 record

Other rows O5 date O6 \$

Formulas:
Enter these formulas:
H5=IF(D5=””,””,DAY(G5))
I5=IF(D5=””,””,IF(ISNA(HLOOKUP(J5,P5:HO6,2,FALSE)),””,HLOOKUP(J5,P5:HO6,2,FALSE)))
J5=IF(D5=””,””,IF(MAX(P5:HO5)=0,””,MAX(P5:HO5)))
K5=IF(D5=””,””,(SUM(P6:HO6)+E5)-INT((SUM(P6:HO6)+E5)/D5)*D5)
L5=IF(D5=””,””,INT(K5/(D5/7)))
M5=IF(D5=””,””,G5+(INT((SUM(P6:HO6)+E5)/D5)*7))
N5=IF(D5=””,””,M5+L5)

Select cells D5 to O6
Now drag these cells down, as many pairs of rows as you have tenants.

The formulas all test the weekly rate and if the weekly rate is empty, the formulas all return “”

For the first tenant, add data in cells D5 to G5 and enter dates and payment amounts (after the initial payment) starting with first regular payment date in P5 and amount in P6.

I hope that this gives you something to work on.
I haven’t tested this with much data so you need to look at results closely when you first start using it, to be sure it is doing what you expect.

One problem is that when you change weekly rates you will need to start a new pair of rows as the paid until calculation is based on one weekly rate.

Regards

• 0