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 Control Panel (Date, Time, Language, And Regional Options in Windows XP). These settings determine how Excel interprets your date entries. For example, with regional options set to Italian, typing a date in d/m/yy format results 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/2010 (if the current year is 2010) 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.
Note
For more information about custom formats, see Creating Your Own Date and Time Formats on page 571.
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 short-date format currently 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.)
Tip
INSIDE OUT The Magic Crossover Date
December 31, 2029, is the default magic crossover date—that is, the last day Excel 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, Excel assumes you mean the year 2029. If, however, you type 1/1/30 in a cell, Excel interprets it to mean January 1, 1930. (Perhaps this would be better characterized as a crossunder date.)
You can change this magic date, but not from Excel; instead you use the Windows Control Panel. (Therefore, changing this setting also affects any other applications that need to interpret past or future date entries.) In Windows 7 or Windows Vista, open Control Panel, and then click Clock, Language, And Region (Regional And Language Options in Windows XP). Next, click Region And Language, and then click the Additional Settings button. (In Windows Vista, click Regional And Language Options, and then Customize This Format. In Windows XP, click the Customize button on the Regional Options tab.) 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 that Windows recognizes as being in the future, the corresponding beginning date—January 1, 1900—changes accordingly. If you need to enter century-spanning dates, you should get into the habit of typing the full four-digit year to avoid surprises.
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, 2011, and the dates must be exactly one month apart.
If you type 3/1/2011 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 an option button adjacent to the selection. Click the button to display a menu, shown in Figure 15-1, that provides a number of AutoFill options; select Fill Months to convert the day series into a month series.
Figure 15-1. After you drag the fill handle to extend a date series, use the options menu to adjust the series.
If you drag the fill handle by right-clicking it, a shortcut menu that is similar to the options 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 with 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.
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.
Note
For more information about AutoFill and the Series command, see Filling and Creating Data Series on page 229.
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.
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 in Figure 15-3 that the entries in rows 7 through 11 contain text values. AutoFill recognizes text entries for days and months and extends them as though they were numeric values. In addition, when a cell contains a mixed text and numeric entry (as in row 10), 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.
