I won’t normally post Excel stuff on here, but while researching an XSLT/Javascript function I was determined to get it right in Excel first, so I understood it better, and definitely wanted to document it so I could pull it back up later!

In this example, I need to know the second Wednesday of next month, because that’s when server maintenance is every month. But what day is it? Here’s the Excel formula:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*2)-WEEKDAY(DATE(YEAR(TODAY(),MONTH(TODAY())+1,8-4))

Notice the DAY argument of the first DATE function in the formula says “1+7*2”. The “2” represents the week of the month, so if you wanted the third week change it to “3”, etc.

Next, notice the DAY argument of the second DATE function in the formula says “8-4”. The “4” represents the day of the week, where 1 is Sunday, 2 is Monday, etc. So if you wanted Fridays only, change it to 6.

This worked in Excel, now to figure out how to do this in JavaScript and/or XSLT. Ultimately I need it in XSLT for InfoPath, and the generic formula functions in InfoPath don’t come close to this. Once I figure it out I’ll post it here.