Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: 6 day work week with workday function?????

  1. #1
    New Member
    Join Date
    Nov 2003
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 6 day work week with workday function?????

    If I'm using the workday function in excel and want to put the activities onto a 6 day workweek, how would I go about doing that?

    Thanks

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 6 day work week with workday function?????

    Could you explain a bit about the problem you're trying to solve? Perhaps the WORKDAY function isn't appropriate here.

  3. #3
    New Member
    Join Date
    Nov 2003
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 6 day work week with workday function?????

    I have a schedule of activities that have durations set to each and it is currently on a 5 day a week calendar but I would the option to see the effect if it was on a 6 day a week calendar.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 6 day work week with workday function?????

    This would take a pretty extensive formula. In order for someone to go down that road, it would be helpful to know:
    1) What six days? (ie no Sunday)
    2) Are you using the holidays argument?

  5. #5
    New Member
    Join Date
    Nov 2003
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 6 day work week with workday function?????

    yeah, no sunday's and have the holidays set in

  6. #6
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 6 day work week with workday function?????

    The following formula from Harlan Grove (Microsoft Excel MVP) mimics Excel’s Networkdays function:

    =SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(A2&":"&B2)))=0),--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),3)<6))

    Where:
    A2: 1 November 03 (start date)
    B2: 31 March 04 (end date)

    “Holidays” is a named range that covers holidays for the year.

    The above formula returns an identical result to:

    =NETWORKDAYS(A2,B2,Holidays) but includes Saturdays and excludes Sundays.

    Look at the last part of Mr. Grove’s formula:

    WEEKDAY(……),3)<6))

    From Excel’s Help file:

    WEEKDAY(serial_number,return_type)

    When the return type is 3 (as per Mr. Grove’s formula), the Weekday functions returns 0 (Monday) through 6 (Sunday). So, to include Saturday and exclude Sunday (days 5 and 6), the formula has “<6”.

    To include Saturdays and Sundays, change the formula to:

    WEEKDAY(……),3)<7))

    Works for me on limited test data.

    HTH

    Mike

  7. #7
    Board Regular
    Join Date
    Jan 2003
    Location
    Round Rock, Texas
    Posts
    564
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 6 day work week with workday function?????

    As an alternative to Ekim's (and indirectly, Harlan Grove's) formulation, perhaps the following:

    ******** ******************** ************************************************************************>
    Microsoft Excel - MrEC153.xls___Running: xl97 : OS = Windows NT 4
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    1
    StartStop"Workdays""Workdays" Holidays
    2
    12-Nov-0319-Dec-033131 01-Jan-03
    3
    17-Mar-0327-Sep-03164164 18-Apr-03
    4
    01-Jan-0311-Dec-03289289 26-May-03
    5
    01-Sep-0317-Oct-034040 04-Jul-03
    6
    03-Apr-0316-Aug-03114114 01-Sep-03
    7
    23-Mar-0307-Nov-03193193 27-Nov-03
    8
    15-Sep-0331-Dec-039090 28-Nov-03
    9
    27-Feb-0328-May-037676 25-Dec-03
    10
    01-Nov-0331-Mar-04126126 01-Jan-04
    Sheet3

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in C2 is:

    =NETWORKDAYS(A2,B2,Holidays)+INT(B2/7)-INT(A2/7)+INT(WEEKDAY(A2)/7)

    while the formula in D2 is as posted in the reply immediately above.

    I offer this alternative, since recently some experts on this board seem to be cautioning against the overuse of array formulations, and because I have long championed this alternative formulation. In short, the simplest, non-array oriented formula for counting the number of a particular weekday between any 2 dates (inclusive) is:

    =INT((End-Wkday)/7)-INT((Start-Wkday)/7)+INT(WEEKDAY(Start-Wkday)/7)

    where Start is the starting date of interest, End is the ending date, and Wkday = 1,2,3,4,5,6, or 7, for Sunday through Saturday, respectively. Note that Wkday can be omitted for Saturday, so that the formula simplifies to:

    =INT(Stop/7)-INT(Start/7)+INT(WEEKDAY(Start)/7)

    when you want to count the number of Saturdays between any 2 dates. Note that either formula is considerably faster than alternative expressions that return the same answer, such as:

    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(Start&":"&End)))=Wkday))

    when dealing with large numbers of calculations. See also:

    http://www.mrexcel.com/board2/viewtopic.php?t=61643
    http://www.mrexcel.com/board2/viewtopic.php?t=63010
    http://www.mrexcel.com/board2/viewtopic.php?t=67153

    --Tom McClain

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com