Formula required

[shaji]

Hi,
I have issue bills to parties in which the rate to be applied depends on
certain conditins. Rate to be applied is given below:

Rate Time Band Remark
1500 07:00-11:59 Weekdays
1100 12:00-17:59 Weekdays
2500 18:00-23:59 All Days
1400 07:00-17:59 Weekends

if the time on air is between 07:00-11:59 and the day(date) is weekday rate
would be 1500/- and so on. I have tried may formula including this one
=INDEX(J19:J22,MATCH(1,(TEXT(D19,"ddd")=$M$19:$S$22)*(E19>=--LEFT($K$19:$K$22,5))*(E19<=--RIGHT($K$19:$K$22,5)),0))
but I am getting #N/A

Date Time on Rate
air
08/01/2007 07:13:12.0 #N/A
08/01/2007 08:13:55.1
08/01/2007 09:07:46.2
09/01/2007 10:22:17.2
09/01/2007 11:18:21.1
09/01/2007 18:30:26.2

Can any one help me with a formula for the same.

Thanks in advance.

 

[Bob Phillips]

=INDEX(J19:J22,MATCH(1,
((M19:M22="All
Days")+((M19:M22="Weekdays")*(WEEKDAY(C19,2)<6))+((M19:M22="Weekends")*(WEEKDAY(C19,2)>5)))*
(E19>=--LEFT($K$19:$K$22,5))*(E19<=--RIGHT($K$19:$K$22,5)),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

Even if you get the formul correct, it will not work at 11:59.30
I would simplify the formula by putting the start range in one colum and the
end range in a second column

Use the look up function and key only on the start column

07:00
12:00
18:00

Lookup return any value less than the lookup value. So any time between 0
< 12:00 will return the 07:00 line. any time between 12:00 < 18:00 will
return 12:00. Any time > 18:00 will return 18:00.

 

[shaji]

It is great, thanks Bob

=INDEX(J19:J22,MATCH(1,
((M19:M22="All
Days")+((M19:M22="Weekdays")*(WEEKDAY(C19,2)<6))+((M19:M22="Weekends")*(WEEKDAY(C19,2)>5)))*
(E19>=--LEFT($K$19:$K$22,5))*(E19<=--RIGHT($K$19:$K$22,5)),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)