# Calculating using times and different rates for bookings

• Hi all,

I am trying to create a booking calculation sheet for facilities. I want it to create something like a quote for customers.
So when booking facilities, the hourly rate changes after 6pm. What I want to do is to put in a start time and a finish time and it calculate how many hours are before 6pm and charge them at \$12 per hour, as well as how many hours after 6pm and charge them \$18 per hour.
I have tried a few things but they don't seem to work. I'm struggling with the logic of it really. This is mainly because the start time may or may not be before 6pm, as too the finish time.

Cheers,
AmLime

• Re: Calculating using times and different rates for bookings

Thank you. But when putting in a start time of say 7pm and an end time of 9pm, it calculates it wrong. This is the annoying logic with it, because the start and finish times could both be before or after, or one either side of 6pm. Tough one. I have been playing around with a few formulae that are in different cells with an IF function relating to the previous, if that makes sense. I have attached what I have come up with, but it seems like the long way round!

## Files

• Re: Calculating using times and different rates for bookings

with the sheet i uploaded, i entered a start time of "6:00 PM" and end time of "8:00 PM" and got \$36 as a quote. it has to do with the format of the time.

• Re: Calculating using times and different rates for bookings

AmLine,
You are right,
6PM to 8PM works, but 7PM to 9PM does not work

• Re: Calculating using times and different rates for bookings

yep, just tried 7pm to 9pm gave \$42 not \$36.... it was only checking the end time. made a correction to check the start time

try again

[ATTACH=CONFIG]56911[/ATTACH]

## Files

• Re: Calculating using times and different rates for bookings

beautiful, thanks olivetwist!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!