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

bigfish311

New Member
Joined
Nov 18, 2003
Messages
23
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 :oops:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Could you explain a bit about the problem you're trying to solve? Perhaps the WORKDAY function isn't appropriate here.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
As an alternative to Ekim's (and indirectly, Harlan Grove's) formulation, perhaps the following:
MrEC153.xls
ABCDEF
1StartStop"Workdays""Workdays"Holidays
212-Nov-0319-Dec-03313101-Jan-03
317-Mar-0327-Sep-0316416418-Apr-03
401-Jan-0311-Dec-0328928926-May-03
501-Sep-0317-Oct-03404004-Jul-03
603-Apr-0316-Aug-0311411401-Sep-03
723-Mar-0307-Nov-0319319327-Nov-03
815-Sep-0331-Dec-03909028-Nov-03
927-Feb-0328-May-03767625-Dec-03
1001-Nov-0331-Mar-0412612601-Jan-04
Sheet3


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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top