How can I test if a date is a workday?

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

Good afternoon Nolo

Use the weekday function something like this:

a = "01/01/2005"
b = Weekday(a)
MsgBox b

This will return a number from 1 to 7, where, Sunday is 1, and Saturday is 7.
The example above should return 7 (Saturday)

HTH

DominicB

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)


<[email protected]> wrote in message
news:[email protected]...
> 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(A1,E2:E6,0))),"weekday","not
weekday")

Cheers
JulieD

<[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:%2351R%[email protected]...
> 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)
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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, [email protected] 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, [email protected] 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