Page 108 - Excel 2013 All-in-One For Dummies

90 It Takes All Kinds (Of Cell Entries)
By storing dates as serial numbers representing the number of days that have elapsed from a particular date (January 1, 1900, or January 2, 1904), Excel can perform arithmetic between dates. For example, you can find out how many days there are between February 15, 1949, and February 15, 2013, by entering 2/15/13 in one cell and 2/15/49 in the cell below, and then creat- ing a formula in the cell below that one that subtracts the cell with 2/15/49 from the one containing 2/15/13. Because Excel stores the date 2/15/13 as the serial number 41320 and the date 2/15/49 as the serial number 17944, it can calculate the difference and return the result of 23376 (days, which is equal to 64 years).
When you type a date directly into a formula that performs date arithmetic (as opposed to constructing a formula using references to cells that contain date entries), you must enclose the date in quotation marks. So, for exam- ple, if you type the dates in a formula that calculates the number of days between February 15, 1949, and February 15, 2012, in the cell you have to type the following formula:
=”2/15/12”–”2/15/49”
Times of the day are stored as decimal numbers that represent the fraction of the 24-hour period starting with 0.0 for 12:00 midnight through 0.999 for 11:59:59 p.m. By storing times as decimal fractions, Excel enables you to perform time calculations such as those that return the elapsed time (in min- utes) between any two times of the day.
Inputting dates and times using recognized formats
Although Excel stores dates as serial numbers and times as decimal frac- tions, luckily you don’t have to use these numbers to enter dates or times
of the day into cells of the worksheet. You simply enter dates by using any
of the recognized Date number formats that are used by Excel, and you
enter times by using any of the recognized Time number formats. Excel then assigns and stores the appropriate serial number or decimal fraction at the same time the program assigns the date or time format that you used for this value. Table 1-1 shows you typical date and time entries that you can use as examples when entering dates and times in the cells of a worksheet.
Table 1-1 Common Ways to Enter Dates and Times
What You Enter in the Cell
1/6/2013 1/6/13 1-6-13
Date or Time Recognized by Excel (As Displayed on the Formula Bar)
January 6, 2013 January 6, 2013 January 6, 2013