Stoping a formula increasing the daily accrued costs when an end date is added

Hi All,

I have been hanging around the forum a lot of late and have found it incredibly helpful. Thanks for that.

I am totally stuck on something that I hope somebody can help with.

I have attached the spreadsheet to illustrate my problem.

I have a date hired and a date off-hired column, along with daily hire costs and weekly hire costs.

I have managed to enter a formula that calculates the cost accrued since the date of hire, and also conditional formatting to highlight when a hired item is over the expected number of hire days.

What I am struggling with is two things.

1) Getting the current total to switch to 'weekly rate' after 5 days
2) Getting the current total to stop accruing costs when data is entered into the 'off-hired' column.

I hope somebody here can help.

ThanksBuyers-hire.xlsx

Hi There

Thanks for that. That seems to go some way to making it work, but not quite.
The current total seems to get stuck with 5 days hire on only no matter how many days the hire period is. It doesn't take into account the weekly hire costs.

That is great thanks very much. Really helpful.

It isn't quite as I need but really close and it puts me much farther on that I was.

Where I am trying to get to is:

After the 5 days of hire has elapsed, the weekly rate will then replace the 5 days cost that has been accrued. At the moment as you said after 5 days has elapsed, the weekly rate is charged on a daily basis. I probably didn't explain that very well.

So if a hire charge is £100 per day(K2) and £450 per week (L2), after 4 days the figure in M2 will be £400. When the next day arrives and the item is still on hire, the cost will then revert to £450 until 7 days time when it will increase to £900 and so on.

I really appreciate your help as everything else is spot on.

Hmm not quite right, but thanks. If you manage to text it out then let me know. I will tinker around with it. Thanks again