Holiday Dates

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.

On Wed, 16 Nov 2005 00:51:06 -0800, "Alpur" <[email protected]>
wrote:

>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

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

> On Wed, 16 Nov 2005 00:51:06 -0800, "Alpur" <[email protected]>
> wrote:
>
> >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
>

On Wed, 16 Nov 2005 07:16:20 -0800, "Alpur" <[email protected]>
wrote:

>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