WORKDAY SCHEDULE LESS HOLIDAYS

Tandem_2

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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.

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.

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
ABCDEFG
1Start DateWork DaysEnd DateHolidays
2Thu, 13 Jan 20051Fri, 14 Jan 2005Sun, 01 Jan 1900dummy start date
3Thu, 13 Jan 20052Sat, 15 Jan 2005Mon, 17 Jan 2005Dr. Martin Luther King Day (3rd Monday in January)
4Thu, 13 Jan 20053Tue, 18 Jan 2005Mon, 21 Feb 2005Presidents Day (3rd Monday in February)
5Thu, 13 Jan 20054Wed, 19 Jan 2005Mon, 30 May 2005Memorial Day (last Monday in May)
6Thu, 13 Jan 20055Thu, 20 Jan 2005Mon, 04 Jul 2005Independence Day
7Thu, 13 Jan 20056Fri, 21 Jan 2005Mon, 05 Sep 2005Labor Day (first Monday in September)
8Thu, 13 Jan 20057Sat, 22 Jan 2005Mon, 10 Oct 2005Columbus Day (2nd Monday in October)
9Thu, 13 Jan 20058Mon, 24 Jan 2005Fri, 11 Nov 2005Veterans Day
10Thu, 13 Jan 20059Tue, 25 Jan 2005Thu, 24 Nov 2005Thanksgiving Day (4th Thursday in November)
11Fri, 25 Nov 2005Thanksgiving Friday (day after Thanksgiving)
12Mon, 26 Dec 2005Christmas Day (observed)
13Tue, 22 Nov 20051Wed, 23 Nov 2005
14Tue, 22 Nov 20052Sat, 26 Nov 2005
15Tue, 22 Nov 20053Mon, 28 Nov 2005
16Tue, 22 Nov 20054Tue, 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.

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

Replies
4
Views
376
Replies
3
Views
363
Replies
4
Views
336
Replies
5
Views
167
Replies
34
Views
1K

1,206,711
Messages
6,074,465
Members
446,071
Latest member
gaborfreeman

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?

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

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