Counting Days and Applying Charges in Excel

[Cstranack84]

I am looking to create a tool where by it calculates the daily rent cost over a period of days (start date -end date) but the rent amount differs depending on the number of days. For example, 1st 7 - 1/5/23 -7/5/23 £19 per day Next 7 - 8/5/23 -14/23 £22 per day and so on. Is there a way to count the number of days and apply the correct charges for that period? Any advice would be greatly appreciated!

[#2]

@Cstranack84 The "and so on" bit makes it quite vague. Perhaps the attached file contains a solution that would work for you. If not, please come back here and clarify your requirements.

[#3]

Hi @Riny_van_Eekelen, so the actual requirements are the first 7 days at a cost of £19 per day, followed by 7 days at £22 per day from the 15th day onwards its becomes £30 per day.
So for example if the chargeable period ran from 01/05/23 -31/05/23 it would be charged as follows
01/05/23 - 07/05/23 £19 per day
08/05/23 - 14/05/23 £22 per day
15/05/23 - 31/05/23 £30 per day
so 31 days at a total cost of £510.
Is this possible to calculate by entering the start and finish date and it would count the days in between at the applicable rates?

[#4 · Accepted solution]

@Cstranack84 Not sure how you did your math but I arrive at a total fee of £797 for 31 days. The £510 you mention would be only for the last 17 days at a rate of £30.

Have attached a revised file.

[#5]

@Riny_van_Eekelen , yes my math was wrong, but thank you so much this has worked perfectly really helpful appreciate it!