Formula - Excluding weekends & holidays

[Guest]

I have this very simple formula: =N42-K42 Both N42 and K42 are dates.

QUESTION #1
Is there a way to incorporate into this formula something that would exclude
weekends when it calculates the number of days' difference?

QUESTION #2
Is there also a way to incorporate into this formula something that would
exclude statutory holidays when it calulcates the difference? I assume I
would need to put a list of statutory holidays somewhere in the worksheet or
on another worksheet.

Thank you
Connie Martin

 

[Roger Govier]

Hi Connie

Try =NETWORKDAYS(startdate,enddate,holidays)
Holidays would be a named range containg a list of the public holidays.
Stardate and enddate the cell references to your dates.
I believe that NETWORKDAYS requires you to have the Analysis Toolpack
loaded.
Go to Tools=>Add-ins and select Analysis Toolpack followed by OK

 

[Bob Phillips]

Look at NETWORKDAYS function in help, it can handle both situations you
describe. It is part of the Analysis Toolpak, so you would need to install
that.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Okay, I can do the NETWORKDAYS formula, but how do I include it in my formula
=N42-K42? I want the difference between those two days excluding weekends
and holidays. I have created a named list and so the NETWORKDAYS formula of:
=NETWORKDAYS("01/01/2004","12/31/2004",NonWorkDays) works on its own, giving
me 252 but...I need an answer of 2 because K42 is Jan. 6/04 and N42 is Jan.
8/04. There is no weekend and no holiday between those two dates. However,
if I have Jan. 9/04 and Jan. 13/04, the formula should yield 2, as well
because there's a weekend in there. (I'm revising a huge report from last
year---that's why I've got 2004 dates here.)

 

[Roger Govier]

Hi Connie

So what result does it give you if you enter
=NETWORKDAYS(K42,N42,Non Work Days)

 

[Peo Sjoblom]

=NETWORKDAYS(K42.N42,Holidays)

 

[Guest]

Thank you. With the formula slightly modified, I get the answer I need:
=NETWORKDAYS(K42,N42,NonWorkDays)-1 I am taking one day off because I don't
want to include the departure day, which is the first date.

 

[Guest]

If I modify the formula slightly, I get the answer I need:
=NETWORKDAYS(K42,N42,NonWorkDays)-1 I am taking one day off because I don't
want to include the departure day, which is the first date, which is the same
answer N42-K42 will give me. Thank you very much!

 

[Gord Dibben]

Connie

NETWORKDAYS Function.

Returns the number of whole working days between start_date and end_date.
Working days exclude weekends and any dates identified in holidays. Use
NETWORKDAYS to calculate employee benefits that accrue based on the number of
days worked during a specific term.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

See Help for formula examples.


Gord Dibben Excel MVP

 

[Myrna Larson]

I think a better way to do that is to modify the departure date, i.e. write
the formula as =NETWORKDAYS(K42+1,N42,NonWorkDays)