S
salim purayil
how can i change nonworking date from saturday&sunday to frinday&Saturday
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
In what, the WORKDAY function, NETWORKSDAYS?
If it is NETWORKSDAYS, use
=SUMPRODUCT(INT((E1-WEEKDAY(E1+1-{2;3;4;5;6})-C1+8)/7))-SUMPRODUCT(ISNUMBER(
MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=C1)*(holidays<=E1))
where C1 and E1 hold the 2 dates, hiolidays is a named range of holiday
dates, and the array {2,3,4,5,6} should be an array of working days.
--
HTH
RP
(remove nothere from the email address if mailing direct)
If it is NETWORKSDAYS, use
=SUMPRODUCT(INT((E1-WEEKDAY(E1+1-{2;3;4;5;6})-C1+8)/7))-SUMPRODUCT(ISNUMBER(
MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=C1)*(holidays<=E1))
where C1 and E1 hold the 2 dates, hiolidays is a named range of holiday
dates, and the array {2,3,4,5,6} should be an array of working days.
--
HTH
RP
(remove nothere from the email address if mailing direct)
R
Ron Rosenfeld
In what, the WORKDAY function, NETWORKSDAYS?
If it is NETWORKSDAYS, use
=SUMPRODUCT(INT((E1-WEEKDAY(E1+1-{2;3;4;5;6})-C1+8)/7))-SUMPRODUCT(ISNUMBER(
MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=C1)*(holidays<=E1))
where C1 and E1 hold the 2 dates, hiolidays is a named range of holiday
dates, and the array {2,3,4,5,6} should be an array of working days.
Bob,
In doing some testing of a VB routine for Workdays and Networkdays, allowing
variable weekend days, I note that your routine seems to have inaccuracies
under circumstances of negative Networkdays.
For example, given your formula and:
C1: 20 Dec 2004
E1: 07 Dec 2004
Networkdays = -10
Your formula = -8
--ron
B
Bob Phillips
Hi Ron,
Good spot. I add 1 to the date in E1 and therein lies the problem. If E1 >
C1 I can add 1, else I need to subtract 1, to maintain the integrity.
Revised version
=SUMPRODUCT(INT((E1-WEEKDAY(E1+IF(E1>C1,1,-1)-{2;3;4;5;6})-C1+8)/7))-SUMPROD
UCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=C1)*(holiday
s<=E1))
--
HTH
RP
(remove nothere from the email address if mailing direct)
Good spot. I add 1 to the date in E1 and therein lies the problem. If E1 >
C1 I can add 1, else I need to subtract 1, to maintain the integrity.
Revised version
=SUMPRODUCT(INT((E1-WEEKDAY(E1+IF(E1>C1,1,-1)-{2;3;4;5;6})-C1+8)/7))-SUMPROD
UCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=C1)*(holiday
s<=E1))
--
HTH
RP
(remove nothere from the email address if mailing direct)
R
Ron Rosenfeld
Hi Ron,
Good spot. I add 1 to the date in E1 and therein lies the problem. If E1 >
C1 I can add 1, else I need to subtract 1, to maintain the integrity.
Revised version
=SUMPRODUCT(INT((E1-WEEKDAY(E1+IF(E1>C1,1,-1)-{2;3;4;5;6})-C1+8)/7))-SUMPROD
UCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=C1)*(holiday
s<=E1))
--
Well, I can still get it to screw up. For example:
StartDate Saturday, January 01, 2005
EndDate Thursday, December 16, 2004
Holidays
Friday, December 24, 2004
Friday, December 24, 2004
Friday, December 31, 2004
Saturday, January 01, 2005
Sunday, January 02, 2005
Your Formula: -11
NetWorkDays : -10
================================
I've been working on a VB Wrkday (and NetWrkDay)routine that allows, as
written, a maximum of three defined weekend days. It seems to be giving
answers consistent with Excel's Networkday and Workday function.
Myrna helped with a routine that really sped up the NetWrkday routine compared
with my initial attempt. Of course, it does not run as fast as the native
routines, but for reasonable ranges, it might be adequate.
----------------------------------------------
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
' Sunday = 1; Monday = 2; ... Saturday = 7
'credits to Myrna
Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean
DoHolidays = Not (Holidays Is Nothing)
SD = StartDate: ED = EndDate
If SD > ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If
w = Weekday(SD - 1)
For i = SD To ED
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
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Date
' Sunday = 1; Monday = 2; ... Saturday = 7
Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date
Stp = Sgn(NumDays)
'Add NumDays
TempDate = StartDate + NumDays
'Add Non-Workdays
Do While Abs(NumDays) <> Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)
Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop
WrkDay = TempDate
End Function
R
Ron Rosenfeld
Well, I can still get it to screw up. For example:
StartDate Saturday, January 01, 2005
EndDate Thursday, December 16, 2004
Holidays
Friday, December 24, 2004
Friday, December 24, 2004
Friday, December 31, 2004
Saturday, January 01, 2005
Sunday, January 02, 2005
Your Formula: -11
NetWorkDays : -10
================================
I've been working on a VB Wrkday (and NetWrkDay)routine that allows, as
written, a maximum of three defined weekend days. It seems to be giving
answers consistent with Excel's Networkday and Workday function.
Myrna helped with a routine that really sped up the NetWrkday routine compared
with my initial attempt. Of course, it does not run as fast as the native
routines, but for reasonable ranges, it might be adequate.
----------------------------------------------
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
' Sunday = 1; Monday = 2; ... Saturday = 7
'credits to Myrna
Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean
DoHolidays = Not (Holidays Is Nothing)
SD = StartDate: ED = EndDate
If SD > ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If
w = Weekday(SD - 1)
For i = SD To ED
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
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Date
' Sunday = 1; Monday = 2; ... Saturday = 7
Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date
Stp = Sgn(NumDays)
'Add NumDays
TempDate = StartDate + NumDays
'Add Non-Workdays
Do While Abs(NumDays) <> Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)
Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop
WrkDay = TempDate
End Function
Hmmm, it seems to work fine for normal weekends. It doesn't seem to work
properly for Fri/Sat weekends and negative Networkdays; although the Workdays
seems to calculate OK.
I'm not going to work on it anymore tonight.
--ron
B
Bob Phillips
It will have problems with the holidays as well as the startdate is after
the enddate. I must admit I have never liked the fact that NETWORKDAYS
returns negatives, so here is another version. It doesn't return the same as
NETWORKDAYS because it doesn't return negative number of days, so it is
agnostic as to whether C1 is the start or end date, which is how it should
be IMO. If a negative sign is wanted, it could be added.
=SUMPRODUCT(INT((MAX(C1,E1)-WEEKDAY(MAX(C1,E1)+1-{2;3;4;5;6})-MIN(C1,E1)+8)/
7))-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=M
IN(C1,E1))*(holidays<=MAX(C1,E1)))
--
HTH
RP
(remove nothere from the email address if mailing direct)
the enddate. I must admit I have never liked the fact that NETWORKDAYS
returns negatives, so here is another version. It doesn't return the same as
NETWORKDAYS because it doesn't return negative number of days, so it is
agnostic as to whether C1 is the start or end date, which is how it should
be IMO. If a negative sign is wanted, it could be added.
=SUMPRODUCT(INT((MAX(C1,E1)-WEEKDAY(MAX(C1,E1)+1-{2;3;4;5;6})-MIN(C1,E1)+8)/
7))-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=M
IN(C1,E1))*(holidays<=MAX(C1,E1)))
--
HTH
RP
(remove nothere from the email address if mailing direct)
R
Ron Rosenfeld
Hmmm, it seems to work fine for normal weekends. It doesn't seem to work
properly for Fri/Sat weekends and negative Networkdays; although the Workdays
seems to calculate OK.
I'm not going to work on it anymore tonight.
--ron
Well, another Hmmm. Now with more sleep. It seems my debugging method was
flawed and that my routines do work properly, at least for Sat-Sun and/or
Mon-Tue.
So I guess I'm satisfied at this point, although further testing would be a
good idea, too.
--ron
R
Ron Rosenfeld
It will have problems with the holidays as well as the startdate is after
the enddate. I must admit I have never liked the fact that NETWORKDAYS
returns negatives, so here is another version. It doesn't return the same as
NETWORKDAYS because it doesn't return negative number of days, so it is
agnostic as to whether C1 is the start or end date, which is how it should
be IMO. If a negative sign is wanted, it could be added.
=SUMPRODUCT(INT((MAX(C1,E1)-WEEKDAY(MAX(C1,E1)+1-{2;3;4;5;6})-MIN(C1,E1)+8)/
7))-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=M
IN(C1,E1))*(holidays<=MAX(C1,E1)))
Well, I agree with you about NetWorkDays returning negatives, but I chose to do
it that way in order to mimic the Excel function; figuring that if one did not
like it, one could use the ABS function.
By the way, I believe my VBA routines work properly. My problem last night, in
debugging, turned out to be an improper model for the debugging process.
I have yet to check it with three weekend days, and with non-contiguous
"weekend days", however.
So far as your routine is considered, there still seems to be some
inconsistencies:
Given the following Data:
StartDate Monday, January 03, 2005
EndDate Thursday, December 23, 2004
Holidays
Friday, December 24, 2004
Friday, December 24, 2004
Friday, December 31, 2004
Saturday, January 01, 2005
Sunday, January 02, 2005
Your formula returns 5 for an Enddate of either December 27, 2004 or December
23, 2004.
Networkdays (and my UDF) return -6 or -5 respectively.
--ron
M
Myrna Larson
IIRC, the OP said that he wants just two weekend days, but they are Friday and
Saturday instead of Saturday and Sunday.
If the start date is in A1 and the end date in B1, and the list of holidays in
a named range Holidays,
=NETWORKDAYS(A1+1,B1+1,Holidays)
will work for this problem. It "tricks" Excel by shifting all dates ahead 1
day of the week, so a "real" Friday is seen as a Saturday by the function (and
hence a day off), and a "real" Sunday is seen as a Monday (and hence a
workday).
Of course you must add 1 to each of the dates in the holiday list, too. That
might be accomplished with a parallel column that adds 1 to the date to the
left. This 2nd column would be used in the calls to the functions involving
workdays.
Or (I haven't tried this), you may be able to use an array formula:
=NETWORKDAYS(A1+1,B1+1,Holidays+1)
Of course if you are dealing with a 4-day workweek, you need VBA or a more
complicated worksheet formula.
Saturday instead of Saturday and Sunday.
If the start date is in A1 and the end date in B1, and the list of holidays in
a named range Holidays,
=NETWORKDAYS(A1+1,B1+1,Holidays)
will work for this problem. It "tricks" Excel by shifting all dates ahead 1
day of the week, so a "real" Friday is seen as a Saturday by the function (and
hence a day off), and a "real" Sunday is seen as a Monday (and hence a
workday).
Of course you must add 1 to each of the dates in the holiday list, too. That
might be accomplished with a parallel column that adds 1 to the date to the
left. This 2nd column would be used in the calls to the functions involving
workdays.
Or (I haven't tried this), you may be able to use an array formula:
=NETWORKDAYS(A1+1,B1+1,Holidays+1)
Of course if you are dealing with a 4-day workweek, you need VBA or a more
complicated worksheet formula.