Formula to list weekends only
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 Example list of weekend dates only](https://exceljet.net/sites/default/files/images/articles/inline/list%20of%20weekend%20dates%20example1.png)
=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 Using WORKDAY.INTL to generate weekend dates only](https://exceljet.net/sites/default/files/images/articles/inline/workday.intl%20function%20weekends%20only.png)
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 :)
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1