Help

Payment tracking

Topic Labels: Formulas
Solved
Jump to Solution
1848 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Papillonhostel
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Capture d’écran 2020-08-12 à 11.33.47

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
JonathanBowen
13 - Mars
13 - Mars

Hi @Papillonhostel - you can do something like this:

Screenshot 2020-08-13 at 12.27.52

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

See Solution in Thread

4 Replies 4
JonathanBowen
13 - Mars
13 - Mars

Hi @Papillonhostel - you can do something like this:

Screenshot 2020-08-13 at 12.27.52

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

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

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