Formula to list weekends only – Best article

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

Here’s a little puzzle for you…how can you use Excel 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. Video about WORKDAY
  3. More formula examples
  4. Excel formula training
 

Leave a Reply