Section 11.2. Manipulating Dates and Times

11.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, 2007 ), 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, Excel treats your date 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 Office button Excel Options, and then choose the Advanced section. Scroll down the page of settings until you see the heading "When calculating this workbook. Underneath, turn on the checkbox next to "Use 1904 date system". Dates don't look any different on your worksheet, but their internal representations have changed, and you can't enter a date earlier than January 1, 1904 without Excel 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's smart enough to adjust to the difference between the two formats. However, in a few rare cases, 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/2007 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 39083 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 date values, see Chapter 5.

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's accurate down to the millisecond, it includes 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 39083.50 and apply the Date number format? In this case, the whole number part (39083) is used for the date, and the fractional part (0.50), is used for the time. Excel therefore interprets 39083.50 to mean 39,083 from January 1, 1900. Thus, the resulting value is the combined date and time of 1/1/2007 12:00:00 PM (see Table 11-2). If you change the number to 39083.40 , you end up with 1/1/2007 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 11-2. The Internal Representation of Dates and Times

What You See	What Excel Sees
January 23, 2007	39083
12:05 PM	0.503472
January 23, 2007 12:05 PM	39083.503472