Weekly Rent Payment sheet - List due dates and tenant based on tenant and duration

[DazDaw]

Good afternoon, I am creating a sheet for each room that is currently being rented. (I have attached one worksheet as an example).

In the example the new tenant John Doe started a tenancy on the 01/01/23,(A7), rent is due every seven days from this start date.

1) I would like to list the payments dates, (start date (cell E7) +7 days from cell A28 and also tenant name (John Doe in this case) in cell B28, but only until his tenancy ends on 04/05/23 as stated by (cell F7).
2) I would then like the dates in column A to resume when Britney begins her tenancy, from her tenancy start date in (cell E8) and until her end (in cell F8)
3) This repeats for Trevor Jones onwards......

Many thanks.

[Pete_UK]

The start and end dates for Britney are the same in the upper table (16/09/2023), although you show her tenancy as lasting until 29/10/2023 in the lower table, which is different than her planned tenancy in cell H8.

Does the first payment fall on the first day of the tenancy (i.e. payment in advance) or at the end of the first week (in arrears)?

Where the tenancy has not yet finished, i.e. for Trevor Jones, would you like the payments to continue up to TODAY() ?

Pete

[DazDaw]

Hi Pete,

The Britney start and end date should be different, they were arbitrary dates and I must have duplicated, apologies.
The first payment for each client should always fall 7 days after that specific tenancy starts.
Yes, if the tenancy has not ended it would be useful to run up to TODAY().

Thanks

[Pete_UK]

Okay, the only other question relates to the length of the tenancy - what do you want to show if the tenancy is not an exact number of weeks?

Suppose a tenancy starts on a Saturday, but finishes on a Wednesday. All of the dates for that tenant will be for Saturdays starting one week after the start, so for the final week it will also show a Saturday, even if the tenant left on the Wednesday of that week. Do you still want to show it as the Saturday?

I have a solution for you, but I'll wait until your reply before posting it, so I can include your preference.

Pete

[DazDaw]

Okay, the only other question relates to the length of the tenancy - what do you want to show if the tenancy is not an exact number of weeks?

Suppose a tenancy starts on a Saturday, but finishes on a Wednesday. All of the dates for that tenant will be for Saturdays starting one week after the start, so for the final week it will also show a Saturday, even if the tenant left on the Wednesday of that week. Do you still want to show it as the Saturday?

I have a solution for you, but I'll wait until your reply before posting it, so I can include your preference.

Pete

Yes, that's fine. The tenancy duration will always be in week multiple, but these may start, (and therefore finish), on any day of a week. That would mean, as you say if it started on a Saturday that specific tenants agreement would finish on a Saturday. Someone else may start on a Monday and therefore finish on a Monday.

[Pete_UK]

Okay, well I think the attached file does what you have asked for.

I've set up a table in cells O5:P15 which calculates the duration in weeks and then just totals these cumulatively - there is no need for you to do anything with this.

I've used this formula in A28:

=IF(ROWS($1:1)>MAX($P$7:$P$15),"",INDEX($E$7:$E$15,MATCH(ROWS($1:1)-1,$P$6:$P$15))+7*(ROWS($1:1)-INDEX($P$6:$P$15,MATCH(ROWS($1:1)-1,$P$6:$P$15))))

to get the dates at 7-day intervals from the start date for each tenant's duration, and this formula in B28 gets the name:

=IF(A28="","",INDEX($D$7:$D$15,MATCH(ROWS($1:1)-1,$P$6:$P$15)))

I've copied these two formulae down to row 100 to cope with more tenants being added to the upper table, though you may need to copy them down further as you could potentially have 52 entries for each year if you have 100% occupancy.

I've not changed the data you had set up, so you can see your first tenant for 18 weeks, then Britney for just one week, and then your latest tenant goes just beyond today (he started on a Tuesday, so it goes up to the next Tuesday after today).

Hope this helps.

Pete

[DazDaw]

Okay, well I think the attached file does what you have asked for.

I've set up a table in cells O5:P15 which calculates the duration in weeks and then just totals these cumulatively - there is no need for you to do anything with this.

I've used this formula in A28:

=IF(ROWS($1:1)>MAX($P$7:$P$15),"",INDEX($E$7:$E$15,MATCH(ROWS($1:1)-1,$P$6:$P$15))+7*(ROWS($1:1)-INDEX($P$6:$P$15,MATCH(ROWS($1:1)-1,$P$6:$P$15))))

to get the dates at 7-day intervals from the start date for each tenant's duration, and this formula in B28 gets the name:

=IF(A28="","",INDEX($D$7:$D$15,MATCH(ROWS($1:1)-1,$P$6:$P$15)))

I've copied these two formulae down to row 100 to cope with more tenants being added to the upper table, though you may need to copy them down further as you could potentially have 52 entries for each year if you have 100% occupancy.

I've not changed the data you had set up, so you can see your first tenant for 18 weeks, then Britney for just one week, and then your latest tenant goes just beyond today (he started on a Tuesday, so it goes up to the next Tuesday after today).

Hope this helps.

Pete

That is brilliant! I'd have been here for several months to get to that result! Thank you so much. Just one question. The weeks in O5:O15 are calculating one extra week. Should I remove the +1 in the formula to ensure these cells return the actual weeks occupied by each tenant? Thank you again...

[Pete_UK]

Glad to help, and thanks for the rep.

Yes, I put the +1 in the formula which calculates the weeks in order to account for a part week at the end of the tenancy. I didn't know at that time that your start and end date would always be for the same day of the week, so the formula in O7 could now become:

=IF(E7="","",INT((IF(F7="",TODAY(),F7)-E7)/7))

and then you can copy this down. You will need to amend the end date for Britney in your sample data to be at least 1 week after the start date. Note also that if the tenancy is still ongoing, then the last date shown will be the start day of the week before today - in your sample data Trevor Jones started on a Tuesday, so his dates finish with the Tuesday before today (6th Feb 2023).

Hope this helps.

Pete