Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Weekend days other than Sat Sun
I need one more argument in Networkdays function after Startdate and Enddate,
weekend day or days in form of 1,2,3,4,5,6,7 before holdays Any solution Thanx |
#2
|
|||
|
|||
What do you mean by this? Give some data and expected results.
-- HTH RP (remove nothere from the email address if mailing direct) "rkk" wrote in message ... I need one more argument in Networkdays function after Startdate and Enddate, weekend day or days in form of 1,2,3,4,5,6,7 before holdays Any solution Thanx |
#3
|
|||
|
|||
Sorry, but that's not a option. The function was not written to handle that
problem. If your problem is that your weekend days are not Saturday and Sunday, but (for example) Friday and Saturday, you can write the formula as =NETWORKDAYS(A1+1,B1+1,HolidayList) where A1 and B1 contain the starting and ending dates, and the dates in the holiday list have all been incremented by 1 as well. If that's not it, you would need to write a VBA function of your own. If you can describe your problem more fully, including what the extra argument means, perhaps we can help. On Sat, 22 Jan 2005 07:45:02 -0800, rkk wrote: I need one more argument in Networkdays function after Startdate and Enddate, weekend day or days in form of 1,2,3,4,5,6,7 before holdays Any solution Thanx |
#4
|
|||
|
|||
Assuming...
A1 contains your start date B1 contains your end date C1:C10 contains your holidays Try the following... =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5),--(1-ISNUMBER(MATCH (ROW(INDIRECT(A1&":"&B1)),C1:C10,0)))) ....which calculates networking days from Monday through Thursday, excluding the referenced holidays, and where Monday=1, Tuesday=2, etc. Or, for more flexibility... =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={ 1,2,3,4})*(1-ISNUMBER(M ATCH(ROW(INDIRECT(A1&":"&B1)),C1:C10,0)))) Hope this helps! In article , rkk wrote: I need one more argument in Networkdays function after Startdate and Enddate, weekend day or days in form of 1,2,3,4,5,6,7 before holdays Any solution Thanx |
#5
|
|||
|
|||
On Sat, 22 Jan 2005 07:45:02 -0800, rkk wrote:
I need one more argument in Networkdays function after Startdate and Enddate, weekend day or days in form of 1,2,3,4,5,6,7 before holdays Any solution Thanx test --ron |
#6
|
|||
|
|||
On Sat, 22 Jan 2005 07:45:02 -0800, rkk wrote:
I need one more argument in Networkdays function after Startdate and Enddate, weekend day or days in form of 1,2,3,4,5,6,7 before holdays Any solution Thanx Not possible to change the NetWorkdays function. However, Domenic has given you a worksheet function solution. Here is a VBA solution that allows you to define up to three weekend days (Sun=1; Sat = 7). It can be easily modified if more weekend days are required. To use this, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code into the window that opens. To use this, enter =NWrkDays(StartDate, EndDate) into some cell. The Holidays must be represented by a range and the argument is optional, and must be left blank if you will be defining weekend days. The weekend days are optional and are entered as numbers. So a formula were the weekend was Sat, Sun and Mon and where there were no holidays would be: =NWrkDays(StartDate, EndDate,,1,2,7) =========================== Function NWrkDays(StartDate As Date, EndDate As Date, _ Optional Holidays As Range = Nothing, _ Optional WeekendDay_1 As Integer = 0, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0) As Long Dim c As Range Dim i As Long, j As Long Dim Count As Long For i = StartDate To EndDate Count = Count + 1 Select Case Weekday(i) Case WeekendDay_1, WeekendDay_2, WeekendDay_3 Count = Count - 1 Case Else If Not Holidays Is Nothing Then For Each c In Holidays If i = c.Value Then Count = Count - 1 Exit For End If Next c End If End Select Next i NWrkDays = Count End Function ============================= --ron |
#7
|
|||
|
|||
Hi, Ron:
I'm always "fussing" about the speed of VBA code since it's almost always slower than built-in functions. I wonder if a couple of changes in the code would speed it up. The first (probably most important) is to read the holiday list into an array and search that rather than accessing the worksheet multiple times. Reading from or writing to a worksheet always creates a big bottleneck. The other is to eliminate multiple calls to Weekday. Using Charles Williams FastExcel2 to time the variations, if there are only two weekend days (Sat and Sun), a 2-year time span (1/1/03-12/31/05) and a Holiday list of 190 dates, the times are as follows: NetWorkDays 1 msec Your code 565 My code 41 For a shorter time span, 7/22/04 to 8/15/04, and a holiday list containing 2004 dates only (10 holidays). NetWorkDays 0.18 msec Your code 0.85 My code 0.46 Both VBA routines are real slouches compared to NETWORKDAYS, but if you need more than 2 weekend days, the alternatives are limited. BTW, your code and mine give the same results, identical to NETWORKDAYS. Function NWrkDays(StartDate As Date, EndDate As Date, _ Optional Holidays As Range = Nothing, _ Optional WeekendDay_1 As Integer = 0, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0) As Long Dim i As Long Dim Count As Long Dim H As Variant Dim w As Long Dim DoHolidays As Boolean If Not Holidays Is Nothing Then H = Holidays.Value2 DoHolidays = True End If w = Weekday(StartDate - 1) For i = StartDate To EndDate Count = Count + 1 w = (w Mod 7) + 1 Select Case w Case WeekendDay_1, WeekendDay_2, WeekendDay_3 Count = Count - 1 Case Else If DoHolidays Then If IsNumeric(Application.Match(i, H, 0)) Then Count = Count - 1 End If End Select Next i NWrkDays = Count End Function |
#8
|
|||
|
|||
On Sat, 22 Jan 2005 15:44:33 -0600, Myrna Larson
wrote: I'm always "fussing" about the speed of VBA code since it's almost always slower than built-in functions. I wonder if a couple of changes in the code would speed it up. The first (probably most important) is to read the holiday list into an array and search that rather than accessing the worksheet multiple times. Reading from or writing to a worksheet always creates a big bottleneck. The other is to eliminate multiple calls to Weekday. All good suggestions. And substantial speedup based on your timing results. Ordinarily, my preference is to use arrays. But I was a bit tired last night. I'm going to try something a bit different and I'll post back. --ron |
#9
|
|||
|
|||
On Sat, 22 Jan 2005 15:44:33 -0600, Myrna Larson
wrote: Using Charles Williams FastExcel2 to time the variations, if there are only two weekend days (Sat and Sun), a 2-year time span (1/1/03-12/31/05) and a Holiday list of 190 dates, the times are as follows: NetWorkDays 1 msec Your code 565 My code 41 For a shorter time span, 7/22/04 to 8/15/04, and a holiday list containing 2004 dates only (10 holidays). NetWorkDays 0.18 msec Your code 0.85 My code 0.46 It seems odd that your code is slower with the shorter time span. But I suppose that is within the limits of error of measurement. I was going to try to use ParamArray to read in both the holiday list and the weekend days. However, I think it is more valuable to have understandable function arguments in this instance. --ron |
#10
|
|||
|
|||
Hi, =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={ 1,2,3,4})*(1-ISNUMBER(M ATCH(ROW(INDIRECT(A1&":"&B1)),C1:C10,0)))) FWIW, in cases where the holidays range has a lot fewer dates than ROW(INDIRECT(A1&":"&B1)), this array formula: {=SUM(INT((B1-WEEKDAY(B1+1-{2;3;4;5;6})-A1+8)/7))-COUNT(MATCH(WEEKDAY(holidays), {2;3;4;5;6},0)+MATCH(holidays,ROW(INDIRECT(A1&":"& B1)),0))} As for the previous formula, you can adapt the constant array (twice in the formula) to include whatever day of the week you whish to count. Example: {2;4;6} counts only Mon,Wed,Fri Regards, Daniel M. |
#11
|
|||
|
|||
I just did more fiddling and was surprised by these results
1st routine: Dim Rng as Range Dim p As Variant 'because D won't be found Dim D As Long Dim i As Long Set Rng = Worksheets("Sheet1").Range("A2:A200") D = #12/31/2004# For i = 1 to 1000 p = Application.Match(D, Rng, 0) Next i 2nd routine: Dim v As Variant Dim p As Variant 'because D won't be found Dim D As Long Dim i As Long v = Worksheets("Sheet1").Range("A2:A200").Value2 D = #12/31/2004# For i = 1 To 1000 p = Application.Match(D, Rng, 0) Next i The first code, using MATCH with a worksheet range, took 29 milliseconds. Searching the same data in a variant array, took 64 milliseconds, slightly more than twice as long. I wonder if VBA is copying all of the data in the array back to Excel's dataspace every time MATCH is called? On Sun, 23 Jan 2005 07:42:28 -0500, Ron Rosenfeld wrote: On Sat, 22 Jan 2005 15:44:33 -0600, Myrna Larson wrote: Using Charles Williams FastExcel2 to time the variations, if there are only two weekend days (Sat and Sun), a 2-year time span (1/1/03-12/31/05) and a Holiday list of 190 dates, the times are as follows: NetWorkDays 1 msec Your code 565 My code 41 For a shorter time span, 7/22/04 to 8/15/04, and a holiday list containing 2004 dates only (10 holidays). NetWorkDays 0.18 msec Your code 0.85 My code 0.46 It seems odd that your code is slower with the shorter time span. But I suppose that is within the limits of error of measurement. I was going to try to use ParamArray to read in both the holiday list and the weekend days. However, I think it is more valuable to have understandable function arguments in this instance. --ron |
#12
|
|||
|
|||
On Sun, 23 Jan 2005 16:08:58 -0600, Myrna Larson
wrote: I just did more fiddling and was surprised by these results 1st routine: Dim Rng as Range Dim p As Variant 'because D won't be found Dim D As Long Dim i As Long Set Rng = Worksheets("Sheet1").Range("A2:A200") D = #12/31/2004# For i = 1 to 1000 p = Application.Match(D, Rng, 0) Next i 2nd routine: Dim v As Variant Dim p As Variant 'because D won't be found Dim D As Long Dim i As Long v = Worksheets("Sheet1").Range("A2:A200").Value2 D = #12/31/2004# For i = 1 To 1000 p = Application.Match(D, Rng, 0) Next i The first code, using MATCH with a worksheet range, took 29 milliseconds. Searching the same data in a variant array, took 64 milliseconds, slightly more than twice as long. I wonder if VBA is copying all of the data in the array back to Excel's dataspace every time MATCH is called? I've had some adult beverages this evening, and maybe that's why I don't see where, in your second routine, Rng is defined ??? --ron |
#13
|
|||
|
|||
I messed up the message. If you try to run what I posted here, you get an
Invalid Procedure Call or Argument message. In the 2nd message, the line was supposed to read p = Application.Match(D, v, 0) I've included a modification of the NWrkDays function that applies this lesson on speed. I compared 4 functions: (1) the built-in NETWORKDAYS function, (2) your original code, (3) my first code, and (4) NWrkDaysR. The dates are 5/15/2004, 7/15/2004, weekend days are 1 and 7, the holiday list has 190 entries. The times are NETWORKDAYS 1.07 Your original 31.47 My first code 2.45 'holidays read into a VBA array, then use MATCH NWrkDaysR 1.37 'use MATCH on the source range IMO NWrkDaysR compares favorably with the NETWORKDAYS, given its additional functionality. In the past I have posted code that was FASTER than NETWORKDAYS. AIR, I wrote it to handle only Sat and Sun as the weekend days. The reason it was faster was (1) calculate the number of full weeks between start date and end date and multiply that by 5 (2) calculate day-by-day only on the "tail", the days in the final partial week (3) subtract holidays between start date and end date via a binary search on the holiday list instead of MATCH The trade-off was that the holiday list had to be sorted ascending and it could not include any holidays that fell on a weekend day. ~~~~~~~~~~~~~~~~~~~~~~~~~ Function NWrkDaysR(StartDate As Date, EndDate As Date, _ Optional Holidays As Range = Nothing, _ Optional WeekendDay_1 As Integer = 0, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0) As Long Dim i As Long Dim Count As Long Dim H As Variant Dim w As Long Dim DoHolidays As Boolean DoHolidays = Not (Holidays Is Nothing) w = Weekday(StartDate - 1) For i = StartDate To EndDate Count = Count + 1 w = (w Mod 7) + 1 Select Case w Case WeekendDay_1, WeekendDay_2, WeekendDay_3 Count = Count - 1 Case Else If DoHolidays Then If IsNumeric(Application.Match(i, Holidays, 0)) Then _ Count = Count - 1 End If End Select Next i NWrkDaysR = Count End Function |
#14
|
|||
|
|||
On Sun, 23 Jan 2005 20:29:08 -0600, Myrna Larson
wrote: NETWORKDAYS 1.07 Your original 31.47 My first code 2.45 'holidays read into a VBA array, then use MATCH NWrkDaysR 1.37 'use MATCH on the source range Nice. --ron |
#15
|
|||
|
|||
{=SUM(INT((B1-WEEKDAY(B1+1-{2;3;4;5;6})-A1+8)/7))-COUNT(MATCH(WEEKDAY(holidays), {2;3;4;5;6},0)+MATCH(holidays,ROW(INDIRECT(A1&":"& B1)),0))} Better (faster) is this non-array formula (again, it's most appropriate for cases where one does not have big holiday list and A1 << B1) : =SUMPRODUCT(INT((B1-WEEKDAY(B1+1-{2;3;4;5;6})-A1+8)/7))-SUMPRODUCT(ISNUMBER( MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays =A1)*(holidays<=B1)) Regards, Daniel M. |
#16
|
|||
|
|||
Except that when I test it today, NWrkDaysR is take about 2.5 times as long as
NETWORKDAYS, not 30% longer. Don't know why... On Mon, 24 Jan 2005 08:06:00 -0500, Ron Rosenfeld wrote: On Sun, 23 Jan 2005 20:29:08 -0600, Myrna Larson wrote: NETWORKDAYS 1.07 Your original 31.47 My first code 2.45 'holidays read into a VBA array, then use MATCH NWrkDaysR 1.37 'use MATCH on the source range Nice. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating interest on number of days in the period | Excel Worksheet Functions | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions | |||
Calculating Production/Man Days in Excel | Excel Discussion (Misc queries) | |||
Re-assigning weekend days in "Networkdays" Function | Excel Discussion (Misc queries) | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |