Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Better Workday function.
Hi,
Few days back i posted this. http://www.microsoft.com/office/comm...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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Better Workday function.
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Better Workday function.
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 On Wed, 7 Apr 2010 21:03:01 -0700, XKruodo wrote: Hi, Few days back i posted this. http://www.microsoft.com/office/comm...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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WORKDAY Function | Excel Worksheet Functions | |||
Using WORKDAY function | Excel Discussion (Misc queries) | |||
WORKDAY function | Excel Worksheet Functions | |||
Workday function | Excel Discussion (Misc queries) | |||
How do I get the WORKDAY function? | Excel Discussion (Misc queries) |