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
[TABLE="width: 301"]
<tbody>[TR]
[TD]New Years Eve[/TD]
[TD]31-Dec-11[/TD]
[/TR]
[TR]
[TD]New Years Day[/TD]
[TD]1-Jan-12[/TD]
[/TR]
[TR]
[TD]Martin Luther King's Bday[/TD]
[TD]16-Jan-12[/TD]
[/TR]
[TR]
[TD]President's Day[/TD]
[TD]20-Feb-12[/TD]
[/TR]
[TR]
[TD]Cezar Chavez[/TD]
[TD]31-Mar-12[/TD]
[/TR]
[TR]
[TD]Memorial Day[/TD]
[TD]28-May-12[/TD]
[/TR]
[TR]
[TD]Fourth of July[/TD]
[TD]4-Jul-12[/TD]
[/TR]
[TR]
[TD]Labor Day[/TD]
[TD]3-Sep-12[/TD]
[/TR]
[TR]
[TD]Veterans Day[/TD]
[TD]11-Nov-12[/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]22-Nov-12[/TD]
[/TR]
[TR]
[TD]Black FR[/TD]
[TD]23-Nov-12[/TD]
[/TR]
[TR]
[TD]Christmas Eve[/TD]
[TD]24-Dec-12[/TD]
[/TR]
[TR]
[TD]Christmas Day[/TD]
[TD]25-Dec-12[/TD]
[/TR]
[TR]
[TD]New Years Eve[/TD]
[TD]31-Dec-11[/TD]
[/TR]
[TR]
[TD]New Years Day[/TD]
[TD]1-Jan-12[/TD]
[/TR]
[TR]
[TD]Martin Luther King's Bday[/TD]
[TD]16-Jan-12[/TD]
[/TR]
[TR]
[TD]President's Day[/TD]
[TD]20-Feb-12[/TD]
[/TR]
[TR]
[TD]Cezar Chavez[/TD]
[TD]31-Mar-12[/TD]
[/TR]
[TR]
[TD]Memorial Day[/TD]
[TD]5/28/2012[/TD]
[/TR]
[TR]
[TD]Fourth of July 2012[/TD]
[TD]4-Jul-12[/TD]
[/TR]
[TR]
[TD]Labor Day 2012[/TD]
[TD]3-Sep-12[/TD]
[/TR]
[TR]
[TD]Veterans Day 2012[/TD]
[TD]12-Nov-12[/TD]
[/TR]
[TR]
[TD]Thanksgiving 2012[/TD]
[TD]22-Nov-12[/TD]
[/TR]
[TR]
[TD]Black FR[/TD]
[TD]23-Nov-12[/TD]
[/TR]
[TR]
[TD]Christmas Eve 2012[/TD]
[TD]24-Dec-12[/TD]
[/TR]
[TR]
[TD]Christmas Day 2012[/TD]
[TD]25-Dec-12[/TD]
[/TR]
[TR]
[TD]New Years Eve 2012[/TD]
[TD]31-Dec-12[/TD]
[/TR]
[TR]
[TD]New Years Day[/TD]
[TD]1-Jan-13[/TD]
[/TR]
[TR]
[TD]Martin Luther King's Bday[/TD]
[TD]21-Jan-13[/TD]
[/TR]
[TR]
[TD]President's Day[/TD]
[TD]18-Feb-13[/TD]
[/TR]
[TR]
[TD]Cezar Chavez[/TD]
[TD]29-Mar-13[/TD]
[/TR]
[TR]
[TD]Memorial Day[/TD]
[TD]27-May-13[/TD]
[/TR]
[TR]
[TD]Fourth of July[/TD]
[TD]4-Jul-13[/TD]
[/TR]
[TR]
[TD]Labor Day[/TD]
[TD]2-Sep-14[/TD]
[/TR]
[TR]
[TD]Veterans Day[/TD]
[TD]11-Nov-14[/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]28-Nov-14[/TD]
[/TR]
[TR]
[TD]Black FR[/TD]
[TD]29-Nov-14[/TD]
[/TR]
[TR]
[TD]Christmas Eve[/TD]
[TD]24-Dec-14[/TD]
[/TR]
[TR]
[TD]Christmas Day[/TD]
[TD]25-Dec-14[/TD]
[/TR]
[TR]
[TD]New Years Eve[/TD]
[TD]31-Dec-14[/TD]
[/TR]
[TR]
[TD]New Years Day[/TD]
[TD]1-Jan-15[/TD]
[/TR]
[TR]
[TD]Martin Luther King's Bday[/TD]
[TD]20-Jan-15[/TD]
[/TR]
[TR]
[TD]President's Day[/TD]
[TD]17-Feb-15[/TD]
[/TR]
[TR]
[TD]Cezar Chavez[/TD]
[TD]28-Mar-15[/TD]
[/TR]
[TR]
[TD]Memorial Day[/TD]
[TD]26-May-15[/TD]
[/TR]
[TR]
[TD]Fourth of July[/TD]
[TD]4-Jul-15[/TD]
[/TR]
[TR]
[TD]Labor Day[/TD]
[TD]1-Sep-15[/TD]
[/TR]
[TR]
[TD]Veterans Day[/TD]
[TD]10-Nov-15[/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]27-Nov-15[/TD]
[/TR]
[TR]
[TD]Black FR[/TD]
[TD]28-Nov-15[/TD]
[/TR]
[TR]
[TD]Christmas Eve[/TD]
[TD]24-Dec-15[/TD]
[/TR]
[TR]
[TD]Christmas Day[/TD]
[TD]25-Dec-14[/TD]
[/TR]
[TR]
[TD]New Years Eve[/TD]
[TD]31-Dec-15[/TD]
[/TR]
[TR]
[TD]New Years Day[/TD]
[TD]1-Jan-16[/TD]
[/TR]
</tbody>[/TABLE]

 

[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