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

bigfish311

New Member
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?

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Scottcmu

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

bigfish311

New Member
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.

IML

MrExcel MVP
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?

bigfish311

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

Ekim

Well-known Member
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

rrdonutz

Well-known Member
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))

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

1,102,150
Messages
5,485,052
Members
407,479
Latest member
jbone2020

This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...