Excluding Weekends & Holidays

[Jayker]

Hey Excel'ers,

I am generating a spreadsheet that focuses on a target opening date. There is about 12 other headings along the top all stating the different phases of construction, permitting, etc. I need to exclude weekends and holidays from most of these columns, and only exclude holidays from about 2 of the columns. I think I am to use the formula for NETWORKDAYS, but not sure how or where to incorporate it.

Your help would be greatly appreciated.

Thanks,
Jay

 

[Jayker]

Thankyou very much!
One last question:

What do I use instead of 'workday' if I do want to include the weekend but still exclude holidays?

 

[Jayker]

My last question didn't seem to post, or maybe it will post twice.

Does the sheet have to remain titled 'Sheet 2' ?

What word do I use instead of 'workdays' to include weekends?

 

[barry houdini]

What do I use instead of 'workday' if I do want to include the weekend but still exclude holidays?

Hello Jay,

Which version of Excel are you using?

 

[Jayker]

Hey Barry,

2007 !

 

[barry houdini]

OK, that's a little tricky in Excel 2007 - in Excel 2010 you can use WORKDAY.INTL function to easily exclude holidays but include Saturday and Sunday.....but that function doesn't exist in Excel 2007. You can use this "array formula"

<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; color: rgb(0, 0, 0); line-height: 18px;">=A2-SMALL(IF(COUNTIF(H$2:H$10,A2-ROW(INDIRECT("1:"&B2*5)))=0,ROW(INDIRECT("1:"&B2*5))),B2)</code>

confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

Assumes A2 is a date, B2 is a positive number of days to go back, and H2:H10 contains holiday dates

 

[Jayker]

That doesn't make sense to me, lol. So I just used SUM instead of WORKDAY and it seems to work fine.

This is the formula I have in most of the cells: =WORKDAY(F9,-2,Holidays!C2:C100)
I'm having a problem though that when I copy a full row of forumlas to the next row, it is changing the formula to =WORKDAY(F9,-2,Holidays!C3:C101), then it gives me an error and I have to go through each one and click to fix the error to include C2. Is there any way I can make Holidays!C2:100 a constant so it doesn't change to C3 and C101? when I copy it down to the next row?

Thanks for your time!
Jay

 

[Andrew Poulsom]

Use the $ sign to make the row references absolute when copying - C$2:C$100.