Movement Schedule Excel

Question

Hello,

I am trying to calculate rent based on number of days boarders stay. I have incoming and outgoing dates. days 0-5 are $10 per night, days 6-20 are $8 and 21 days plus is $5 per night.

Any ideas on what formulas i can use so the costs for each bracket is auto calculated.

thank you in advance.

Answer 1

You are incredible! Thank you so much.

That has worked perfectly.

Thank you

Have a wonderful day

Answer 2

Hi Simone!

Thank you for the feedback.

In the formula above, I assumed the following: Cell A2 contains the incoming date Cell B2 contains the outgoing date Cell C2 contains the total number of days stayed

That is once any student comes in, column A will record his/her incoming date

Column B will record his/her outgoing date

Column C will record the total number of days the student spent

Column D will display the amount of money to be paid by the student based on the formula above

In cell D, enter this formula: =IF(C2 <= 5, C2 * 10, IF(C2 <= 20,="" 5="" *="" 10="" +="" (c2="" -="" 5)="" *="" 8,="" 5="" *="" 10="" +="" 15="" *="" 8="" +="" (c2="" -="" 20)="" *="" 5))="" however,="" the="" formula="" aboe="" can="" be="" broken="" down="" for="" each="" bracket="" separately="" as="" shown="" below,="" allowing="" you="" to="" see="" the="" rent="" for="" each="" specific="" range="" of="" days="" stayed.="" for="" days="" 0-5="" (rate:="" $10="" per="" night):="MIN(C2," 5)="" *="" 10="" for="" days="" 6-20="" (rate:="" $8="" per="" night):="IF(C2"> 5, MIN(C2, 20) - 5) * 8

For days 21 and above (rate: $5 per night): =IF(C2 > 20, C2 - 20) * 5

I hope this helps!

Thank you

Best Regards, Shakiru</=>

Answer 3

Good Morning Shakiru,

Thank you

I absolutely love your answer and the formula does work for the total however it has highlighted to me that I should have been more clear in my question.

I need to know the break down for each bracket

Any further assistance would be so helpful

Thank you so much

Answer 4

Hi Simone W!

I am Shakiru, an independent advisor and an Excel user like you, and I am glad to be helping you out today.

To calculate the rent based on the number of days boarders stay and apply different rates for different brackets, you can use the following formulas in Excel:

Assuming:

Incoming date is in cell A2 Outgoing date is in cell B2 No of days stayed in C2 =B2 - A2

In cell D, enter this formula: =IF(C2 <= 5, C2 * 10, IF(C2 <= 20, 5 * 10 + (C2 - 5) * 8, 5 * 10 + 15 * 8 + (C2 - 20) * 5))

That is,

=IF(Days<=5, Days*10, IF(Days<=20, (5*10) + ((Days-5)*8), (5*10) + (15*8) + ((Days-20)*5)))

Please let me know how it goes with Microsoft Support and if you need any other assistance, I will be more than happy to help further.

Thank you

Best Regards, Shakiru