list of weekday dates filtering holidays

[Bert]

I have (currently) two columns of dates. Column C has a list of days for
holidays and school vacation days. (there are a few blank cells scattered
in, too.) Column D is a list of all the dates that fall on weekdays between
the first and last days of school. Ideally, I'd like that list NOT to
include the holidays, or perhaps a third column which has the no-school days
filtered out. I'm wondering what the most effective way to do this is.
(I'm using Excel 2003, running on XP.)
Ultimately, I'll want to cut and paste these dates into an HTML file, but
I'll just do that "manually".
Thanks,

Bert

 

[Ron Coderre]

See if this helps

With
A1:B10 containing this list of dates:
01/01/07 (New Year's Day)
01/15/07 (Birthday of Martin Luther King, Jr.)
02/19/07 (Washington’s Birthday)
05/28/07 (Memorial Day)
07/04/07 (Independence Day)
09/03/07 (Labor Day)
10/08/07 (Columbus Day)
11/12/07 (Veterans Day)
11/22/07 (Thanksgiving Day)
12/25/07 (Christmas Day)

And assuming the school year runs from 04-SEP through 24-DEC

D1: 9/4/2007
D2: =IF(D1<=DATE(2007,12,24),WORKDAY(D1,1,$A$1:$A$10),"Done")
Copy that formula down until it returns "Done"

That will list every weekday, excluding holidays from 04-SEP through 24-DEC.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

 

[Bert]

Thanks. I think this is close to what I need. I tried modifying your
formula to:
=IF(D1<=DATE(2008,6,24),WORKDAY(D1,1,$C$1:$C$24),"Done")
because the the school year runs 9/4/2007 to 6/15/2008 +/- depending on
missed days due to weather.
Also, I've listed in the C1:C24 range all the days we have off for
vacations.
However...when I copy the formula into D2, it shows "Done" immediately.
(Actually, even using the formula as it is (except changing $A$1:$A$10 to
$C$1:$C$10) got the same result: "Done" shows in D2.
Any insights how I broke it?
Thanks,
Bert

 

[Ron Coderre]

It sounds like D1 does not contain an actual Excel date. It should contain
9/4/2007.

Try this in an empty cell: =ISNUMBER(D1)

If it returns FALSE....D1 contains text (even if it LOOKS like a number)
-Set the format of D1 to your preferred date format
-Type 9/4/2007 in D1 and Press ENTER.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

 

[Bert]

I messed up the cell designation-which accounted for one problem-but then I
was getting a #Name? error. The problem was with the
WORKDAY(D2,1,$C$1:$C$10) function, but Help for WORKDAY suggested loading a
particular add-in and that seemed to do the trick.
Thanks, Ron!
Bert