Excel IF + AND or OR Statements

[Beaglemania]

I am trying to do two things, but can't seem to get my IF statements to work. I have a date in column M, time in column N, and day of the week in column O. I want to add information in columns P and Q:

1. I am trying to show when a day would fall on a weekend or holiday. I created a table (cells T2:X13) that shows all of the holidays in each year and added the dates for every other Friday to the bottom of the table (cells T14:X39) because we have every other Friday off (we work a longer day on other days of the week), so those Fridays are considered weekend days for our purposes. I also have a custom text column (column O) that I created to show the day of the week based on the date in column M. So I need to put "Weekend/Holiday" in column P whenever column O says Saturday or Sunday or the date is Column M is the same as one of the dates in cells T2:X39. My formula of =IF(OR(O2="Saturday",O2="Sunday"),"Weekend/Holiday"," ") works for the Saturdays and Sundays, but I get a #VALUE ! error message as soon as I change it to =IF(OR(O2="Saturday",O2="Sunday",M2=T2:X39),"Weekend/Holiday"," ") to try to add the holidays and Fridays off.

2. I am trying to put information in column P about whether the times in column N are after hours, with after hours being from 17:30:01 (after 5:30 PM) to 7:29:59 (before 7:30 AM). So I want column Q to say "After hours" if the time in column N (formatted as Time, e.g., 15:38:18 shows on the screen but it it shows as 15:38:18 PM when I click on the cell; I can't get rid of the PM) is after 17:30:00 and before 7:30:00. The statement I tried (with some variations based on the error messages) was =IF(AND(N2>17:30:00<7:29:59),"After hours"," ").

Any suggestions on how to fix these?

 

[AlphaFrog]

1.)=IF(OR(WEEKDAY(M2,2)>=6,COUNTIF(T2:X39,M2)),"Weekend/Holiday","")

2.)=IF(OR(N2 > TIMEVALUE("17:30:00"),N2 < TIMEVALUE("7:29:59")),"After hours","")<timevalue("7:29:59")),"after hours","")<="" html=""></timevalue("7:29:59")),"after>

 

[RasGhul]

This worked for me, not as simple as AlphaFrog's though

=IF(OR(WEEKDAY(M2,1)=1,WEEKDAY(M2,1)=7,M2=SUMPRODUCT(($T$2:$X$39=M2)*$T$2:$X$39)),"Weekend/Holiday","")

 

[Beaglemania]

Thank you - These worked for me!