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:
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
- More about WORKDAY.INTL
- Video about WORKDAY
- More formula examples
- Excel formula training