WORKDAY function and holidays

[pplperth]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

Does anyone else have problems with Excel 2008's WORKDAY function and holidays?

It seems to me that holidays are ignored. I have entered a list of holiday days in a range of cells and have referred to these in the WORKDAY formula, but this seems to have no effect on the WORKDAY function.

NETWORKDAYS seems to work OK and takes the same range of holiday cels into account.

Try, for example, entering Friday 25 December 2009 as a holiday and refer to it in a formula. Excel will still schedule on 25 December 2009.

Can anyone else confirm this?

Using Excel 12.2.1 and Mac OS X 10.6.

 

[JE McGimpsey]

Can anyone else confirm this?

Using Excel 12.2.1 and Mac OS X 10.6.

Yup - I've reported it. You should too, using Help/Send Feedback.

 

[pplperth]

Thanks, and I have also reported it.

This seems to be a large defect given that Excel has been around for so long.

Instant downgrade to Excel 2004 under way here.

 

[JE McGimpsey]

Thanks, and I have also reported it.

This seems to be a large defect given that Excel has been around for so long.

Instant downgrade to Excel 2004 under way here.

You don't NEED to downgrade, I think.

I looked back in the archives, because I recall reporting a similar bug
when XL08 first came out.

When WOKRDAY() was first converted from the Analysis Toolpak Add-in to a
native function in Windows XL 2007 (and subsequently in Mac XL 2008), it
had a bug. It only worked correctly in the 1900 date mode. In the (Mac
default) 1904 date mode, it skipped Fridays and Saturdays, rather than
Saturdays and Sundays.

This has been fixed (absent the holidays parameter) by updates to both
XL07 and XL08.

It appears, however, that XL08 (and possibly XL07, I haven't tried it)
still doesn't properly implement the function with the holidays
parameter specified, WHEN IN 1904 DATE MODE.

In the 1900 date system (Preferences/Calculation, uncheck the 1904 date
system checkbox), I get the correct return for your scenario in XL08.

I haven't tested thoroughly, but I suspect that changing date modes will
solve your problem until the function is fixed.

 

[pplperth]

You are correct I think.

A quick test here shows that when 1904 date mode in unchecked the WORKDAY function works properly, i.e. includes the holiday cells.

I will check this more fully at work tomorrow but your workaround looks good.

Thanks again for your feedback.

This defect and the workaround should be more widely known I think. As Excel 2008 chooses the 1904 date system by default the problem will also be present by default in new spreadsheets.

 

[Shane Devenshire]

Hi,

For what its worth, this problem still exists in 2007 SP2.

 

[JE McGimpsey]

For what its worth, this problem still exists in 2007 SP2.

Yeah, I finally got around to checking, too. Thanks for confirming that.

I'm not sure whether I think it's better that both versions are
consistent or not.

I'd guess that it's marginally more likely that the function will get
fixed (again) quickly.