A First Date with Excel: Entering Dates

Excel features a special format for dates. Unfortunately, some people find out about how Excel formats dates only after dates hadn’t been entered properly. Perhaps an invoice gets sent out on December 1 instead of January 12 because the day and month were reversed. Or maybe you can’t sort that data you collected because the dates weren’t entered properly.

Once you master dates, you can do important features such as tracking the age of invoices and trouble tickets, predicting membership expiry, sorting employees by their effective hired date, calculating early-bird discounts, and more — all of which we will cover in upcoming posts.

But the first step along this path is to how to enter dates properly, which we cover in this post.

Numeric Data Entry

We need to be careful and precise with how we enter these values. Excel recognizes other date entry styles, including ones with words (ie. Feb 14, 2011). When you want to enter a date using pure numbers, here is the simple rule:

number separator number [separator number]

• number is the month, day, or year;
• separator is the delimiting symbol used in the date (such as “/” or “-”)
• the square brackets indicate an optional component (such as a year number)

When using the numeric date method, the separator or delimiting symbol can be a slash or hyphen.

The date, Feb 14, 2011, can be entered numerically by typing “2/14/11” (assuming American date format), which also happens to be the fastest way to enter the date.

Date Entry Feedback

When entering any data in unformatted (technically, General format) cells, you get a little bit of feedback as to how Excel has interpreted the entry. Watch closely as you hit the <Enter> or <Return> key.

If an entry appears left-aligned upon entry, Excel considers the data to be text. Excel right-aligns items it thinks are a numeric value. Dates (and times) should be captured as numeric values, so they should appear right-aligned.

Try the following experiment:

1. Make a spreadsheet column really wide.
2. Enter the following date, exactly, but without the quotes, “Febr. 28, 2012″ and press <Enter> or <Return>. Note the entry is left-aligned. It’s text.
3. Now, in the cell below, enter the following exactly, without the quotes, “Feb 28, 12″ and press <Enter> or <Return>. Note the entry is right-aligned. It’s numeric. It’s a “true date value.” Also note Excel reformatted the numeric date value slightly.

Any human on the planet knows what you meant by the first entry, even though it is formatted as text and not a date. Excel, however, does not consider it a date. Consequently, you will not be able to, for instance, perform math operations on the first date. And if you sort it, it will be alphabetical, not chronological.

Tip: Excel supports two types of data: text or numbers. That’s it!

Date Interpretation

Which number you enter first when supplying a date depends not on Excel but your operating system’s regional settings. We explain how to change this later in this post.

If your computer is set to use the American format for dates, then you’d enter the month number first. For example:

6/7/2011

Excel will assume that’s the 7th of June 2011.

British format has the day first, so the same date would be entered as:

7/6/2011

If you find you prefer a different date format than your current one, you have to change this using your system settings.

Setting Excel’s Date Format

Once the date is entered properly you can focus on how Excel displays the date.

To change how a date is displayed in Excel:

1. Select the cells to apply the format change to.
2. Choose HOME, FORMAT , then select FORMAT CELLS. Or, [Right Click] the selected cells, then select FORMAT CELLS.
3. Select DATE from CATEGORY, then select a TYPE. [Click] OK.

There is no way to make this setting permanent. It has to be applied to cells each time you wish to change the formatting. However, you can use the Format Painter if you already have a cell with the desired format available:

1. Select the cell with the desired date format applied.
2. Choose HOME, , then select the date(s) you wish to format.

Date Entry Shortcuts

Excel allows you to enter dates quickly, with fewer keystrokes. You do not need to enter the century digits, unless you want a date in the distant past or far future. For example:

6/7/9

Excel, in American mode, will interpret this entry as the 7th of June 2009; in, for instance, British mode, it will be the 6th of July, 2009.

Also, you don’t need to enter the year at all if you’re supplying a date within the calendar year. For example: 6/7 means the 7th of June the current year.

Be sure to note that leading zeros can be left out. So, instead of entering 06/07/09, you can save time by simply typing 6/7/9.You may want a date to appear with leading zeros but that’s a formatting issue we’ll cover in an upcoming post on date formatting.

From This Date Forward: Pay Attention to Dates

Working with dates in Excel spreadsheets becomes much easier simply by collecting and entering data correctly. Dates entered as text cannot be used in calculations, will not sort correctly, and will not be used effectively in AutoFilter lists, PivotTables or charts. So it’s important to be careful when you enter dates.

In upcoming posts, we’ll talk about time entry, formatting, time calculations, and more.

For more shortcuts, quick tips, and step-by-step instructions, check out our Quick Reference Cards. They’re the perfect resource for casual computer users right through to trainers and trouble-shooters to get the software information they need quickly.