Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Networking days

Status
Not open for further replies.

Cpreston

MIS
Mar 4, 2015
972
0
16
GB
Hi

I have a long list of dates in 2 columns and want to calculate the days between them but exclude weekends and bank holidays.

I have used =NETWORKDAYS(F2,G2,P2:p10) The P2:p10 have the bank holiday dates in but when I copy the formula down it changes for example =NETWORKDAYS(F3,G3,P3:p11) so that takes out the range where the bank holiday dates are kept. Any ideas please.

Thanks
 
You could also use Excel's named range:

NamedRange_rysjwt.png


then you formula would be:
[tt]=NETWORKDAYS(F2,G2,[blue]MyHolidays[/blue])[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Was any of this helpful?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
I think it's most helpfully intuitive when List Headers and Table Headers (really one-in-the-same functionally) that those name correspond directly to Range Names, except for the fact that Range Names cannot contain SPACES but rather UNDERSCORES.

With that said, there's a feature in Formulas that enables you to name a Range using the Name at one of these 4 positions relative to the Selected Range: Top row, Bottom row, Left column or Right column.

Formulas > Create from Selection > Create names from values in the: [Top row, Bottom row, Left column or Right column]

In Andys example, the Range Name would simply be Holidays.

More or less a one step process with respect to typing a name.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top