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$2 2)*(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.