Trying exclude holidays in this argument. Basically if the Holiday falls on the weekday result I need to push the end date to the next weekday or back one weekday.
For example:
=C7+45+LOOKUP(WEEKDAY(C7+45),{1,2,7},{-2,0,-1})
The date in C7 is 7/23/2010 the result is 9/06/2010 which is Labor day. I want the result of 09/03/2010 or 09/07/2010.
Any suggestions on how to add the lookup value for HOLIDAYS in this formula?
Since this scenario could repeat for any number of holidays, you might opt for using a look up table containing the dates of each holiday, then nest the formula in an IF Function to test whether to increment or not based on a matching date against a holiday. You have done this in principle with the formula, but a table would be a better choice
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Did we not cover this already: http://www.excelforum.com/excel-prog...arguments.html
=WORKDAY(C7+46,-1,holidays) -> 3rd Sep
or conversely
=WORKDAY(C7+44,1,holidays) -> 7th Sep
where holidays is a named range containing public holidays (ie incl. 6-Sep-2010)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks