Formula to check the time between and give pricing

[aliaslamy2k]

Dear Experts,

I am looking for a formula in excel to check the time and give and give the pricing accordingly.
Example : If a tennis court is booked between 15:00 to 16:00 the price is 70 for 1 hour and same court is booked between another timings the price should change accordingly.

I have attached the excel sheet for reference.

Thank you so much

 

[Snakehips]

Given your apparent data set, maybe like.....

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3	Tennis
4	Booking Time		1hr	2hr	4+ Hrs			Name	From	To	Hours	Price
5	15:00	16:00	70					ABC	15:00	16:00	1.00	70
6	18:00	22:00	90	160	300			DEF	18:00	20:00	2.00	160
7	22:00	23:59	70	120				GHI	22:00	23:00	1.00	70
8	Tournament		80	160				ABC	23:00	23:59	1.00	70
9								DEF	22:00	23:59	2.00	120
10								GHI	18:00	22:00	4.00	300
11								Tournament	14:00	16:00	2.00	160
12								Tournament	18:00	19:00	1.00	80
13
Sheet1

Cell Formulas
Range	Formula
L5:L13	L5	=IF(I5="","",ROUNDUP(24*(K5-J5),0))
M5:M13	M5	=IF(I5="","",INDEX($D$5:$F$8,IF(I5="Tournament",4,IF(K5<=$C$5,1,IF(K5<=$C$6,2,IF(K5<=$C$7,3,4)))),MIN(3,L5)))

Hope that helps.

 

[aliaslamy2k]

Hi Snakehips,

Thank you so much, It is working very well ! ?

Thanks a million

AB

 

[aliaslamy2k]

Hi Snakehips,

Can you please help me with the attached problem. This time i need to get the results based on criteria.
Example : Football : 1/3 Pitch, one hour charge is 200, one and half hour charge is 300 and two hour charge is 400. Same with 1/2 Pitch and Full Pitch

Example 2: Same as above for Multipurpose hall.

Thank you so much for your wonderful solution.

Kind regards,
AB

 

[Snakehips]

AB, try this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
14
15	Football
16
17	Booking	1Hr	1Hr 30Min	2Hrs						Booking	From	To	Hours	Price
18	1/3Pitch	200	300	400						1/3Pitch	15:00	16:00	1	200
19	1/2Pitch	300	450	600						1/2Pitch	18:00	20:00	2	600
20	Full Pitch	600	800	1000						Full Pitch	22:00	23:00	1	600
21										1/2Pitch	10:00	11:30	1.5	450
22										Full Pitch	22:00	23:59	2	1000
23
24
25	Mult Hall
26
27	Booking	1Hr	2Hr							Booking	From	To	Hours	Price
28	Batmenton	70	130							Batmenton	15:00	16:00	1	70
29	Volleyball	250	450							Volleyball	18:00	20:00	2	450
30	Basketball	250	450							Basketball	22:00	23:00	1	250
31	Footsall	250	450							Footsall	10:00	11:00	1	250
32	Outdoor basketball	120	220							Outdoor basketball	14:00	16:00	2	220
33										Batmenton	22:00	23:59	2	130
34
Sheet2

Cell Formulas
Range	Formula
M28:M33,M18:M22	M18	=IF(K18="","",MROUND(24*(L18-K18),0.5))
N18:N22	N18	=INDEX($A$18:$D$20,MATCH(J18,$A$18:$A$20,0),LOOKUP(M18,{1,1.5,2},{1,2,3})+1)
N28:N33	N28	=INDEX($A$28:$C$32,MATCH(J28,$A$28:$A$32,0),M28+1)

Hope that helps.