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 :banghead:
 

Some videos you may like

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
Joined
Aug 1, 2002
Messages
150
Could you explain a bit about the problem you're trying to solve? Perhaps the WORKDAY function isn't appropriate here.
 

bigfish311

New Member
Joined
Nov 18, 2003
Messages
23
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
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Nov 18, 2003
Messages
23
yeah, no sunday's and have the holidays set in
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
Joined
Jan 15, 2003
Messages
564
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top