Date Range

C

CJ

Hi,

Can someone please assist me with the following issue:

I am currently using the formula noted below to direct
another application ( Bloomberg ) to select prices from
the previous 6th business day.

My question is> Is there any way to revise the formula to
exclude US holidays for successive years (i.e. for 2005,
2006...) without having to manually change the date
entries each year, in the formula?

=WORKDAY(Today(),-6,
{"5/31/04","7/5/04","9/6/04","11/25/04","12/25/04","1/1/05"
})


Thank you
 
J

Jason Morin

I'd recommend listing the holidays in different ranges and
then referencing that range. For example, 2004 holidays
are in A1:A4:

=WORKDAY(Today(),-6,A1:A4)

and 2005 holidays are in B1:B6:

=WORKDAY(Today(),-6,B1:B6)

HTH
Jason
Atlanta, GA
 
J

Jason Morin

But if you're looking for 1 formula, regardless of the
what the current year is, and 2004 holidays are in A1:An,
2005 holidays in B1:Bn, etc., try:

=WORKDAY(TODAY(),-6,OFFSET(INDIRECT(CHAR(RIGHT(YEAR(TODAY
()),2)+61)&"1"),,,COUNT(INDIRECT(CHAR(RIGHT(YEAR(TODAY
()),2)+61)&":"&CHAR(RIGHT(YEAR(TODAY()),2)+61)))))

HTH
Jason
Atlanta, GA
 
G

Guest

Hi Jason,

Thanks for the response. Unfortunately, the option you
suggest requires two seperate formulas for each different
year. For the model I am building, it can only reference
one specific cell for the specific date to use, and the
formula would have to go to perpetuity. Do you know of a
formula that would meet this criteria?

In other words, hypothetically a formula like:
=WORKDAY(Today(),-6,"A1:G1",(IF(AND A1:G1=xxxx2004,+1))
With A1:G1= Holidays in 2004 shown as xxxx2004

Thanks,
CJ
 
G

Guest

Hi Jason,
Thanks for the response. Two quick questions...
1) Will the formula recognize the holiday dates if I place
them in A1:A500 for 2004 and B1:B500 for 2005?
2) Will the formula use these previous dates as a basis
for recognizing the future dates in 2006, 2007 etc?

Thanks again
CJ
 
Top