Microsoft Office Tutorials and References
In Depth Information
Mastering Excel’s Built-in Functions
Date and Time Functions
We can think of dozens of reasons to make date calculations in Excel worksheets. You might
want to determine a person’s age by comparing the person’s birth date to today’s date. Or
perhaps you want to look over a list of invoices and compare invoice date to payment date
to determine how long your customers typically take to pay their bills.
To master date calculations, you first have to understand how Excel stores date values.
When you enter a date in a recognized format, Excel converts that date to a serial value—a
number equal to the number of days after January 1, 1900, which has a serial value of 1.
Thus, September 29, 2010, is stored as 40450 and September 29, 2011, is 40815. Not
surprisingly, subtracting those two dates, which are exactly one year apart, produces a result
Dates in a worksheet are off by four years
As we noted at the beginning of this section, Excel 2010 (like all previous versions of
Excel developed for Windows) uses a date system based on serial numbers where the
start date is January 1, 1900. On a Macintosh, however, date serial numbers begin with
January 1, 1904. If you create a worksheet in Excel for Windows and open it in the Mac
version of Excel—or vice versa—your dates will look normal because both programs
recognize and adjust for the different date formats. However, if you paste a date value
from a worksheet created in Excel on a Mac into a new worksheet created in Excel on
a Windows PC, your results will be off by approximately four years. The moral? Be very
careful when pasting dates between worksheets if you even suspect that your original
data might have been created originally on a Mac.
INSIDE OUT What’s the serial number for a given date?
Under most circumstances, Excel handles the work of converting dates to serial values
automatically. If you enter 9/29/2011 in a cell, Excel displays it using the default date
format in the cell itself and in the formula bar. If you need to know the serial number,
you can determine it easily. Select the cell, press Ctrl+1 to open the Format Cells dialog
box, and click General. The Sample box shows the serial value. Click OK to change the
cell format or click Cancel to return to the worksheet without changing it.