Working out a price when different rates apply?

**jason7579** · 02-04-2010, 01:01 PM

I am trying to sort out our venues bookings at work and get them as streamlined as possible. One of my ideas is to have a simple form that we can fill in when people call and ask for a quote to use our Theatre or book a room before loading all the data into our venues program and creating customers etc.

My problem is I'm not sure what kind of formulas I'll need.

e.g. if someone wants to book from 15:00 - 23:00 on a Thursday I need the preadsheet to work out that all hours before 17:30 are charged at £50 per hour and all after 17:30 are charged at £100 per hour. Then on top of this if the booking carries on past 22:00 there is a set fee of £300 to be added to the total price.

Can anyone point me in the right direction? I thought it wouldn't be that hard to work out but I'm not sure what I need?

Thanks

**NBVC** · 02-04-2010, 01:31 PM

I am sure there is a shorter way.. but I think this works:

Please Login or Register  to view this content.

it should work if the meeting carries over to the next day too.

**Richard Buttrey** · 02-04-2010, 01:35 PM

Hi

Perhaps the attached example will give you an idea and starter for 10.
The reference in the formula to .729167 refers to the excel time value for 17:30.

HTH