If AND OR Help

**Pjcan1** · 07-20-2017, 11:48 AM

I having been trying, to no avail, to write a statement which takes into account the following variable

Weekday is not 1 or 7 - i.e. Weekends

That the time (broken into Hours and Minutes in seperate columns) is either:

Greater than 07:30 but less than 08:30

or

Greater than 18:00 but less than 18:30

Therefore if the weekday is 2-6 and either of the time statements are true I want Yes, if either the weekday is 1 or 7 or the time is outside of those times then No

**etaf** · 07-20-2017, 12:13 PM

an example sheet would help
I assume you are testing a date to get the weekday and its not a field

if you use
=weekday(A1,2)
the ,2 uses a 1 for monday

so now you need to know if the date is <6 for a weekday and not worry about a formula for 2-6

=AND(WEEKDAY(A1,2)<6, OR(AND(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIMEVALUE("18:00"),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<=TIMEVALUE("18:30")), AND(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIMEVALUE("07:30"),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<=TIMEVALUE("08:30"))))

=if( AND(WEEKDAY(A1,2)<6, OR(AND(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIMEVALUE("18:00"),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<=TIMEVALUE("18:30")), AND(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>=TIMEVALUE("07:30"),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<=TIMEVALUE("08:30")))), "yes","NO")

**Pjcan1** · 07-20-2017, 12:26 PM

No i have the weekday already, here is an example worksheet (attached), in the Extended Day field I have typed what I would want to appear in each instance

**etaf** · 07-20-2017, 12:43 PM

you can still use the date field A2
and Add another nested IF to test if a weekend

=IF(WEEKDAY(A2,2)>5,"Weekend", IF( AND(WEEKDAY(A2,2)<6, OR(AND(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))>=TIMEVALUE("18:00"),TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIMEVALUE("18:30")), AND(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))>=TIMEVALUE("07:30"),TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIMEVALUE("08:30")))), "YES","NO"))