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

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)


"Ron Rosenfeld" wrote in message
...
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))-SUMPR
OD

UCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6} ,0))*(holidays=C1)*(holid
ay
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

On Mon, 28 Feb 2005 22:26:01 -0500, Ron Rosenfeld
wrote:

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

On Tue, 1 Mar 2005 11:03:43 -0000, "Bob Phillips"
wrote:

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

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.



On Tue, 1 Mar 2005 11:03:43 -0000, "Bob Phillips"
wrote:

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)))