Include Saturdays and maybe Sundays?

**lil_ern63** · 05-20-2008, 05:12 PM

Previously posted by User Madball,

"Hi,

Okay currently I'm working as project manage for a software development studio and I am using MS project for all my work. Since the development team aren't aloud to have a MS project license on their PC I have to copy their tasks from MS project and put them into Excel.

I would say that I am okay using Excel but not an expert. I want to create a formula that uses NETWORKDAYS to determines a finish date from a duration and start date.

The headers I would use for each column are Duration, Start and Finish. So I would like to be able to get a team member to put a duration down i.e 1 day and give me the start day and in the finish date it produces a finish date automatically without including Bank Holidays and Weekends. What would the formula be for that?

Any help will be great

Thanks "

I have the same question as Madball and was able to work out the Worday function, however for my company, we work on Saturdays and or Sundays sometimes.

I would like to be able to specify from line to line if Saturday or Sunday needs to be included in the duration calculation resulting in a finish date, however if I can simply get one formula to work that includes Saturdays alone (most common working day of the weekend, and do work more than don't) it would be the most helpful, I think I can get it adjusted from there.

Thank you.

**daddylonglegs** · 05-20-2008, 06:34 PM

Perhaps the simplest way to do this would be to have a list of all days that you don't work, e.g. include in the list all holidays and those Sundays (or Saturdays) that you don't work. This list should be a single column. Name it daysoff and then use the following formula

=SMALL(IF(ISNA(MATCH(A2+ROW(INDIRECT("1:"&10*B2)),daysoff,0)),A2+ROW(INDIRECT("1:"&10*B2))),B2)

confirmed with CTRL+SHIFT+ENTER

where A2 is the start date and B2 the number of working days to add, B2 should be a positive number

**lil_ern63** · 05-20-2008, 07:25 PM

Thank you for the response, and i will definitly give it a try. I was able to find another thread with someone having a similar problem, maybe excluding the need to randomly decide if a saturday or sunday would be worked, but the responder to their question came up with the following:

=IF(WEEKDAY(G9,1)=6,WORKDAY(G9,1)-2,WORKDAY(G9,1))

I have not been able to get this formula to work off the bat, and in fact can't even follow the logic of the function. The responder says that G( should be the starting date, and I do not see any reference to the duration.

Is this a function path that might be usuable?

Once again thank you all for any help you bring.

**daddylonglegs** · 05-20-2008, 07:39 PM

Originally Posted by lil_ern63

Is this a function path that might be usuable?

The short answer is "No"

Slightly longer answer....

That formula just returns Saturday if G9 is a Friday or the next workday if G9 is any other day of the week, as you say, it doesn't specify how many days to add.

If you want the flexibility to choose which days are workdays (i.e. some Sundays are, some Sunday aren't) then I think you'd need a formula like the one I proposed.

I could supply a formula which would include all days but Sundays, i.e.

=A2-WEEKDAY(A2,3)+INT(7/6*(B2+MIN(5,WEEKDAY(A2,3))))

again where A2 is the start date abd B2 days to add.....although this doesn't exclude holidays, do you need to do that too?

**lil_ern63** · 05-20-2008, 08:12 PM

I do need to include (exclude from the duration, if that makes sense) holidays for certain. If I have to include ALL Saturdays and exclude ALL Sundays, that is alright, as we typically do work Saturdays.

I was hoping to be able to specify if the Saturday or Sunday was "on" or not, but it was more of a bells and whistle to add to the end.

If we do add ALL Saturdays, and also exclude holidays, would it be feasible, or easier to simply create a condition where if there are saturdays within the duration that were to not be worked, they are added to a list called holidays?

Sorry for so many questions, I don't want it to seem you are flat out doing my work, but I have been against a mental block on this for quite awhile.

As always thanks for your help.

**daddylonglegs** · 05-21-2008, 04:53 AM

This formula will exclude all Sundays and any dates in named range holidays. There's no reason why you can't include non-working Saturdays in the holiday list.....

=SMALL(IF(ISNA(MATCH(A2+ ROW(INDIRECT("1:"&10*B2)), holidays,0)),IF(WEEKDAY(A2+ ROW(INDIRECT("1:"&10*B2)))<>1,A2+ ROW(INDIRECT("1:"&10*B2)))),B2)

confirmed with CTRL+SHIFT+ENTER

**lil_ern63** · 05-21-2008, 10:16 PM

Formula worked great! And with the three equations you have given me, it has sparked a few more ideas. Thanks for all your help, true showing of why this forum works so well!

**lil_ern63** · 05-22-2008, 05:36 PM

Well I am back again! The formula worked great with one exception. If you input a duration of 1 day, say starting on Monday, the result/finish date is a Tuesday and I need it to finish on Monday (the same day).

I played with the formula and tried the (duration-1) but an error comes up when 1 days is specified, leaving a duration of 0 days. I have tried to disect the formula, but am unfamiliear with INDIRECT and ROW. If I watch the function computer, Excel always crashes!

I appreciate all you have done, but is there a way to use the last posted formula and return a finish date where the first day is included in the duration (i.e. duration of 1 day ends on the same day as it starts)?

Thank you.

**daddylonglegs** · 05-22-2008, 05:57 PM

You can add in a few -1s, i.e.

=SMALL(IF(ISNA(MATCH(A2+ ROW(INDIRECT("1:"&10*B2))-1, holidays,0)),IF(WEEKDAY(A2+ ROW(INDIRECT("1:"&10*B2))-1)<>1,A2+ ROW(INDIRECT("1:"&10*B2))-1)),B2)

Then if A2 was today's date (a Thursday) and B2 was 1 the formula returns the same date as A2. Note that if A2 was a Sunday or holiday then with B2 as 1 it would return the next workday

**lil_ern63** · 05-23-2008, 08:51 PM

The formulas given above have been working great! Thank you for all your help!

I have been asked to make a few modifications/additions to help with the functionality of the sheet. One of the additions is to add predecessors to each task. In doing this you can specify when a task is to begin, however it is also dependent on the finish date or start date of the referenced task to determine when current task finishes/ends.

Example:

Task #1 starts on 5/20, and has a duration of 3 days, therefore ending on 5/22. Using the formulas above I have been able to get this to work perfectly! Now comes the "addition/modification"

Task #2 is to begin on 5/20 as well, however with predecessors of "1 finish-start+2", Task #2 is to start 2 days after the finish of task 1.

This is something I have been able to achieve as well. The predecessors can be specified as one of the four following:

Finish-Start+# (Task starts after/before the finish of the other task depending on the days following, #, which can be negative or positive)

Start-Start+# (Task starts after/before the start of the other task depending on the days following, #, which can be negative or postive)

Finish-Finish+# (Task finishes after/before the finish of the other task depending on the days following, #, which can be negative or positive)

Start-Finish+# (Task finishes after/before the start of the other task depending on the days following, #, which can be negative or positive)

As I have mentioned, having something start AFTER the start or finish of the referenced task is doable, as long as # is POSITIVE. I come up with errors if # is negative.

The other problem is beign able to specify when an object is supposed to finish, and be able to work backwards to formulate the start, all based on duration. Once again these all have to take into account no sundays, or holidays.

This may be confusing, and if it is I understand, as trying to figure it out looking at the sheet directly has become challenging in itself. If posting the file will help, I will be glad to post.

Thank you.

**lil_ern63** · 05-25-2008, 01:46 PM

The formulas given above have been working great! Thank you for all your help!

I have been asked to make a few modifications/additions to help with the functionality of the sheet. One of the additions is to add predecessors to each task. In doing this you can specify when a task is to begin, however it is also dependent on the finish date or start date of the referenced task to determine when current task finishes/ends.

Example:

Task #1 starts on 5/20, and has a duration of 3 days, therefore ending on 5/22. Using the formulas above I have been able to get this to work perfectly! Now comes the "addition/modification"

Task #2 is to begin on 5/20 as well, however with predecessors of "1 finish-start+2", Task #2 is to start 2 days after the finish of task 1.

This is something I have been able to achieve as well. The predecessors can be specified as one of the four following:

Finish-Start+# (Task starts after/before the finish of the other task depending on the days following, #, which can be negative or positive)

Start-Start+# (Task starts after/before the start of the other task depending on the days following, #, which can be negative or postive)

Finish-Finish+# (Task finishes after/before the finish of the other task depending on the days following, #, which can be negative or positive)

Start-Finish+# (Task finishes after/before the start of the other task depending on the days following, #, which can be negative or positive)

As I have mentioned, having something start AFTER the start or finish of the referenced task is doable, as long as # is POSITIVE. I come up with errors if # is negative.

The other problem is beign able to specify when an object is supposed to finish, and be able to work backwards to formulate the start, all based on duration. Once again these all have to take into account no sundays, or holidays.

This may be confusing, and if it is I understand, as trying to figure it out looking at the sheet directly has become challenging in itself. If posting the file will help, I will be glad to post.

Thank you.

**lil_ern63** · 05-28-2008, 02:23 PM

Attached is a copy of the schedule in working condition. (working as it is now). Above a few requests were made with regard to the attached sheet. I am looking to be able to include a negative number in column R as well as include the start date for Finish/Finish in column K and Start/Finish in column L.

Ideally when all is said and done, the only information that needs to be inputted will be in column F,H, and O-R with the remaining dates calculating.

Any help will be appreciated.

**lil_ern63** · 06-12-2008, 11:38 AM

For all of those that have helped, daddylonglegs especially, I have managed to "finish" the scheduling project at hand. I have noticed quite a few people have viewed the thread as well as through my searching for others with my same questions online I have found quite a few experiencing my same issues.

Not that my goals and project is anything anyone else would potentially use, I have still posted what is CURRENTLY my finished Scheduling Spreadsheet. I say current as it is ever changing.

If anyone views, uses, and find errors or general comments and suggestions, please reply with your comments.

Thank you!