Statement to search date range and eliminate weekends and holidays

**mrnuce** · 02-14-2014, 01:49 PM

Hello,

I'm trying to search data from another program with and IF statement and filter out dates and times. I have a mm/dd/yy hr:mm format in a series of cells. I've converted that to hh:mm using the MOD command. I also have a range of data that corresponds to each date in the next cell range. So far, I use this formula to evaluate the date:time cells for anything that is greater than 9:00 and less than 21:00. If this situation occurs, then I want it to show the corresponding data in the adjacent cell, otherwise leave blank.

For cell F37 formula is =IF((E37>TIME(8,59,0)+0)*(E37<TIME(21,1,0)),B37," ")

this works exactly as I want EXCEPT, I want to eliminate all weekends and holidays as well. What would I add to this formula to not only evaluate based on time, but on weekends and holidays as well.

Thanks in advance for any help.

**MarvinP** · 02-14-2014, 01:58 PM

Hi rmnuce and welcome to the forum,

Excel has a Weekday() function that will return the Day of the Week number that might help in your formula(s). There is also a function called
Workday() that takes a list of holidays you can specify. My thinking is you can use either or both of these built-in functions to get to your desired answer.
http://social.msdn.microsoft.com/For...forum=exceldev
http://office.microsoft.com/en-us/ex...010343038.aspx

**mrnuce** · 02-19-2014, 08:04 AM

thanks for the quick input. I was able to get this formula to work for me, although it does not take into account holidays. I ended up using an IF(OR statement against the list of holidays in another cell that is doing the job for me.

=IF(AND(OR(WEEKDAY($A3)<>1),OR(WEEKDAY($A3)<>7),($E3>TIME(8,59,0)+0)*($E3<TIME(21,1,0))),$B3," ")

**daddylonglegs** · 02-19-2014, 11:23 AM

You can use NETWORKDAYS function to evaluate whether a date is a working day or not, e.g. with holiday dates in H2:H10 this formula

=NETWORKDAYS($A3,$A3,$H$2:$H$10) will return 1 if A3 is a working day or zero uf it's a weekend or holiday.....so your formula can be as follows:

=IF(AND(NETWORKDAYS($A3,$A3,$H$2:$H$10)=1,$E3>TIME(8,59,0),$E3<TIME(21,1,0)),$B3,"")