Hi Reader,

Welcome to the Google Sheets Tips newsletter #291, your Monday morning espresso, in spreadsheet form!

Coming to you on a Tuesday, because yesterday was Presidents' Day here in the US.

Oh, the quiet irony of making Monday the subject of a newsletter I send on Mondays... only for it to fall on a week that I send the newsletter on a Tuesday instead.

A big thank you to this week's sponsors who help keep this newsletter free to read:

➜ New videos on YouTube

Check out my newest video tutorials on YouTube:

🎥 How To Create Formula Pie Charts in Google Sheets​

And here's the accompanying blog post:

​Formula Pie Charts in Google Sheets​

If you enjoy these videos, please consider subscribing so I can continue to make more free tutorials in the future :)

_______

➜ News

I.
​Google is bringing an improved comment experience to Sheets soon.

​Sort, filter, and manage comments faster in Sheets​

_______

➜ Google Sheets Tip #291: Counting all Mondays between two dates

Today's tip comes from a question from Łukasz W. (thank you!).

He wanted to create an automatic way to count how many Mondays fell between two dates.

The NETWORKDAYS.INTL function counts the number of working days between a start date and an end date, excluding weekends and holidays.

There's a lot of customization possible with this function but what we're going to use today is the option to set a custom "weekend".

If we specify Monday as the only workday and set Tuesday to Sunday as the "weekend", then we can count the number of Mondays between 2 dates.

We add a third argument to the function: a string with 7 digits, consisting of 0's and 1's.

The 0s represent workdays (which are included in the count) and the 1s represent "weekends" (excluded). The first digit represents Monday and the last represents Sunday.

Let's assume our start date is in cell A2 and our end date is in cell B2.

Thus, our formula to count the Mondays is:

In our Sheet, it looks like this:

It gives an answer of 4, which is indeed how many Mondays there are in February 2024.

What if I want to count Thursdays?

To do this, we need the code: 1110111

The 0, which is our "workday" that we count, is in the 5th position, i.e. represents Thursday.

Our formula becomes:

In our Sheet:

And this time the answer is 5 Thursdays in February.

Other combinations

Of course, we can have more than one day in our count.

This formula will count Wednesdays and Sundays:

See if you can spot how we set up the code inside the formula.

And here's how it looks in our Sheet:

Nice!

I love it when there's a dedicated formula that solves the problem.

Originally, I solved this with a complex REDUCE formula, but I knew there were a bunch of Date functions that I rarely use. So, don't forget to search the Google Sheets function list sometimes to check what you're missing!

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. The coolest iceberg picture (no pun intended).