Hi all,
I have trouble implementing a formula to take into account to apportion the proper rental to be charged when the rate changes after a certain date.
Currently, with my formula I have no problem dealing with normal cases where I charge the full rental rate.
My problem lies in the edge case where I must prorate the rental charge when it is in a month that has 2 rates (i.e the old rate * the number of days before rate changed + new rate * the number of days of the new rate).
So Basically what I have now is:
- If the date is less or equals to the start date of the Tenant, I do not charge anything (i.e 0),
- Else I lookup by referring the month to a table to identify the rental for that month.
Some ideas that I had in mind but unsure of feasibility and implementation:
- somehow a way to check if the month in contention is within an overlap range of the previous billing date and the new rate date (and the new rate date to the current billing date) and prorate accordingly by taking into account the number of days that should use the old rate and the number of days which should be charging the new rate.
The file is attached along.
Thanks.
I have trouble implementing a formula to take into account to apportion the proper rental to be charged when the rate changes after a certain date.
Currently, with my formula I have no problem dealing with normal cases where I charge the full rental rate.
My problem lies in the edge case where I must prorate the rental charge when it is in a month that has 2 rates (i.e the old rate * the number of days before rate changed + new rate * the number of days of the new rate).
So Basically what I have now is:
- If the date is less or equals to the start date of the Tenant, I do not charge anything (i.e 0),
- Else I lookup by referring the month to a table to identify the rental for that month.
Some ideas that I had in mind but unsure of feasibility and implementation:
- somehow a way to check if the month in contention is within an overlap range of the previous billing date and the new rate date (and the new rate date to the current billing date) and prorate accordingly by taking into account the number of days that should use the old rate and the number of days which should be charging the new rate.
The file is attached along.
Thanks.