Forum Discussion
Simple property management system
I am developing a simple property management system that would calculate the remaining rent days from the last date of payment. i have design date column from K5:AT5 with headers as 31/03/2024, 30,0...
You can solve this by finding the last non-empty payment cell in the row, then referencing the date header above it.
Assuming:
Dates are in row 5 (K5:AT5)
Payments for a tenant are in row 6 (K6:AT6)
You can use this formula:
=TODAY()-LOOKUP(2,1/(K6:AT6<>""),K5:AT5)
How it works:
K6:AT6<>"" checks for non-empty payment cells
LOOKUP(2,1/(...),K5:AT5) returns the last corresponding date from the header row
TODAY() minus that date gives you the number of days since last payment
If you want remaining days until next due date, you can adjust logic depending on your billing cycle.