searching in the list of dates

[Guest]

I hope that someone can help me with the following:

When filling in a specific date I have to tjeck for holidays which are
filled in, in another sheet. Is there a special function for this?

 

[Viquar]

Try the following function
=NETWORKDAYS(A2,B2,C3:C14)

Where cell A2 holds the start date (or the lower date)
B2 holds the end date
and the Column C from range C2:C14 holds the holidays.

To use this function you must have the Analysis ToolPack add-in
installed. Click Tools--> Addins --> Select Analysis Toolpack.

 

[Bob Phillips]

=ISNUMBER(MATCH(date_cell,holiday_lits,0))

will return TRUE if it is a holiday



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

Looks like a nice solution but I do not have ISNUMBER and MATCH, although I
have added Ananlysis Toolpack. Do you know how/where I can get the functions?

 

[Dave Peterson]

Both those functions are in excel.

If you're not using an English version of excel, then that could be the problem.

You may want to share the excel language you're using.

Or you could try this.
Create a new worksheet
Hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window
type this and hit enter:
range("a1").formula = "=isnumber(b1)"

then type this and hit enter:
range("a2").formula = "=match(b2,c:c,0)"

Then back to that worksheet and look at the formulas in A1 and A2 (ignore the
values returned).

 

[Guest]

Hi Dave,

-Thanks a lot (and the rest of you guys)....