Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Payment tracking

Topic Labels: Formulas
Solved
964 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hello,

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.

1 Solution

Accepted Solutions
13 - Mars

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

4 Replies 4
13 - Mars

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

5 - Automation Enthusiast

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.

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 ❌')`

5 - Automation Enthusiast

Exactly what I was looking for.