The following will work if you are only dealing with checking dates in a giving year, if you are looking at calculating date spanning multiple years with the WORKDAY formula, you will need to either repeat the list adding +1 to the year in the function, or you will need to develop a different function for counting workdays and that would probably entail some VBA.
What you need to do is work out the logic on how the dates are figured out and store those calculated values in your sheet. Now assuming you have a date somewhere in your sheet or you refer to the year someone in your sheet that you will be dealing with, you can try the following. I will develop the logic for 3rd Monday in January. you can then use this though process to develop your own method for coming up with similar holidays. Take a look at the various built in date manipulation formulas built into excel such as DATE, YEAR, MONTH, DAY, WEEKDAY, TODAY, NOW, EOMONTH, etc. they may be of great help to you.
Dealing with dates that fall on X day or week of the month
I worked out the date using 7 steps in a table like this:
(A) | (B)
Year | 2014
Weekday of 1st day of month | 4
Weekday number for Monday | 2
Difference | -2
3rd Monday | 21
Days from start of month | 20
Target date | 14/01/20
Step 1) Determine the year.
In cell B1 I hard coded this with a supplied 2014, but this could also be
=YEAR(Q10)
Where Q10 is some cell on your sheet with a date in the range you are working with of if you just have a year number say 2017 in a cell you could just point at that cell without the year.
Step 2) Determine what day of the week the first day of the month is.
In order to do this we incorporate the DATE function imbedded in the WEEKDAY function. Since we know were are dealing with January and the first day we can hard code that. In B2 use:
=WEEKDAY(DATE(B1,1,1))
Step 3) State the day of the week a Monday is.
This is important to do as depending no how your weekday function defines the start of the week, the day of the week that Monday is can vary. since I did not date a change in the B2 formula we can supply the default value for Monday as 2. so in B3:
=2
Step 4) Find the difference between first weekday of month and Monday
The difference between the first weekday of the month and Monday need to be know in order to make an adjustment to the number of days since the start of the month the third Monday will fall. In B4 use:
=B3-B2
Step 5) Determine how many full weeks to third Monday.
If the difference is step 4 is negative or 0 you have another 21 days to work with, if its positive you need to work with 14 days. in B5 use:
=if(B4<=0,21,14)
Step 6) Day of the month for 3rd Monday
So basically we need to ad B5 with B4, and that will tell us how many days from the start of the month the 3rd Monday will be. In B6 use:
=B5+B4+1
Step 7) Determine the date of 3rd Monday
We know the year, we know the month and now we know the day. We take those three knowns and use the DATE function again. In B7 use:
=date(B1,1,B6)
Now if you want to back substitute that all into one formula in a single cell it would look something like:
=DATE(2014,1,IF(2-WEEKDAY(DATE(2014,1,1))<=0,21,14)+2-WEEKDAY(DATE(2014,1,1))+1)
Remember the 2014 would be from your spread sheet either using a cell with the year or using a cell with the date and using the YEAR() formula on that cell.
Dealing with a holiday falling on a Saturday or Sunday
Again we need to refer to some cell in your spreadsheet with the year so I will again use Q10 as the example and we will assume a date of 2014/10/24.
=IF(WEEKDAY(DATE(YEAR(Q10),12,25))=7,DATE(YEAR(Q10),12,24),IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25)))
The formula checks first if the weekday is a Saturday. W do this using a function that will return the day of the week see step 2) above. Which is this part from the equation above:
WEEKDAY(DATE(YEAR(Q10),12,25))
It will return a single integer 1 through 7 corresponding to the day of the week the date function results in, in this case. If its a 1 we known its Sunday, if its 7 we know its Saturday. So the check for saturday is:
WEEKDAY(DATE(YEAR(Q10),12,25))=7
If WEEKDAY()=7 is true then we provides the date of the day before which is really just subtracting 1 from the date we were looking at. We use this part of the formula to calculate that:
DATE(YEAR(Q10),12,24)
notice how I changed the day from 25 to 24. An alternate way would be to recycle our date and make the computer do one more calculation using this formula:
DATE(YEAR(Q10),12,25)-1
or
DATE(YEAR(Q10),12,25-1)
That all sits in the TRUE portion of the if statement. so if the date does not fall on Saturday then we wind up in the FLASE portion of the IF statement. Here we check with a second IF for the date falling on a Sunday. we use the same theory and process as we did for the Saturday check.
IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25))
Placing an IF statement inside an IF statement is commonly referred to as "nesting". This whole IF statement happens in the FALSE portion of the previous IF that checked to see if it was Saturday. This time we checked for Sunday:
WEEKDAY(DATE(YEAR(Q10),12,25))=1
When this is true, then we need to increase the date by 1 day instead of decreasing it like was done for Saturday:
DATE(YEAR(Q10),12,26)
or
DATE(YEAR(Q10),12,25)+1
or
DATE(YEAR(Q10),12,25+1)
So that was the true portion of the Sunday check. Logically speaking the only way to get to the FALSE portion of this nested IF statement is to fail the Saturday check and then fail the Sunday check. Which means you do not need to go through and check if is the WEEKDAY comes out as 2, 3, 4, 5 or 6! Its one of those by the process of eliminating Sunday and Satruday (1 and 7). And if the date falls on Monday-Friday we dont need to change the date and can leave it just as is:
DATE(YEAR(Q10),12,25)
HOLIDAY TABLE
This is a potential way you could layout a holiday table.
little explanation here. Row 3 and row 4 are the same information. The difference is row 4 was formatted to only display the year. Its actually a full date like row 3. This is also very much different than enter the year as a number say 2014 or 2016. Those are just numbers. You can go any route you want, but you need to make your formulas work with what you are supplying them with.
In this example, I just used two holidays. The third Monday in January example we worked out before and the Christmas case. The holidays were layed out vertically beneath the year with all formulas referencing Q$4. The year headers were copied to the right increasing the year by 1 for each column. The holiday formulas were copied to the right. As you do this they will automatically change the Q to R then S then T etc. so they will always be referencing the year above them.
Then when you use your workday formula, instead of referencing $Q$5:$Q$6 for this example you would reference $Q$5:$V$6.
The formula for advancing the date in R3 or R4 is:
=DATE(YEAR(Q4)+1,1,1)
