Aug 12, 2020 02:40 AM
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.
Solved! Go to Solution.
Aug 13, 2020 04:29 AM
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
Aug 13, 2020 04:29 AM
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
Aug 14, 2020 09:46 AM
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.
Aug 15, 2020 12:55 AM
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 ❌')
Aug 17, 2020 07:51 AM
Exactly what I was looking for.
Thank you for your help.