Right now the formula is just taking 5 days before and 5 days after and not taking into account holidays and weekends.
I want to change the formulas in column D and E to 5 workdays/holidays before(column D) and 5 workdays after (column E). The holidays are in Column G.
Last edited by rhudgins; 04-29-2010 at 10:31 AM.
Try this,
D6=IF(SUMPRODUCT(--($H$6:$H$5000=B6),--($I$6:$I$5000>=C6),--($I$6:$I$5000<=WORKDAY(C6,$F$2,$G$7:$G$60))),"YES","")
E6=IF(SUMPRODUCT(--($H$6:$H$5000=B6),--($I$6:$I$5000>=WORKDAY(C6,-$F$2,$G$7:$G$60)),--($I$6:$I$5000<=C6)),"YES","")
And copy down.
Regards
Note that pre XL2007 the WORKDAY function as used above requires activation of the Analysis ToolPak Add-In (via Tools -> Add-Ins)
If you can not use the Add-In for whatever reason (it must be activated on each client that uses the file) let us know.
The same can be achieved without it but the syntax is undoubtedly more complex (and longer).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks that formula works. I didnt realize it but the formulas in Column F now needs to be adjusted. Can you help me make this adjustment?
Replace >=C6-$F$2 by >=WORKDAY(C6,-$F$2,$G$7:$G$60) and <=C6+$F$1 by <=WORKDAY(C6,$F$2,$G$7:$G$60)
Regards
Thanks this works!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks