Holiday Dates

[Guest]

I have a problem in trying to calculate valuation dates. The problem is
arising because I have to take in to account two sets of holidays i.e. local
holidays and US holidays. The date requires to be 2 working days (TD+2)
after the last day of each month. When looking at only the local holidays I
do not have a problem as I can use the WORKDAY function. However, i have a
problem when I have to take in to account the US holidays also: - if TD+1 is
a US holiday but not a local holiday it should be ignored, if TD+2 is a
USholiday but not a local holiday it should be included in the calculation.
I have got as far as: -
=IF(AND(ISNA(VLOOKUP(WORKDAY($A5,1,USDHols),1,FALSE)),ISNA(VLOOKUP(WORKDAY($A5,1,AUDHols),1,FALSE)),WORKDAY($A5,2,AUDHols)),WORKDAY($A5,2,AUDHols))
but, for example, when I enter 04/07/05 as a US holiday only, I still get the
answer 04/07/05 when I am expecting 05/07/05. Can anyone help please.

 

[Ron Rosenfeld]

I have a problem in trying to calculate valuation dates. The problem is
arising because I have to take in to account two sets of holidays i.e. local
holidays and US holidays. The date requires to be 2 working days (TD+2)
after the last day of each month. When looking at only the local holidays I
do not have a problem as I can use the WORKDAY function. However, i have a
problem when I have to take in to account the US holidays also: - if TD+1 is
a US holiday but not a local holiday it should be ignored, if TD+2 is a
USholiday but not a local holiday it should be included in the calculation.
I have got as far as: -
=IF(AND(ISNA(VLOOKUP(WORKDAY($A5,1,USDHols),1,FALSE)),ISNA(VLOOKUP(WORKDAY($A5,1,AUDHols),1,FALSE)),WORKDAY($A5,2,AUDHols)),WORKDAY($A5,2,AUDHols))
but, for example, when I enter 04/07/05 as a US holiday only, I still get the
answer 04/07/05 when I am expecting 05/07/05. Can anyone help please.

It seems to me that the simplest method would be to set up two holiday lists --
one which applies to TD+1, and the other which applies to TD+2.

Are my assumptions correct below?

TD+1: +US / -local :=Ignore
-US / +local :=Include
+US / +local :=Include
-US / -local :=Ignore

TD+2: +US / -local :=Include
-US / +local :=Include
+US / +local :=Include
-US / -local :=Ignore

That being the case, then your holiday list with regard to TD+2 would be the
combination of AUDHols + USHols.

I believe that duplicate holidays only get counted once by WORKDAY. So you
could just copy/paste your list of local holidays below the list of US holidays
to make a combined list.

Or you could download Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use the ARRAY.JOIN function to make a single array
out of your two holiday lists.

I think either of these formulas will do what you want.

I assume $A5 is the last day of the month.

=WORKDAY(WORKDAY(A5,1,AUDHols),1,ARRAY.JOIN(USHols,AUDHols))

or, without the morefunc.xll add-in:

=WORKDAY(WORKDAY(A5,1,AUDHols),1,Combined_Holiday_List)


--ron

 

[Guest]

Many thanks, Ron. Your second method worked perfectly (my company aren't too
keen on us downloading data so I haven't tried your first method).

Alan.

 

[Ron Rosenfeld]

Many thanks, Ron. Your second method worked perfectly (my company aren't too
keen on us downloading data so I haven't tried your first method).

Alan.

You're welcome. Glad to help. Thanks for the feedback.


--ron