A
Alpur
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.
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.