Here’s a little puzzle for you…how can you use Excel to generate a list of dates that are weekends only? For example, a list of Saturday Sunday pairs like this:
A couple years ago, I found and described a formula that will do it using the WEEKDAY function and some tricky date logic handled with IF: With a date in A1, you can enter the formula in A2 and drag down to get your list of weekend dates. This formula works fine, but it’s overly complicated. As a smart reader pointed out recently, you can do the same thing with the WORKDAY.INTL function and a much simpler formula: This takes advantage of what I call the “mask” feature of WORKDAY.INTL, which allows you to designate any day of the week as a weekend. The logic may seem a little backwards, but basically 1 means “weekend” and 0 means “not weekend”. So, “1111100” effectively filters out all days except Saturday and Sunday by telling WORKDAY.INTL that Mon-Fri are weekends.
What I love about this example is how an initially complicated formula “collapses” into a simple solution. Excel is full of hidden gems like this that can drastically simplify your work. The trick is of course is finding them :) By the way, the NETWORKDAYS.INTL function also supports same 7-digit mask feature.
More formula info
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
