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,04,2024 ..... 31/01/2026.
the Tanents payment details are just below each date reference i.e. first client K6:AT6, ..... Last client K30:AT30.
Now i need a formula that will check the latest payment to the right and reference it to date directly above it and substrate today's day from that date and return of the number of remaining days or due date.
Thank
3 Replies
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.In row 6:
=XLOOKUP(1000000000, $K6:$AT6, $K$5:$AT$5, "", -1, -1)-TODAY()
Format the cell with the formula as General, then fill down.
- Thanks Hans,
that formula fail to work with me, it only bring #Values.. so i used this "=TODAY()-INDEX(K:AV,5,(COUNT(L6:AV6)+1))" and it worked.
