Exceljet

Quick, clean, and to the point

Formula to list weekends only

by Dave Bruns | July 25, 2017

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:

=IF(WEEKDAY(A1)=7,A1+1,A1+(7-WEEKDAY(A1)))

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:

=WORKDAY.INTL(A1,1,"1111100")

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
Your website and your emails are the best!!! Thank you! I’m going to try to do the pivot table video soon. -Lydia
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course