| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
If/AND/OR help needed..
I'm having trouble with an IF,AND,OR formula...
A4=08:00 B4=20:00 A10 is a time entry cell, such as 10:21 A11 is a date entry cell, such as 20/03/2005 A12 works out the day of the week according to A11 (=A11 formatted as "dddd") I need the following: - If the time entered in A10 is between A4 and B4 and the day of week is NOT Saturday or Sunday the the answer should be "PEAK". If it is Saturday or sunday, the answer should be "OFFPEAK" regardless of the time. 10:21 on 20/03/05(sun) should be OFFPEAK 10:21 on 21/03/05 (mon) should be PEAK 20:05 on 21/03/05 (mon) should be OFFPEAK Hope I explained it well TIA |
|
#2
|
|||
|
|||
|
=IF(OR(WEEKDAY(A11)=1,WEEKDAY(A11)=7,A10<A4,A10B4 ),"OFFPEAK","PEAK")
-- HTH RP (remove nothere from the email address if mailing direct) "Anthony Slater" wrote in message ... I'm having trouble with an IF,AND,OR formula... A4=08:00 B4=20:00 A10 is a time entry cell, such as 10:21 A11 is a date entry cell, such as 20/03/2005 A12 works out the day of the week according to A11 (=A11 formatted as "dddd") I need the following: - If the time entered in A10 is between A4 and B4 and the day of week is NOT Saturday or Sunday the the answer should be "PEAK". If it is Saturday or sunday, the answer should be "OFFPEAK" regardless of the time. 10:21 on 20/03/05(sun) should be OFFPEAK 10:21 on 21/03/05 (mon) should be PEAK 20:05 on 21/03/05 (mon) should be OFFPEAK Hope I explained it well TIA |
|
#3
|
|||
|
|||
|
Thanks Bob, that worked a treat...
....have a decent day "Bob Phillips" wrote: =IF(OR(WEEKDAY(A11)=1,WEEKDAY(A11)=7,A10<A4,A10B4 ),"OFFPEAK","PEAK") -- HTH RP (remove nothere from the email address if mailing direct) "Anthony Slater" wrote in message ... I'm having trouble with an IF,AND,OR formula... A4=08:00 B4=20:00 A10 is a time entry cell, such as 10:21 A11 is a date entry cell, such as 20/03/2005 A12 works out the day of the week according to A11 (=A11 formatted as "dddd") I need the following: - If the time entered in A10 is between A4 and B4 and the day of week is NOT Saturday or Sunday the the answer should be "PEAK". If it is Saturday or sunday, the answer should be "OFFPEAK" regardless of the time. 10:21 on 20/03/05(sun) should be OFFPEAK 10:21 on 21/03/05 (mon) should be PEAK 20:05 on 21/03/05 (mon) should be OFFPEAK Hope I explained it well TIA |
|
#4
|
|||
|
|||
|
-- HTH RP (remove nothere from the email address if mailing direct) "Anthony Slater" wrote in message ... Thanks Bob, that worked a treat... ...have a decent day "Bob Phillips" wrote: =IF(OR(WEEKDAY(A11)=1,WEEKDAY(A11)=7,A10<A4,A10B4 ),"OFFPEAK","PEAK") -- HTH RP (remove nothere from the email address if mailing direct) "Anthony Slater" wrote in message ... I'm having trouble with an IF,AND,OR formula... A4=08:00 B4=20:00 A10 is a time entry cell, such as 10:21 A11 is a date entry cell, such as 20/03/2005 A12 works out the day of the week according to A11 (=A11 formatted as "dddd") I need the following: - If the time entered in A10 is between A4 and B4 and the day of week is NOT Saturday or Sunday the the answer should be "PEAK". If it is Saturday or sunday, the answer should be "OFFPEAK" regardless of the time. 10:21 on 20/03/05(sun) should be OFFPEAK 10:21 on 21/03/05 (mon) should be PEAK 20:05 on 21/03/05 (mon) should be OFFPEAK Hope I explained it well TIA |
|
#5
|
|||
|
|||
|
"Anthony Slater" wrote in message ... Thanks Bob, that worked a treat... ...have a decent day Decent? I'm going to have a great day :-) |
|
#6
|
|||
|
|||
|
OR(WEEKDAY(A11)=1,WEEKDAY(A11)=7,
Another option might be: WEEKDAY(A1,2)<=5 (by shifting Monday to the start of the week) -- Dana DeLouis Win XP & Office 2003 "Anthony Slater" wrote in message ... Thanks Bob, that worked a treat... ...have a decent day "Bob Phillips" wrote: =IF(OR(WEEKDAY(A11)=1,WEEKDAY(A11)=7,A10<A4,A10B4 ),"OFFPEAK","PEAK") -- HTH RP (remove nothere from the email address if mailing direct) "Anthony Slater" wrote in message ... I'm having trouble with an IF,AND,OR formula... A4=08:00 B4=20:00 A10 is a time entry cell, such as 10:21 A11 is a date entry cell, such as 20/03/2005 A12 works out the day of the week according to A11 (=A11 formatted as "dddd") I need the following: - If the time entered in A10 is between A4 and B4 and the day of week is NOT Saturday or Sunday the the answer should be "PEAK". If it is Saturday or sunday, the answer should be "OFFPEAK" regardless of the time. 10:21 on 20/03/05(sun) should be OFFPEAK 10:21 on 21/03/05 (mon) should be PEAK 20:05 on 21/03/05 (mon) should be OFFPEAK Hope I explained it well TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time format help needed | Excel Worksheet Functions | |||
| Major help needed | Excel Discussion (Misc queries) | |||
| Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions | |||
| If statement help needed please | Excel Discussion (Misc queries) | |||
| Autofit in excel - cell is large than what is needed or wanted & . | Excel Discussion (Misc queries) | |||
Linear Mode
