change nonworking day to friday

[salim purayil]

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

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


"salim purayil" <salim [email protected]> wrote in message
news:[email protected]...
> how can i change nonworking date from saturday&sunday to frinday&Saturday

[Ron Rosenfeld]

On Mon, 28 Feb 2005 10:08:25 -0000, "Bob Phillips"
<[email protected]> 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

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


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 28 Feb 2005 10:08:25 -0000, "Bob Phillips"
> <[email protected]> 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

[Ron Rosenfeld]

On Mon, 28 Feb 2005 20:30:52 -0000, "Bob Phillips"
<[email protected]> 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(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
-------------------------------------


--ron

[Ron Rosenfeld]

On Mon, 28 Feb 2005 20:16:23 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Mon, 28 Feb 2005 20:30:52 -0000, "Bob Phillips"
><[email protected]> 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(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
>-------------------------------------
>
>
>--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

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


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 28 Feb 2005 20:16:23 -0500, Ron Rosenfeld
<[email protected]>
> wrote:
>
> >On Mon, 28 Feb 2005 20:30:52 -0000, "Bob Phillips"
> ><[email protected]> 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(E1>C1,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

[Ron Rosenfeld]

On Mon, 28 Feb 2005 22:26:01 -0500, Ron Rosenfeld <[email protected]>
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

[Ron Rosenfeld]

On Tue, 1 Mar 2005 11:03:43 -0000, "Bob Phillips"
<[email protected]> 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

[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.



On Tue, 1 Mar 2005 11:03:43 -0000, "Bob Phillips"
<[email protected]> 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)))

[samlion27]

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 [email protected]> wrote in message
news:[email protected]...
> how can i change nonworking date from saturday&sunday to frinday&Saturday

If i'm using the WORKDAY Function, how to change the weekends to Friday only?

[DonkeyOte]

samlion,

Your post does not comply with Rule 2 of our Forum RULES.

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.