WORKDAY SCHEDULE LESS HOLIDAYS

[Tandem_2]

We are using the WORKDAY formula to create a construction schedule returning dates for each task.

Start Date 12/22/05 (Keyed Date) Cell B1
Slab Date =workday(B1,2,Holidays)
Formula Returns 12/27/05 Cell B2
Inspection =workday(B2,1,Holidays)
Formula Returns 12/28/05 Cell B3

This is perfect...EXCEPT...now we want to use a 6 day work week. How can this formula be modified to acknowledge Saturday as a working day?

Appreciate the help!!!

 

[Tandem_2]

6 day Work Week

OK...I have noticed that a number of people have viewed this question but no one has replied.

Have I worded the question properly? Or, is there just not a formula or an easy way to extend the work week to include Saturday and return work day dates accordingly?

If I need to better explain the question....please let me know.

Thanks again.

 

[NBVC]

Check out this thread. It may help you.

http://www.mrexcel.com/board2/viewtopic.php?t=146713&highlight=

 

[2rrs]

also check here!!

http://www.mrexcel.com/board2/viewtopic.php?t=72395

 

[Tandem_2]

Schedule Generator using 6 Day Work Week excluding Holidays

Thanks for the replys. I had looked at the TOPIC referenced below, but it did not address holidays.

http://www.mrexcel.com/board2/viewtopic.php?t=146713&highlight=

The second posting dealt with Holidays but it calculated NET DAYS...thus it delivers a count.

http://www.mrexcel.com/board2/viewtopic.php?t=72395

BUT, my problem is HOW to convert this into a schedule that returns dates....like the Workday function does. Thus you start with a given date, provide a number of days for task completion, which is then added to the prior date excluding ONLY Sundays and Holidays as work days.

I can't seem to figure out how to make a SCHEDULE work.

 

[Ronald Moore]

I believe the following will work for you, although I have no experience with the Workday function so some adjustment may be needed. I'm assuming you want to skip all Sundays and all holidays as possible End dates, but allow Saturdays. The example below shows the behavior of the formula on intervals containing holidays.

workdays.xls
	A	B	C	D	E	F	G
1	Start Date	Work Days	End Date		Holidays
2	Thu, 13 Jan 2005	1	Fri, 14 Jan 2005		Sun, 01 Jan 1900		dummy start date
3	Thu, 13 Jan 2005	2	Sat, 15 Jan 2005		Mon, 17 Jan 2005		Dr. Martin Luther King Day (3rd Monday in January)
4	Thu, 13 Jan 2005	3	Tue, 18 Jan 2005		Mon, 21 Feb 2005		Presidents Day (3rd Monday in February)
5	Thu, 13 Jan 2005	4	Wed, 19 Jan 2005		Mon, 30 May 2005		Memorial Day (last Monday in May)
6	Thu, 13 Jan 2005	5	Thu, 20 Jan 2005		Mon, 04 Jul 2005		Independence Day
7	Thu, 13 Jan 2005	6	Fri, 21 Jan 2005		Mon, 05 Sep 2005		Labor Day (first Monday in September)
8	Thu, 13 Jan 2005	7	Sat, 22 Jan 2005		Mon, 10 Oct 2005		Columbus Day (2nd Monday in October)
9	Thu, 13 Jan 2005	8	Mon, 24 Jan 2005		Fri, 11 Nov 2005		Veterans Day
10	Thu, 13 Jan 2005	9	Tue, 25 Jan 2005		Thu, 24 Nov 2005		Thanksgiving Day (4th Thursday in November)
11					Fri, 25 Nov 2005		Thanksgiving Friday (day after Thanksgiving)
12					Mon, 26 Dec 2005		Christmas Day (observed)
13	Tue, 22 Nov 2005	1	Wed, 23 Nov 2005
14	Tue, 22 Nov 2005	2	Sat, 26 Nov 2005
15	Tue, 22 Nov 2005	3	Mon, 28 Nov 2005
16	Tue, 22 Nov 2005	4	Tue, 29 Nov 2005
17
6 Day Work Week

The array formula (entered with CTRL-SHIFT-ENTER) in C2, and copied down, is:

=A2+MATCH(TRUE,ROW($1:$1000)-INT((ROW($1:$1000)-1+WEEKDAY(A2))/7)-MATCH(A2+ROW($1:$1000),E$2:$E$12,1)+MATCH(A2,E$2:$E$12,1)=B2,0)

Some notes and caveats:

In order for the MATCH usage to work correctly, the Holidays list (E2:E12 in the example) must be sorted in ascending order and a dummy date for the first holiday (smaller than any start date you'll be using) is needed, as I've shown.

The 1000 number is an arbitrary choice -- it must be large enough to cover the number of total days needed in any of your task periods. If you wish to adjust this, do so in all instances of ROW($1:$1000).

If you must allow for possible deletions or insertions of rows above any cell containing the formula, then all instances of ROW($1:$1000) (or whatever you choose) should be replaced by

ROW(INDIRECT("1:1000"))

The formula isn't efficient, but making it so would make it longer, and besides, I've struggled long enough with this as it is.

 

[barry houdini]

Adapting Fairwinds formula from the thread posted above by NVBC

=SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&WORKDAY(A1,B1,holidays))))<>1,IF(ISNA(MATCH(ROW(INDIRECT(A1&":"&WORKDAY(A1,B1,holidays))),holidays,0)),ROW(INDIRECT(A1&":"&WORKDAY(A1,B1,holidays))))),B1+(AND(WEEKDAY(A1)>1,ISNA(MATCH(A1,holidays,0)))))

confirmed with CTRL+SHIFT+ENTER

where A1 contains a date B1 the number of days to be added and holidays is a named range containing holiday dates