how can i change nonworking date from saturday&sunday to frinday&Saturday

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)


"salim purayil" <salim wrote in message
...
how can i change nonworking date from saturday&sunday to frinday&Saturday

On Mon, 28 Feb 2005 10:08:25 -0000, "Bob Phillips"
wrote:

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

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(E1C1,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)


"Ron Rosenfeld" wrote in message
...
On Mon, 28 Feb 2005 10:08:25 -0000, "Bob Phillips"
wrote:

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

On Mon, 28 Feb 2005 20:30:52 -0000, "Bob Phillips"
wrote:

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(E1C1,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
-------------------------------------


--ron

On Mon, 28 Feb 2005 20:16:23 -0500, Ron Rosenfeld
wrote:

On Mon, 28 Feb 2005 20:30:52 -0000, "Bob Phillips"
wrote:

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(E1C1,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
-------------------------------------


--ron

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