Forum Discussion
WORKDAY Function check specific location Holidays
Hi Guys I need to set the next good business day for a calculation based on a specific holiday calendar for London for example. I have a large data set for ALL holidays. I need to return the full...
Something like this may work for you. The key is the FALSE return cannot be text or you get a #VALUE error if the location is not London.
=WORKDAY(D1,1,IF(C1="London",holidays,0))
This helped to a certain extent thanks a lot. However the problem I have is that Index and Match will work when the data is structured a certain way, Match will only return a row number or column number and that would work if I had all dates for London in a single column or row but I have a table where a date is a value the location applies to each one of those. On investigating other solutions using AGGREGATE, SMALL, ROWS it seems you always need to drag the formula down to return the dates rather than a single formula returning the array..which is what I need as the input for the WORKDAY function.