Excel - Remove holidays from calculation

Asked By Sal on 27-Dec-09 09:44 PM
=IF(COUNT(K13:L13)<>2,"",NETWORKDAYS(K13,L13)-1)

This formula already excludes weekends from the calculation.

Do you know how I could improve this formula so that it does not include
holidays in the calculation, I can specify the holidays, and I will not get
an error message if Column K is blank?

Or

Do you know how I can improve this formula so that if Column K is blank I
will not get a #VALUE! message where the calculation should be?

=NETWORKDAYS(K13,L14,Holidays!A1:A29)-1




Rick Rothstein replied to Sal on 27-Dec-09 10:13 PM
Someone did not read the help files for the NETWORKDAYS function...

.....
.....
Holidays is an optional range of one or more dates to
exclude from the working calendar, such as state and
federal holidays and floating holidays. The list can be
either a range of cells that contains the dates or an
array constant of the serial numbers that represent the
dates."

--
Rick (MVP - Excel)
ryguy7272 replied to Sal on 27-Dec-09 10:40 PM
If text is not a number, the Value function will return #VALUE!
Look here for detailed description:
http://www.techonthenet.com/excel/formulas/value.php


I could not reproduce the #VALUE! scenario that you described, but maybe some
of those cells are formatted as text?  Try to convert (select > right-click >
format cells > date) them all to values and see what happens.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
Rick Rothstein replied to Rick Rothstein on 27-Dec-09 10:56 PM
Someone did not read the OP's complete message and that person is... me!
Sorry, I did not see your whole message (mainly because I did not scroll down
to the bottom of it).

--
Rick (MVP - Excel)
Rick Rothstein replied to Sal on 27-Dec-09 11:00 PM
How about this...

=IF(OR(K13="",L14=""),"",NETWORKDAYS(K13,L14,Holidays!A1:A29)-1)

--
Rick (MVP - Excel)
Sal replied to ryguy7272 on 27-Dec-09 11:16 PM
Hello.  Thank you for the tip.  I tried out the conversion suggestions and I
got the same result.

I think the problem might be due to the fact that another formula resides in
Column K that will only pull data into the cell if certain conditions are
met.  Since I am only having problems with those cells that are blank I am
thinking this is the cause of the problem.

This also might be why you cannot reproduce the problem  That being said, do
you know how else I might be able to resolve the problem I am having?
Sal replied to Rick Rothstein on 27-Dec-09 11:20 PM
Hi Rick.  Thank you for the input.  I am looking to reference a list of
holidays from a range that contains the dates in a worksheet called Holidays.
I am not sure I completely understand.

Are you recommending to include NETWORKDAYS(start_date,end_date,holidays)
somewhere in this formula?

=NETWORKDAYS(K13,L14,Holidays!A1:A29)-1
ryguy7272 replied to Sal on 27-Dec-09 11:36 PM
I am still not sure what the problem is here.  Click the little question mark
symbol in the Main Excel view.  Type 'NETWORKDAYS' and yo will see a good
example of this this function works.  Copy/paste the example into a sheet
(fills cells A1:C7), and enter this function into cell E3;
=NETWORKDAYS(B3,B4,B5:B7)

What result do you get?  108?  That is correct.  Look at the logic.

I renamed a sheet to Holidays and entered =TODAY() in Cell A1.  in A2 I put
=A1+1 then filled down until A29.  now, back to the first sheet, enter this
in cell E3:  =NETWORKDAYS(B3,B4,Holidays!A1:A29)

What result do you get?  You should get 88.  try it.  Try to adopt that for
your specific example.  Post back with any additional, specific, questions.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
Sal replied to ryguy7272 on 28-Dec-09 10:06 PM
Wow this is great.  I think the problem I am having might have been specific
to the worksheet.  Thank you a lot for your help with this.  I appreciate it
very much.
Sal replied to Rick Rothstein on 28-Dec-09 10:07 PM
Thank you Rick.  This works well.  I think you figured it out.  I am thankful
for your help.  Thank you!