Holidays and Networkdays

  • Thread starter Thread starter Kemi
  • Start date Start date
K

Kemi

Hello all,

I would like to incorporate holidays in my networkdays function in
excel. But I want the holidays to be accessible to all my excel
workbooks. How do I write a function in vba that contains all the
holidays in the year, such that excel will recognize it?

Thank you very much for your help.


Kemi
 
Kerri,

Could you not put a list of holidays in the Personal.xls file, and use that
in the formula, such as

=NETWORKDAYS(A2,A1,[PERSONAL.XLS]Sheet1!$A$1:$A$10)

or better with a range name

=NETWORKDAYS(A2,A1,PERSONAL.XLS!holidays)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for replying Bob.

First of all, how do I put the list in the Personal.xls file? An
would this be accessible to all my workbooks? Thanks for you
patience!


Kem
 
Hi Bob,

I put the list of dates in an excel file, named the range as Holiday
and also saved the file as Holidays, i.e. Holidays.xls. But say
forward an excel file that includes the networkdays function to anothe
person who doesn't have the Holidays.xls file would the networkday
function work for that person even though they don't have the Holiday
file? would doing it in VBA overcome that shortcoming?

Thank you so much.

Kem
 
Kemi,

Answer to the second bit first. Yes, it will be, but you need to reference
that workbook as I showed in my original response.

To put the holidays in that file, it first needs to exist. Try this:
1 goto the menu Windows>Unhide.
2.if there is an item Personal.xls, select and OK that, 3. if there is no
such entry, cancel out. Then goto Step 4
3. goto menu Tools>Macro>Record New Macro..., and make sure the 'Store Macro
in' values is Personal Macro Workbook. A new tool.bar to 'Stop Recording'
will now appear, just click the stop button on that. Now go back Step 2
4. enter the holiday dates in Sheet1 range A1:A10 on the Personal.xls
workbook
5. Select A1:a10
6. Goto menu Insert>Name>Define..., and put holidays in the name box, and OK
7. Goto menu Window>Hide

You should be all set-up now. You will be prompted to save Personal.xls when
you close Excel, just this once, so say yes.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Kemi ,

If they don't have access to the file with holidays in it, their formula
will fail. VBA will not overcome that, an absence of data is an absence of
data whatever is used to access it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top