Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula required
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula required
=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) "shaji" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula required
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula required
It is great, thanks Bob
"Bob Phillips" wrote: =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) "shaji" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help Required | Excel Discussion (Misc queries) | |||
FORMULA REQUIRED | Excel Discussion (Misc queries) | |||
Formula help required!!! | Excel Worksheet Functions | |||
Formula Required | Excel Worksheet Functions | |||
A Formula required - please! | Excel Discussion (Misc queries) |