Excel tools to demonstrate

DATEDIF Function

Because dates are stored as numbers, it is possible to perform arithmetic on them. One particular arithmetic operation makes sense: subtraction. If you subtract one date from another, you find the number of days between them. You can do this by simple subtraction of cells, or you can get more control with the DATEDIF function. If you use simple subtraction, you automatically get the difference in days. For example, the formula =A1-A2, where A1 contains 11/25/2004 and A2 contains 11/20/2004, gives 5. The DATEDIF function provides more options. (Evidently, DATEDIF has been in Excel for years, but its documentation in online help has been sporadic. See the interesting article at http://www.cpearson.com/excel/datedif.htm.)

To use the DATEDIF function:

Enter the formula =DATEDIF(earlierdate,laterdate,interval), where interval can be “y”, “m”, “d”, or a few other options not covered here. If interval is “y” (quotes required), this returns the number of years between the two dates. Similarly, if interval is “m” or “d”, it returns the number of months or number of days between the two dates.

Try it! Use simple subtraction of cells to find the number of days between the following two dates. Then use DATEDIF with the three options described above to get the number of days, months, and years between the two dates. (Note: You’ll have to reformat the answer in cell B6 as a number. Excel wants to format it as a date for some odd reason. Scroll to the right for the answers.)