Entering Dates and Times

Although Excel records dates and times as serial date values, you don't have to type them that way. You can manipulate dates and times in your worksheet formulas just as you manipulate other types of values. You enter date values in formats that Excel automatically applies. To enter date values in this way, type the date in one of the following formats: m/d/yy, d-mmm-yy, d-mmm, or mmm-yy. (You can also type four-digit years for any of these formats.)

Note 

You can change the default date, time, currency, and numbering settings through the Clock, Language, And Region item in Windows Vista Control Panel (Date, Time, Language, And Regional Options in Windows XP). These settings will determine how Excel interprets your date entries. For example, with regional options set to Italian, typing a date in d/m/yy format will result in a properly displayed date, but if you type the same date with regional options set to English, the entry is interpreted as text.

If your entry doesn't match any of the built-in date or time formats, Excel picks the format that's most similar to your entry. For example, if you type 1 dec, you see the formatted entry 1-Dec in the cell. In the formula bar, the entry appears as 12/1/2008 (if the current year is 2008) so you can edit the date more easily.

You can also type times in a time format. Select a cell, and type the time in one of the following forms: h: mm AM/PM, h:mm: ss AM/PM, h:mm, h:mm:ss, or the combined date and time format, m/d/yy h:mm. Notice that you must separate the hours, minutes, and seconds of the time entries by colons.

For more information about custom formats, see "Creating Your Own Date and Time Formats" on page 527.

If you don't include AM, PM, A, or P with the time, Excel uses the 24-hour (military) time convention. In other words, Excel always assumes that the entry 3:00 means 3:00 AM, unless you specifically enter PM.

You can enter the current date in a cell or formula by holding down Ctrl and pressing the semicolon (;) key. This enters the date stamp in the current short-date format, as set in Control Panel. Enter the current time in a cell or formula by holding down Ctrl+Shift and pressing the colon (:) key. This enters the time stamp in h: mm AM/PM format. (Of course, the colon and semicolon occupy the same key-the Shift key changes the entry to a time stamp.)

Inside Out-The Magic Crossover Date

December 31, 2029, is the default magic crossover date-that is, the last day Microsoft Windows assumes is in the future if you enter the year using only two digits. For example, if you type 12/31/29 in a cell, Windows assumes you mean the year 2029. If, however, you type 1/1/30 in a cell, Windows interprets it to mean January 1, 1930.

You can change this magic crossover date by changing the setting on the Date tab in the Customize Regional Options dialog box. To access this dialog box, open Control Panel, and then click Clock, Language, And Region. Next, click Regional And Language Options, and then click the Customize This Format button. Finally, click the Date tab, and change the last date (2029) to the value of your choice. Of course, you're still limited to a 100-year span; if you change the last date Windows recognizes as being in the future, the corresponding beginning date-January 1, 1900-changes accordingly. Therefore, if you need to enter century-spanning dates, you should get into the habit of typing the full four-digit year to avoid surprises.

Entering a Series of Dates

You can create an evenly spaced series of dates in a row or column in several ways, but the job is especially easy when you use the fill handle. Suppose you want to create a series of dates in row 1. The series begins with March 1, 2008, and the dates must be exactly one month apart.

If you type 3/1/2008 in cell A1 and drag the fill handle to the right, Excel extends the series of dates incrementally by days, as shown in Figure 15-1. After you drag, Excel displays a smart tag adjacent to the selection. Click the smart tag to display the smart tag action menu shown in Figure 15-1, which displays a number of AutoFill options; select Fill Months to convert the already-extended day series into a month series.

Figure 15-1: After you drag the fill handle to extend a date series, use the smart tag action menu to adjust the series.

If you drag the fill handle by right-clicking it, a shortcut menu that is similar to the smart tag action menu appears. You can use this shortcut menu to select a fill command before performing any fill action. If what you want to do isn't represented on the menu, click the Series command to display the Series dialog box.

You can use the Series command to tend to a series of dates with a bit more flexibility than using the fill handle. To use this approach, type the starting date, select the range of cells you want to fill (including the starting date), click the Fill button on the Home tab on the Ribbon, and click Series to display the Series dialog box shown in Figure 15-2.

Figure 15-2: Use the Series dialog box to create date series.

When extending a series of dates, remember the following:

• You can use the Series In options to choose whether to extend the selected date across the current row or down the current column.

• You can use the Step Value option to specify the interval between cells. For example, by typing 2 in the Step Value text box and selecting Month in the Date Unit area, you can create a series of dates occurring every other month. By typing a negative number in the Step Value text box, you can create a series that decreases (goes backward in time).

• You can use the Stop Value text box to set an ending date for the series. Using this method, you can use the Series command without having to figure out how many cells to select in advance. For example, to enter a series of dates that extends from 1/1/08 through 12/31/10, type 1/1/08 in a cell. Then select only that cell, display the Series dialog box, select the Columns option, and type 12/31/10 in the Stop Value text box. Excel extends a series of dates following the original cell.

For more information about AutoFill and the Series command, see "Filling and Creating Data Series" on page 211.

Extending an Existing Date Series

The AutoFill feature uses the selected cells to determine the type of series you intend to create when you drag the fill handle. AutoFill copies text and nonsequential values and increments sequential numeric values. Because dates are stored as serial values, AutoFill extends them sequentially, as illustrated in Figure 15-3.

Figure 15-3: Starting with the values in the Selected Values area, we created the values to the right by dragging the fill handle.

When you use the fill handle to extend the value in a single selected cell, Excel assumes you want to increment the numeric value in each cell. (If you want to copy the cell instead, hold down Ctrl while dragging the fill handle.) Notice that in Figure 15-3 the entries in rows 7 through 11 contain text values. AutoFill recognizes text entries for days and months and extends them as if they were numeric values. In addition, when a cell contains a mixed text and numeric entry (as in row 11), AutoFill copies the text portion if it's not the name of a month or day and extends the numeric portion if it occurs at either end of the entry.