Another one for the masses.
I have 2 columns that calculate days in arrears and rent arrears based on the days in arrears and taking into account a third column of a part payment. A fourth column is a vacate date.
these are the formulas for each column:
Days in arrears: (IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)))-(IF({Part Payment},0,0))-(IF({Part Payment},ROUNDUP({Part Payment}/({Weekly Rent Amount}/7),0)))
Rent arrears:
({Weekly Rent Amount}/7)*IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)) -{Part Payment}
These work fine until you put a vacate date in which stops the days in arrears and arrears amount changing. which is what I want… but…
If a tenant pays money and the paid to date is changed, this is not reflected in the new arrears amount.
so my 2 questions are:
Is there a way to have this recalculate the amount if the paid to date is changed after a vacate date is entered.
I would also like the to be able to enter the vacate date ahead of time and it not affect the result until the status is changed to vacate.
Picture of the tabs for you
