Thanks:  0
Likes:  0

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

1. ## 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. ## 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. ## 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. ## 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. ## Re: 6 day work week with workday function?????

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

6. ## 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. ## 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
 C2D2C3D3C4D4C5D5C6D6C7D7C8D8C9D9C10D10 =

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

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

#### Posting Permissions

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