Contextures News 20181211

Excel Weekend Dates Only

December 11, 2018

Fix hidden rows, enter weekend dates 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.

Happy Holidays! This is the final newsletter for 2018, and I'll be back on January 8th.

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

Quick Tip

Usually it's easy to unhide rows or columns that have been temporarily hidden. Select the columns (or rows) on either side of the hidden ones, then right-click, and click Unhide.

However, if the first few rows or columns are hidden, it's a little trickier to unhide them. The animated gif below shows the steps:

• For rows, press on the first visible row button, then drag up, onto the Select All button. The little popup shows the number of rows you've selected.
• Then, right-click the first visible row button, and click Unhide.

NOTE: For hidden columns, select the first visible column, and drag to the left.

Weekends Only

To make sure that people make valid entries in a cell, you can set up a drop down list, and only allow those items. That doesn't work for every situation though.

In this example, we want people to enter a date in cell B2, for weekend dates only. If a Monday to Friday date is entered, a warning message should appear, and prevent the entry.

In Excel's Data Validation, you can set up Date Rules, but those rules only let you set up date ranges. To allow weekends only, you can set up a Custom Rule, using the WEEKDAY function.

=WEEKDAY(B2,2)>5

That formula has 2 as the second argument, so the weekdays are numbered from Monday (1) to Sunday (7). We'll only allow dates where the weekday number is greater than 5 (Friday).

That formula will also prevent entries that aren't dates. If you enter text, the WEEKDAY function returns an error, and that isn't greater than 5.

NOTE: To show an error message, set up an Error message in the Data Validation settings.

Go to my Contextures website, to see more Custom Rules for Data Validation.

Excel Articles

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

Challenge - Try this Titanic challenge from the Excel section on Reddit. Download the workbook with details for the people on board, and answer the questions (easy, medium, hard) (Level - All)

Data Viz - Jonathan Schwabish taught data visualization to his son's 4th-grade class, and there's good information (and fun ideas) there for adults too! (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Bumpy Ride

Our grandson was here on the weekend, and he always asks for a story that is "funny, but true." This time, I told him about my long-ago bumpy flight home from Gods River, Manitoba, about 400 miles north-east of Winnipeg. We flew in a Grumann Goose, and I think we did an extra 100 miles up and down, as the plane flew through turbulence!

The bottom photo shows the grassy air strip in Gods River, and the upper photo was from our stop at Norway House, where we landed on water. It was quite an adventure!

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181211ctx.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
[email protected] gmail.com

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.