Payment tracking

[Papillonhostel]

Hello,

Sorry for bad English.

I created a payment tracker, which allow me to track every rent from tenants. You can find the sheet here (translate in English). Usually, rents are grouped by tenants, but for the translation I do not have copy them.
When I receive a payment, I write on the record the date of receipt, the means of payment and I check the “Done” field.

To quickly see what rent I need to claim for the month, I am looking for a formula that shows “LATE PAYMENT” in red for every rent that I do not receive when the “Due” date is over.

Exemple : Every rent for September must be paid at least on 5th September. If a tenant do not paid the rent on 6th September, the formula field in “Rent of September” will be shows “LATE PAYMENT” in red.

Can you please tell me what formula I need to write to make this happen ?

Thanks.

[JonathanBowen · Accepted solution]

Hi @Papillonhostel - you can do something like this:

The “Rent Overdue?” field is a formula:

IF(AND({Payment Due} < TODAY(), NOT({Date Payment Received})), '❌ Overdue ❌')

This is saying:

If payment due date is before today AND there’s no payment received, then show as overdue

[Papillonhostel]

Hi JonathanBowen,

Thanks a lot, this is exactly what I was looking for.

I have one last question. Sometimes my tenants have housing assistance from government, so I was thinking about create another checkbox field named “Housing assistance”, so I can track those payments too (most of those payments are always late).

Is it possible to create a formula filed which says something like “if Housing Assistance is not checked after due date, then show as HA Overdue”.

Again, thanks a lot.

[JonathanBowen]

It is very similar. If you had a checkbox for “Housing Assistance Received” then you could have another formula field:

IF(AND({Payment Due} < TODAY(), NOT({Housing Assistance Received})), '❌ HA Overdue ❌')

[Papillonhostel]

JonathanBowen:

IF(AND({Payment Due} < TODAY(), NOT({Housing Assistance Received})), ‘ :x: HA Overdue :x: ’)

Exactly what I was looking for.
Thank you for your help.