Software

Office challenge: How would you inhibit the entry of weekend dates without using VBA?

Learn the solution to last week's Excel formatting challenge and test your skills on a new Excel challenge.

Code isn't always the answer—or rather, code is seldom the only answer. A client recently struggled with a way to restrict the entry or weekend date values. She just couldn't get the code right and finally called me. She was almost miffed when I showed her Excel's easy code-less solution! How would you keep users from entering weekend dates, without using VBA?

Last week we asked…

How do you display leading zeroes in Excel? Rickk was the first to respond by pointing to a couple of previous entries, one of his and my personal favorite, a custom format, which you apply as follows:

  1. Select the cell or range in question.
  2. Right-click the selected cell or range and choose Format Cells from the context menu.
  3. Click the Number tab.
  4. Choose Custom from the Category list.
  5. In the Type field, enter the number of 0s necessary to accommodate the largest value. For instance, if the largest value contains four digits, enter four 0s. (To accommodate decimal values, enter placeholders for the appropriate number of digits.)
  6. Click OK and Excel displays leading values.

When these values are text — part numbers, ZIP codes, and so on — you don't have a problem. Simply precede the value with an apostrophe character (').

Thanks to everyone for the interesting and creative solutions. The simple custom format solution won't always be practical so alternatives are great!

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox