Jump to content

How Excel stores date and time values

  sarahkim's Photo
Posted Jun 30 2010 10:49 AM

This excerpt from Excel 2010: The Missing Manual explains how Excel stores date and time values so you can use Excel's functions to manipulate them.

The reality is that even though many dates look, at least partly, like text entries (for instance, Mar. 5, 2010), Excel actually stores them as serial numbers. Under this system, Excel designates the date January 1, 1900 as day 1; January 2, 1900 is 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 it—if you do, Excel treats your date just like text.

Excel also supports an alternate date system where it designates January 1, 1904 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 File→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 common—but 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/2010 into a worksheet, right-click the cell, and then choose Format Cells. In the Category list, choose General instead of Date. Now you'll see the number 40179 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.)

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 40179.50, and then apply the Date number format? In this case, Excel uses the whole number part (40179) for the date, and the fractional part (0.50) for the time. Excel therefore interprets 40179.50 to mean 40,179 days from January 1, 1900. Thus, the resulting value is the combined date and time of 1/1/2010 12:00:00 PM (see image below). If you change the number to 40179.40, you end up with 1/1/2010 9:36:00 AM. At this point, you're probably realizing that there's really no difference between dates and times—they're just different components of a single number.

The internal representation of dates and times

Attached Image

Excel 2010: The Missing Manual

Learn more about this topic from Excel 2010: The Missing Manual.

Fast-paced and easy to use, this guide shows you how to get the most out of Excel 2010. You'll learn how to develop a spreadsheet from scratch, create formulas, add data, and analyze and graph data so you can make informed business decisions. With clear jargon-free explanations, step-by-step instructions, tons of illustrations, and lots of undocumented tips and shortcuts, you'll get hands-on guided tours and explanations of Excel's new features.

See what you'll learn

1 Subscribe

0 Replies