Skip to main content

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.

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

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


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.

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

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.

Reply


Terms & Conditions