Formula to list weekends only - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Wednesday, July 8, 2020

Formula to list weekends only

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
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 :)
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment