Parking Fees

Hi,

I need help in calculating peak and off-peak rates.
The parking may involve parking fees outside of the peak like it becomes night rate or weekend rate.

Not sure how to go about it.

Thanks in advance.

Thanks but I forgot to put a sample of mixed times. After 5pm and following day exit and it's a weekend it becomes off peak.

It is a little tricky when it comes to overlapping duration.
I can restrict calculation for weekday AND from 7:00 thu 17:00 only, with this formula:

Formula:

Please Login or Register  to view this content.

Next step, we'll have to play with those times left before and after the peak rate period.
I'll get back to this as soon as I can, and hopefully this will help you get started.

Appreciate your help.

Hello, spiralcookie.

To the formula in post #4, I added a second IF(AND and now it looks like this:

Formula:

Please Login or Register  to view this content.

As I said before, the first IF(AND will restrict count to weekdays and from 7:00 thru 17:00.
The second IF(AND will verify entry time after 17:00 and if entry day is different from exit day and, if True, it will subtract entry time from 17:00 and will return the equivalent amount for that difference only.

I also elaborated the off peak formula so it will on weekdays verify entry after 17:00 OR exit not on the same day.

Formula:

Please Login or Register  to view this content.

For the sake of simplifying those formulas, I took the liberty of reorganizing the hours table in your Rates tab.
Also, I added start and end time of night period, so you don't have to hard code them in the formula.

Please run some test and let us know how it goes.

Thank you!

I tried entering before and on 7am, no cost for both rate is appearing.
15/10/2021 5:00 15/10/2021 10:00
15/10/2021 7:00 16/10/2021 10:00

I added a new IF(AND to those formulas to contemplate entry before 7:00.

As for the fault in entry time at 7am, it is due to a decimal difference that for some reason the Excel finds, starting on the twelfth decimal between these two values:
0.291666666664241 = MOD(day & 7:00,1)
0.291666666666667 = 7/24 = 7:00:00
So, to get around that issue, I now entered 0.291666666664241 in cell C14 in your Rates tab and formatted as time.
I did the same in cell D14, just in case.

Looks good, thanks for your help on this

You're welcome. Glad to help.
Thanks for the feedback and for the reputation added.
Have a blessed day.

Just for the heck of it, here's another option for the Peak Rate - I think this gives the correct answer:

=IFERROR(XLOOKUP(IF([@Day]="Weekend",0,MAX((MIN(EDATE(A2,0)+17/24,B2)-MAX(EDATE(A2,0)+7/24,A2))*24,0)),mins,club,,-1),0)

The change that you need to make is to change cell A2 on the "Rates" table to .01

Just one more, when I tried to enter this times 26/11/2021 16:00 - 26/11/2021 18:00 no rates happen. TIA

I assume you're referring to the formula from Estevaoba. Using my formula I get $7.50

Hi Greg, Yes for Este, when I tried your formula it shows an amount on weekend and night rate which should be zero.

The formula I gave was only for Peak rate (as stated in Post #12)

Hello, spiralcookie.
I have added an OR(AND to the second IF(AND so as to contemplate this scenario that was missing.
Please give it a try.

Formula:

Please Login or Register  to view this content.