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

2018 financial reporting survey: Challenges and trends

Learn the top reporting challenges that emerged in a survey of more than 800 finance, accounting, and compliance professionals across the world, and compare them with your organization's obstacles.

PODCAST

How the skill set for today’s CFO is changing

Scott Simmons, a search expert for large-company CFOs, gives advice for the next generation of finance leaders and more, including which universities are regularly producing future CEOs and CFOs.