Problem with Identifying Sun & Holidays, M-F, Sat for analysis

[Sunny54321]

Problem with Identifying Sun & Holidays, M-F, Sat, (using Excel 2010)

I have been trying to create a sheet where the user can conduct some type of analysis (MEAN, MEDIAN, MODE) over some data that occurs over a ‘SELECTABLE’ range of dates. The user should be able to select one of three sets; “M-F”, “Sat” or “Sun & Holidays”. The data would have a list of dates to the far left “COL A”. MEASUREMENTS would be in “COL B, C, D, E”. I have created an array of HOLIDAYS on a separate area to the far right (for now).

I have been experiencing difficulties in trying to pull off calculating the relevant dates. Any suggestions? I have tried to create a separate column having a “conditional” equation to “populate” with the dates of interest, then sum the data for those specific dates that are identified.

For example, I want to get the MEAN from Jan 31, 2012 to Jan 31, 2013 but have the option to select M-F or Sun&HOL or Sat (using a cell that has a "Data Validation" pull down list somewhere)

Any recommendations are welcome.

Thanks,
Sunny

DATE
Jan 31, 2013 5 6 7 8
Jan 29, 2013 8 7 6 9
.
.
.
Jan 31, 2012 7 3 5 7
Jan 29, 2012 1 4 4 3
.
.
Feb 14, 2011


HOLIDAYS

New Years Eve	31-Dec-11
New Years Day	1-Jan-12
Martin Luther King's Bday	16-Jan-12
President's Day	20-Feb-12
Cezar Chavez	31-Mar-12
Memorial Day	28-May-12
Fourth of July	4-Jul-12
Labor Day	3-Sep-12
Veterans Day	11-Nov-12
Thanksgiving	22-Nov-12
Black FR	23-Nov-12
Christmas Eve	24-Dec-12
Christmas Day	25-Dec-12
New Years Eve	31-Dec-11
New Years Day	1-Jan-12
Martin Luther King's Bday	16-Jan-12
President's Day	20-Feb-12
Cezar Chavez	31-Mar-12
Memorial Day	5/28/2012
Fourth of July 2012	4-Jul-12
Labor Day 2012	3-Sep-12
Veterans Day 2012	12-Nov-12
Thanksgiving 2012	22-Nov-12
Black FR	23-Nov-12
Christmas Eve 2012	24-Dec-12
Christmas Day 2012	25-Dec-12
New Years Eve 2012	31-Dec-12
New Years Day	1-Jan-13
Martin Luther King's Bday	21-Jan-13
President's Day	18-Feb-13
Cezar Chavez	29-Mar-13
Memorial Day	27-May-13
Fourth of July	4-Jul-13
Labor Day	2-Sep-14
Veterans Day	11-Nov-14
Thanksgiving	28-Nov-14
Black FR	29-Nov-14
Christmas Eve	24-Dec-14
Christmas Day	25-Dec-14
New Years Eve	31-Dec-14
New Years Day	1-Jan-15
Martin Luther King's Bday	20-Jan-15
President's Day	17-Feb-15
Cezar Chavez	28-Mar-15
Memorial Day	26-May-15
Fourth of July	4-Jul-15
Labor Day	1-Sep-15
Veterans Day	10-Nov-15
Thanksgiving	27-Nov-15
Black FR	28-Nov-15
Christmas Eve	24-Dec-15
Christmas Day	25-Dec-14
New Years Eve	31-Dec-15
New Years Day	1-Jan-16

<tbody>
</tbody>

 

[mole999]

If f22 holds a date then =TEXT(F22,"ddd") will return the day and you can filter for that, though it won't do holidays on that alone