Page 19 - Excel Data Analysis

02 537547 Ch01.qxd 3/4/03 11:45 AM Page 5

GETTING STARTED WITH EXCEL 1

NUMERIC (CONTINUED)

Dates and Time If you decide to use two-digit dates in Excel, you must
Excel uses the Western, formally called Gregorian, exercise caution when entering them. Excel interprets
calendar as a basis for all dates and times, and stores two-digit years between 00 and 29 as the years 2000
them as a combined number. Dates are all sequential, though 2029. Excel interprets two-digit years between
whole numbers from 1 to 2958465. Excel stores times, 30 and 99 as 1930 to 1999. To avoid errors, consider
which are all portions of the dates, as decimal values. always using a four-digit year.
For example, if you type the value 12/05/02 4:00 PM in a Time
cell, Excel stores it as the numeric value 37595.66667,
where 37595 represents the date portion, and .66667 Excel stores all time values as decimal values between 0
represents the time. and 0.99999999, with 12:00 midnight being 0, and
11:59:59 PM being 0.99999999. So a time that displays as
You can apply any mathematical calculations to 12:00 P.M. (noon) has a value of 0.5.
compare and manipulate dates and time. For example,
you can add, subtract, or determine the elapsed time By default, Excel bases all times on a 24-hour clock,
between two dates and times. The cell's formatting commonly known as military time. This means that if
determines how the date or time value displays. you enter 10:30 without an A.M. or P.M., Excel assumes
you mean 10:30 A.M. If you want 10:30 P.M., enter P.M.
Dates
after the time, or use the corresponding 24-hour clock
Although a date displays in a cell on your worksheet, value of 22:30.
Excel actually stores its numeric equivalent. Using the
Western calendar, Excel determines the number of days Formula
in each month. For example, January always has 31 days, You can create formulas within any cell of a worksheet
and February has 28 days with the exception of leap year. to evaluate data values in other cells within your
worksheet. For example, the following formula adds the
Excel for Windows bases all dates on what is commonly numeric values in cells A1 through A10 and displays the
referred to as the 1900 date system, which recognizes total in the cell containing the formula.
1/1/1900 as the first date with a stored value of 1. The
last date that Excel recognizes is December 31, 9999 or =SUM(A1:A10)
12/31/9999, which it stores as 2958465. If you use Excel
on a Macintosh computer, dates are based on a 1904 You must always precede formulas with an equal sign,
date system, which means 1/1/1904 has a value of 1 and which signals that what follows is a formula that Excel
12/31/9999 has a value of 2957003. needs to evaluate. You can use any of the built-in
functions, mathematical operators, constant values, and
Although the two operating systems use different date cell references to create a formula. Although you can
systems, you can convert them when moving use any combination of elements in a formula, the total
worksheets between a Windows and a Macintosh number of characters in the formula cannot exceed
computer. If you open a Macintosh-created Excel 1,024 characters.
(version 2.0 or later) worksheet in Windows, the dates
automatically convert to the 1900 date format. Likewise, Excel formats a cell based upon the resulting value of
opening a Windows-created worksheet on a Macintosh the formula. For example, most formulas return a
converts dates to the 1904 system. You can also numeric value; therefore, by default, Excel right-justifies
manually force the date conversion in Excel for the returned value of numeric formula cells like other
Windows by selecting the 1904 date system option on numeric cells. See Chapter 4 for more information
the Calculation tab of Options dialog box. about creating formulas in your worksheets. For more
on formula basics, see Appendix D.

5