I need to test whether a date entered by the user is a workday, so I
can use the "true" or "false" returned by the test in another formula
in the spreadsheet. I have a cell range of holidays set up. The date
entered by the user could be any date in the year and any day of the
week.

Thanks,

Nolo

Nolo,

Assuming date in E1, and the holidays in named range called holidays

=AND(WEEKDAY(E1,2)<5,COUNTIF(holidays,E1)=0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I need to test whether a date entered by the user is a workday, so I
can use the "true" or "false" returned by the test in another formula
in the spreadsheet. I have a cell range of holidays set up. The date
entered by the user could be any date in the year and any day of the
week.

Thanks,

Nolo

Hi

with your holidays in the range E2:E6 and assuming that both saturday and
sunday are non-workdays, the following formula will return "workday" or "not
workday" for a date in A1

=IF(AND(WEEKDAY(A1)<1,WEEKDAY(A1)<7,ISNA(MATCH(A 1,E2:E6,0))),"weekday","not
weekday")

Cheers
JulieD

wrote in message
oups.com...
I need to test whether a date entered by the user is a workday, so I
can use the "true" or "false" returned by the test in another formula
in the spreadsheet. I have a cell range of holidays set up. The date
entered by the user could be any date in the year and any day of the
week.

Thanks,

Nolo

oops. small error

=AND(WEEKDAY(E1,2)<6,COUNTIF(holidays,E1)=0)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Nolo,

Assuming date in E1, and the holidays in named range called holidays

=AND(WEEKDAY(E1,2)<5,COUNTIF(holidays,E1)=0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I need to test whether a date entered by the user is a workday, so I
can use the "true" or "false" returned by the test in another formula
in the spreadsheet. I have a cell range of holidays set up. The date
entered by the user could be any date in the year and any day of the
week.

Thanks,

Nolo

On 8 Mar 2005 08:57:10 -0800, wrote:

I need to test whether a date entered by the user is a workday, so I
can use the "true" or "false" returned by the test in another formula
in the spreadsheet. I have a cell range of holidays set up. The date
entered by the user could be any date in the year and any day of the
week.

Thanks,

Nolo


=WORKDAY(A1-1,1,Holidays)=A1

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.



--ron

=WORKDAY(A1-1,1,Holidays)=A1

Ron, I love the brilliant simplicity of your solution! Thanks so much.

Nolo

On 8 Mar 2005 11:59:55 -0800, wrote:

=WORKDAY(A1-1,1,Holidays)=A1

Ron, I love the brilliant simplicity of your solution! Thanks so much.

Nolo

You're welcome. Glad to help.


--ron