Software

Office challenge: How do you convert date strings to valid date values in Excel?

This week's Office challenge tests your knowledge of Excel date values and reveals the answer to last week's challenge on Access controls.

Recently, a client received a spreadsheet — a badly designed spreadsheet — and needed help. One of the minor problems was a column full of date strings. You might remember the days when we preceded dates with an apostrophe, e.g., '08/11/2009, '08/12/2009, and so on. Excel is much smarter now, and the apostrophe is unnecessary. The choice was to convert the date strings to valid date values or to compensate for the date strings in all future formulas and functions. It's easy enough to accommodate the date strings, but the client opted for valid date values. How can you convert a column of date strings to valid date values? (This one was so easy, I did it for free.) Last week, we ask… How do you get rid of labels when inserting controls into an Access form? This is one of my favorite tips. By default, Access inserts a corresponding label control with most controls. If you don't want the label, you have to select just the label and press Delete to get rid of it. It isn't a big deal unless most of your controls don't require labels. If that's the case, you can inhibit the label as follows:

  1. Click the appropriate control in the Toolbox but don't insert the control just yet.
  2. Click the Properties icon.
  3. Set the Auto Label property to No.
  4. Insert the control.
All subsequent controls of the same type will not have a corresponding label control. To undo the label fix, simply repeat the process, but choose Yes in step 3. Thanks to Cuhlig, Amasa, and Wazz for playing along and answering correctly.

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