Objective 12. Enter Dates and Clear Formats

2017-11-03 09:05:06

Dates are a type of value that you can enter in a cell. When you enter a date, Excel assigns a serial value to the date. This makes it possible to treat dates like other numbers. For example, if two cells contain dates, you can find the number of days between the two dates by subtracting the older date from the more recent date.

The contents of a cell and the format of a cell are different. The difference is particularly apparent with dates. The content of the cell is a number that can be represented in a variety of common date formats. If you clear the formatting of a cell that contains a date, you can see its content, which is a number.

Deleting the contents of a celleither a value or a formuladoes not remove the cell's format. Once applied, cell formats stay with the cell, even if you delete the contents of the cell. To clear the format from a cell, you must purposely clear the format.

Activity 1.21. Entering and Formatting Dates

Date values entered in any of the following formats will be recognized by Excel as a date:

Format	Example
m/d/yy	7/4/05
d-mmm	4-Jul
d-mmm-yy	4-Jul-05
mmm-yy	Jul-05

On your keyboard, (the hyphen key) and (the forward slash key) function identically in any of these formats and can be used interchangeably. You can abbreviate the month name to three characters or spell it out. You can enter the year as two digits, four digits, or even leave it off. When left off, the current year is assumed but does not display in the cell. A two-digit year value of 30 through 99 is interpreted by the Windows operating system as the four-digit years of 1930 through 1999. All other two-digit year values are assumed to be in the 21st century. Get in the habit of typing year values as four digits, even though only two digits may display in the cell. In that manner, you can be sure that Excel interprets the year value as you intended. See the table in Figure 1.74 for examples.

Figure 1.74. How Excel Interprets Dates
Date Typed As:	Completed by Excel As:
7/4/05	7/4/2005
7-4-96	7/4/1996
7/4	4-Jul (current year assumed)
7-4	4-Jul (current year assumed)
July 4	4-Jul (current year assumed)
Jul 4	4-Jul (current year assumed)
Jul/4	4-Jul (current year assumed)
Jul-4	4-Jul (current year assumed)
July 4, 1996	4-Jul-96
July 2005	Jul-05
July 1996	Jul-96

In this activity, you will type a date for one of the delivery periods that was missing in the original worksheet and format it to match the other dates. You will also clear formatting.

1.	Click to make the Austin #1 worksheet the active sheet, and then click cell B18. Notice that the cell indicates 3/30 (March 30), and then look up at the Formula Bar and notice that the full dateMarch 30, 2007displays in the format 3/30/2007. Press and hold down , and then press (the semicolon key) on your keyboard. Press to confirm the entry. Excel enters the current date, obtained from your computer's internal calendar, into the selected cell using the m/d format that was previously applied to that cell. is a convenient keyboard shortcut for entering the current date.
2.	Click cell A8, type 2/24/2007 and then press . The date right aligns in the cell and displays as 2/24/2007, using the m/d/yyyy format. Alert!: The date does not display as 2/24/2007? The Windows setting in the Control Panel under Regional and Language Options determines the default format for dates. If your result is different, it is likely that the formatting of the default date was adjusted on the computer at which you are working.
3.	Click cell A8 again, type 2/24/07 and press . Because the year 07 is less than 30, Excel assumes a 21st century date and changes 07 to 2007 to complete the four-digit year. Typing 98 would result in 1998for two-digit years that you type that are between 30 and 99, Excel assumes a 20th century date.

4.	Click cell A7 and then, on the Standard toolbar, click the Format Painter button . Click cell A8, and notice that the date format from cell A7 is copied to cell A8. Compare your screen with Figure 1.75. Figure 1.75.

Activity 1.22. Clearing Cells and Using Undo and Redo

Recall that a cell has content and a format. You can choose to clear the content, the format, or both. You can cleardeletethe contents of a selected cell in one of two ways: Press the key or use the Clear command from the Edit menu.

Clearing the contents of a cell with the key deletes the value or formula typed there, but it does not clear formatting attached to a cell. In this activity, you will clear the contents of a cell and then clear the format of a cell that contains a date to see its underlying content.

1.	Click cell A15, display the Edit menu, point to Clear, and then on the displayed submenu, point to Contents. Notice that next to Contents there is an alternate, Del, which indicates that the key can also be used.
2.	Click Contents to delete the contents of the cell. Click cell A1, press , and notice that although the contents are deleted, the yellow format is not.
3.	On the Standard toolbar, click the Undo button . Alternatively, you can press on the keyboard to reverse the last action. Display the Edit menu, point to Clear, and then on the displayed submenu, click Formats. Clearing the Formats deletes formatting from the cellin this instance, the yellow formattingbut does not delete the cell's contents.

4.	Click cell A8. Display the Edit menu, point to Clear, and then on the displayed submenu, click Formats. The date, February 24, 2007, is stored as a serial number that indicates the number of days since January 1, 1900. This date is the 39,137th day since the reference date of January 1, 1900.
5.	On the Standard toolbar, click the Undo button . Alternatively, you can press on the keyboard to reverse the last action.
6.	Click cell B8. Type 220 and then press . Type 10.5 and press . Type 2.15 and press . Notice in cell E12 that the new total for Austin #1 is recalculated as 163.08. Compare your screen with Figure 1.76. Figure 1.76.
7.	On the Standard toolbar, click the Save button to save your changes.

[Page 659 (continued)]

Objective 12. Enter Dates and Clear Formats

Objective 13 Use a Summary Sheet

Категории