Formula - Excluding weekends & holidays
 

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

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

--
Regards
Roger Govier
"Connie Martin" wrote in message
...
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

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)


"Connie Martin" wrote in message
...
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

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" wrote:

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

--
Regards
Roger Govier
"Connie Martin" wrote in message
...
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

Hi Connie

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



--
Regards
Roger Govier
"Connie Martin" wrote in message
...
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

=NETWORKDAYS(K42.N42,Holidays)

--

Regards,

Peo Sjoblom


"Connie Martin" wrote in message
...
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" wrote:

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

--
Regards
Roger Govier
"Connie Martin" wrote in
message
...
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

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.


"Peo Sjoblom" wrote:

=NETWORKDAYS(K42.N42,Holidays)

--

Regards,

Peo Sjoblom


"Connie Martin" wrote in message
...
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" wrote:

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

--
Regards
Roger Govier
"Connie Martin" wrote in
message
...
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

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!


"Roger Govier" wrote:

Hi Connie

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



--
Regards
Roger Govier
"Connie Martin" wrote in message
...
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

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

On Thu, 24 Feb 2005 08:23:06 -0800, "Connie Martin"
wrote:

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

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)

On Thu, 24 Feb 2005 10:19:01 -0800, "Connie Martin"
wrote:

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.


"Peo Sjoblom" wrote:

=NETWORKDAYS(K42.N42,Holidays)

--

Regards,

Peo Sjoblom


"Connie Martin" wrote in message
...
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" wrote:

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

--
Regards
Roger Govier
"Connie Martin" wrote in
message
...
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