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

How can I test if a date is a workday?
 

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

How can I test if a date is a workday?
 

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

How can I test if a date is a workday?
 

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

How can I test if a date is a workday?
 

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

How can I test if a date is a workday?
 

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

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

Nolo

How can I test if a date is a workday?
 

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