In actual fact, you don't need a "Day 7" ... I was just getting carried away 
"Day 1" is a Sunday, "Day 6" should have been "Day 7" and is a Saturday. The formulas in between represent what value to return for weekdays, that is 2,3,4,5,6.
So, the formula should be:
The reason for doing it that way is that your formula returns FALSE for a weekend date. You might not list any weekend dates, so it won't matter either way. I just think it's a bit neater and will highlight any anomalies.
The key difference is that the formula does a VLOOKUP in a table of holiday dates and, if it finds it, it returns the value in the next column. As you know the date(s) of the holiday(s), you can replace "Hol 1", "Hol 2", etc., with actual dates of your choice. If it doesn't find an entry in the lookup table, the IFERROR function kicks in and executes the original formula or the replacement provided.
Your formula, with the VLOOKUP addition would look like this:
As you can see, it's a little longer than the alternative provided.
The VLOOKUP in either case is using a holiday lookup table in cells M1:N5 ... but it could be anywhere. I just don't know where you want it.
You haven't provided a sample workbook so the only thing I have to go on is the formula. I had to make up data in order to test the solution. Obviously, I don't know what your workbook and worksheet structure is like so I can't provide an "out of the box" solution.
Regards, TMS
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks