Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Payment tracking

Topic Labels: Formulas
Solved
Jump to Solution
2952 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.