Sure, you can use a complicated WEEKDAY formula to generate a list of weekends. But with WORKDAY.INTL, you can do the same thing with a WAY simpler formula. The trick is to use a special 7-digit "mask" to filter out all dates except weekends.

In a world where everyday is Saturday or Sunday....

Here's a little puzzle for can you use Excel to generate a list of dates that are weekends only? For example, a list of Saturday Sunday pairs like this:

Example list of weekend dates only

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.

Using WORKDAY.INTL to generate weekend dates only

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

  1. More about WORKDAY.INTL
  2. Calculate due dates with WORKDAY (video)
  3. More formula examples
  4. Excel formula training
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.