Dating Tips

BY J. CARLTON COLLINS

Q: When entering dates in Excel, it would be much simpler if I could enter the date as just the numbers without the forward slash character (for example, 06102010 instead of 06/10/2010). Entering the slash makes the exercise a two-handed exercise instead of a one-handed exercise via the numeric keyboard. Is there a way to enter data into just one cell so I can avoid using multiple cells and then concatenating (linking) them together?

 

A: You can enter dates into Excel 2003, 2007 and 2010 without using slash marks by applying a custom format. Start by highlighting a range of cells, then right-click on that range to display the pop-up menu. Select Format Cells to display the Format Cells dialog box. On the Number tab select Custom, and enter the following custom format in the Type box: 00/00/0000, then click OK. (Because the forward slash has special meaning in a custom format, you must precede it with a backslash for Excel to recognize the slash mark as a literal character, not its special meaning.) Thereafter, dates entered in that formatted range without slash marks will display slash marks, an example of which is shown below.

 

Once the custom format is created, Excel remembers it so you won’t have to re-create it.

 

Caution: Using this approach, the dates you enter will look like dates, and they will even sort like dates, but because the slash marks are inserted as formats instead of actual slash marks, date and time functions (such as =Month, =Day, =Weekday, and others) applied to these dates will not return correct results.

 

More from the JofA:

 

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.