Working with Dates

Dates and times are actually numeric values that have been formatted to appear as dates and time. You can change the way Excel displays the date and time if you want.

The Date and Time categories are in the Category list on the Number tab in the Format Cells dialog box. You can use the Date format to display date and time serial numbers as date values with slashes or hyphens. The default Date format is the month and day separated by a slash; for example, 7/2 . To display only the time portion, use the Time format.

The Time format lets you display date and time serial numbers as time values with hours, minutes, seconds, AM, or PM. The default Time format is the hour and minutes separated by a colon ; for example, 11:00 . You can perform calculations on the time values. To display only the date portion, use the Date format.

Understanding Date and Time Formats

Excel offers a wide variety of date and time formats, which are listed in Table 9.2.

Table 9.2. Excel's Date and Time Formats

Date/Time Format	Sample Date/Time
m/d	7/2
m/d/yy	7/2/98
mm/dd/yy	07/02/98
d-mmm	2-Jul
d-mmm-yy	2-Jul-98
dd-mmm-yy	02-Jul-98
mmm-yy	Jul-98
mmmm-yy	July-98
mmmm d,yyyy	July 2, 1998
m/d/yy h:mm	7/2/98 7:30
m/d/yy hh:mm	7/2/98 19:30
hh:mm	13:35
h:mm AM/PM	1:35 PM
h:mm:ss AM/PM	1:35:50 AM

Changing Date Formats

After you figure out which date and time format you want to use, you can change the dates using the Format Cells dialog box.

In the To Do exercise, you need to format the date on the Summary sheet in the Sales workbook.

To Do: Change a Date Format

1. Click the Summary sheet tab. Select the cell that contains the date you want to format; in this case, select cell A12.

2. Click the Format menu and choose Cells. The Format Cells dialog box opens.

3. Click the Number tab.

4. Click the Date category in the Category list. On the right, you see a date in the Sample box and a list of date types (see Figure 9.8).

   Figure 9.8. Date format options in the Format Cells dialog box.

   

5. In the Type list, click the seventh date format ( Mar-01 ) in the list. In the Sample box, Excel shows you what a sample date would look like formatted with that type.

6. Click OK. You should see the formatted date in the selected cell.

Creating Custom Date Formats

If Excel doesn't have a preformatted date format that suits your needs, you can create your own date format. To do so, choose the Custom category on the Number tab in the Format Cells dialog box. Excel displays format codes in the Type list on the right. Choose one of the date format codes as a starting point for your custom date format. Then make your changes to the format you selected.

You can reuse all modified custom date formats within a worksheet without retyping them. Excel stores all customized date formats at the bottom of the Type list. That way, you can select them again and again. However, if you create a custom date format for one workbook, you have to re-create the format for other workbooks.