Better Workday function.

[XKruodo]

Hi,
Few days back i posted this.

http://www.microsoft.com/office/com...70eea5309c2&cat=&lang=en&cr=US&sloc=en-us&p=1

I couldn't make the UDF work. So i used Pearson's better workday function.
I used the example workboook and yes, that's what i was looking for.
Now how do i use it for multiple dates?

My sheet contains dates in colunm A.

1. How do i get due dates in Column B?
2. Do i need to make 2 sheets, one for MWF batch and another for TTS batch?
3. My list of holidays is entire H column. Pearson's sheet gives only about
10 holidays..
4. I have office 2007. So please suggest functions that work with 2007.

TIA.

 

[Bernd P]

Hello,

I cannot see any restriction to 10 holidays in Chip's function.

But if you set the hours to 0:00 - 24:00 in my UDF count_hours you
might like to use:
http://sulprobil.com/html/count_hours.html

Regards,
Bernd

 

[Chip Pearson]

Copy the code into some regular code module (e.g., Module1, not one of
the sheet modules and not the ThisWorkbook module). Then, in A1, enter
the start date, say 8-April-2010. In B1, enter then number of days to
calculate from the data in A1. For example, enter 16 into B1 to
calculate the date that is 16 work days from the date in A1. Enter
your holidays in H1:H100 or whatever range you need to list all your
holidays.

There is no limit to the number of holidays, but (1) the range used in
the formula must contain all the holidays, and (2) you cannot use an
entire column as the holiday range. That is don't use H:H. Instead,
use H1:H1000 or whatever range will encompass all the holidays. If
the final calculated date will be a more than a year in the future,
(e.g., 500 days in the future), the holiday dates must be specified
for both years. E.g., if 5-May-2010 is a holiday and number of days is
500, both 5-May-2010 and 5-May-2011 must appear in the holiday list.

Then, in C1, enter

=Workday2(A1,B1,4+8,H1:H100)

In this formula, the 4+8 indicates that Tuesdays and Wednesdays are to
be excluded. You specify the days of week to exclude by adding the
values assigned to each day of week. The day of week numbers are

Enum EDaysOfWeek
Sunday = 1 ' 2 ^ (vbSunday - 1)
Monday = 2 ' 2 ^ (vbMonday - 1)
Tuesday = 4 ' 2 ^ (vbTuesday - 1)
Wednesday = 8 ' 2 ^ (vbWednesday - 1)
Thursday = 16 ' 2 ^ (vbThursday - 1)
Friday = 32 ' 2 ^ (vbFriday - 1)
Saturday = 64 ' 2 ^ (vbSaturday - 1)
End Enum

You can add up to 6 days of week numbers (but only each day number
once) to exclude. If you attempt to exclude all the days of the week,
you will get a #VALUE error.

That's about all there is the Workday2 function.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com