10.2. Manipulating Dates and Times

To understand how you can use Excel's functions to manipulate dates and times, you need to understand a little more about how Excel stores these values. The reality is that even though many dates look, at least partly, like text entries (for instance, Mar. 5, 2005 ), Excel actually stores them as serial numbers . Under this system, the date January 1, 1900 is designated as day 1; January 2, 1900 is designated as day 2; and so on.

Thus, if you use Excel to subtract one date from another, you actually end up calculating the difference in days, which, it turns out, is exactly what you want. But this system of date storage leads to some interesting side effects. For example, you can't enter a date in Excel that's earlier than January 1, 1900. Try itif you do, your date will be treated just like text.

Excel also supports an alternate date system where January 1, 1904 is designated as day 1. This format is included to ensure compatibility with the Macintosh version of Excel, which uses that date system. To change your worksheet to use dates that start at 1904, select Tools Options from the menu, select the Calculation tab, and then choose the checkbox labeled "1904 date system." Dates won't look any different on your worksheet, but their internal representation will have changed, and you won't be able to enter a date earlier than January 1, 1904 without Excel's converting it to plain text.

Ordinarily, you won't see these underlying serial values because Excel always displays dates using the Date number format. However, you can take a look at the underlying number by changing the format of any cell that contains a date. For example, type 1/1/2005 into a worksheet, and then right-click the cell and choose Format Cells. In the Category list, choose General instead of Date. Now you'll see the number 38353 instead of the text date. (Incidentally, you can also perform the reverse trick to display a normal number as a date, although doing this doesn't usually make much sense.) For more information about formatting cells and dates, see Chapter 4.

Excel also stores times as numbers behind the scenes. In fact, Excel stores every time value internally as a fractional number from 0 to 1. The number 0 represents 12:00 AM (the start of the day) and 0.999 represents 11:59:59 PM (the end of the day). Because Excel stores times as a single number, it's easy to subtract one time value from another. However, time values can have varying degrees of precision. If your time is accurate down to the millisecond, it will include more decimal places (up to eight). For example, Excel stores 10:30 AM as 0.4375; whereas 10:30:32.34 is 0.437874306.

So now that you know dates are really whole numbers and times are fractions, what happens if you enter a number like 38353.50 and apply the Date number format? In this case, the whole number part (38353) is used for the date, and the fractional part (0.50), is used for the time. Excel therefore interprets 38353.50 to mean 38,353 from January 1, 1900. Thus, the resulting value is the combined date and time of 1/1/2005 12:00:00 PM (see Table 10-2). If you change the number to 38353.40 , you'll end up with 1/1/2005 9:36:00 AM. At this point, you're probably realizing that there's really no difference between dates and timesthey're just different components of a single number.

What You See | What Excel Sees |
---|---|

January 23, 2005 | 38375 |

12:05 PM | 0.503472 |

January 23, 2005 12:05 PM | 38375.503472 |

Excel 2010: The Missing Manual

ISBN: 1449382355

EAN: 2147483647

EAN: 2147483647

Year: 2003

Pages: 185

Pages: 185

Authors: Matthew MacDonald

Similar book on Amazon

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net