Contextures

Contextures News 20200310

Excel Visible Rows Only

March 10, 2020

Find the Nth weekday in a month, visible rows only, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Nth Weekday

Some events occur on the Nth weekday of a specific month each year. For example, US Thanksgiving is the 4th Thursday in November, and here in Canada, we celebrate Victoria Day on the 3rd Monday in May.

You can calculate those dates in Excel, with a couple of date functions -- DATE and WEEKDAY.

calculate nth weekday in month

Here's the formula in cell C9 -- it uses names, instead of cell references:

=DATE(SelYr,SelMth,1
  + ((SelN-(SelWD>= WEEKDAY(DATE(SelYr,SelMth,1)))) *7)
  + (SelWD- WEEKDAY(DATE(SelYr,SelMth,1))))

How It Works

The formula caluclates the 1st of the month date, with the DATE function. It also calculates the weekday number for that date, with the WEEKDAY function:

WEEKDAY(DATE(SelYr,SelMth,1)

  • Then, it adds days, based on which week the Nth occurrence is in
  • Adds or subtracts days, to get to the selected weekday

For more details on the formula, and a sample file, go to the Nth Weekday page on my Contextures site.

find the nth weekday in month

Visible Rows Only

When you build a pivot table, it includes all the data from your list, even if some of the rows are hidden by a filter. Sometimes though, you'd rather not have those hidden rows included, for privacy reasons.

In the past, you could use macros to copy the visible data to a different sheet, and build your pivot table from that new data. But now, with the new Excel function, FILTER, you can get the visible rows only -- with NO macros. Then, build the pivot table from the dynamic array.

There are step-by-step instructions on my Contextures blog, and you can get the completed sample file on the Pivot Table Source Data page of my website.

pivot table from visible rows only

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Don't Worry: We knew this all along, but Tyler Folkman explains why you shouldn’t be embarrassed to use Excel, as a data scientist. (Level-All)

Programming: If you'd like to get started with Excel macros, the Think Like a Coder animated video series, by TED-Ed, is a fun way to learn some of the key concepts. Eight of the 10 episodes are available on YouTube, and kids might enjoy the series too. (Level-Int)

Excel Humour: See what people said about Excel recently. Did you start using Excel on a Mac or on Windows?

Also see: My Excel Products || Previous Issues

Bee Happy

We had beautiful weather on the weekend, and lots of outdoor family fun on Sunday, with ball hockey, football tossing, and climbing trees at the park. Later in the day, we headed indoors, and made zines ("zeens") with happy themes -- we figured the world could use a smile or two! Zines are like tiny magazines, made from a folded sheet of letter-sized paper, with one cut in it. Make an Excel-themed zine, and give it to a friend!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200310ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

Date Functions

Pivot Table Source Data

 

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: March 9, 2020 3:11 PM