Section 10.2. Manipulating Dates and Times

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.

---

Tip: The fact that the PC version of Excel uses a different date format than the Mac version of Excel won't cause a problem when you transfer files, because Excel is smart enough to adjust to the difference between the two formats. However, there are a few rare cases where it could be trouble. First, if you cut-and-paste between Excel files that use different date systems, you could get some glitches. And if you use General format on your dates and then transfer the files, you could wind up with errors. These cases aren't commonbut if they affect you, be on the lookout.

---

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.

Table 10-2. The Internal Representation of Dates and Times

What You See	What Excel Sees
January 23, 2005	38375
12:05 PM	0.503472
January 23, 2005 12:05 PM	38375.503472