27-May-2007 is a WORKDAY?

[mattmarnell]

I'm using the following formula in local Excel 07 and Excel Web
Services, with the same unexpected results.

=WORKDAY(DATE(2007,5,27),0,holidays)

it returns the following.

27-May-2007

Sunday, May 27, 2007, is a workday? I think not.

It has worked fine, and still does, for a spread of dates before and
after, but just not on this one...any ideas?

-- MM

 

[Rick Rothstein \(MVP - VB\)]

I'm using the following formula in local Excel 07 and Excel Web

Services, with the same unexpected results.

=WORKDAY(DATE(2007,5,27),0,holidays)

it returns the following.

27-May-2007

Sunday, May 27, 2007, is a workday? I think not.

It has worked fine, and still does, for a spread of dates before and
after, but just not on this one...any ideas?

It works the same way for May 20, 2007 too. The help files for this function
says...

"Returns a number that represents a date that is the
indicated number of working days before or after
a date (the starting date)."

Isn't the starting date exactly zero working days away from itself?
Basically, specifying zero days offset from a date gives you that date, no?
I mean, you are standing on a certain date and want to know the date will be
if you don't move from it.

Rick

 

[Guest]

The zero in your formula tells it to find the workday zero days after the
date specified so it is returning the date specified, Try it with a 1 and it
will return the next working day that doesn't appear in the range holiday.

Mike

 

[Guest]

According to your formula you are looking to return the workday that is zero
days before/after the referenced date. In my version of Excel, that formula
always returns the referenced date (ignoring holidays completely).

If you're trying to determine if the referenced date is a holiday or weekend
try these:

=NETWORKDAYS(DATE(2007,5,27),DATE(2007,5,27),holidays)=1
or
=AND(WEEKDAY(DATE(2007,5,27),2)<6,COUNTIF(holidays,DATE(2007,5,27))=0)

(In that example, the formulas return FALSE)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[MM]

First, thanks for the various bits of advice.

I now understand that throwing the function a days=0 does give
unexpected results (at least to those of us working off the API). So
it pays to perform a check using IF(days=0)... and/or to begin an
increasing series at 1 instead of 0 or negative numbers.

I don't believe this behavior is published anywhere or maybe it's just
me. What gives?

-- MM