Microsoft Excel Question:

Download Questions PDF

Excel stores dates as serial numbers. The number 1 represents January 1, 1900, the number 2 represents January 2, 1900, and so on. Formatting these numbers using a date format causes them to appear as actual dates. Therefore, if you have dates stored in two cells, you can simply create a formula that subtracts one from the other to get the number of intervening days. You'll want to make sure that the formula cell is formatted as a number, not a date.

You might also find the DATEDIF function useful. This function, which was not documented prior to Excel 2000, returns the difference between two dates, expressed in years, months, or days. You might use the DATEDIF function to calculate how many months the payment on an invoice is overdue or to determine a person's age when you know their birth date.

Excel's DATEDIF function takes three arguments. Its syntax is:

=DATEDIF(start_date,end_date,units)

In the syntax, start_date is a date or reference to a date, end_date is a date or reference to a date, and units is a one- or two-digit string (in double quotes) specifying the units for the difference between the two dates. Acceptable values for the units argument are shown below.

* y returns the number of full years in the period.
* m returns the number of full months in the period.
* d returns the number of full days in the period.
* md returns the number of full days in excess of the last full month.
* ym returns the number of full months in excess of the last full year.
* yd returns the number of full days in excess of the last full year.

For example, assume cells A1 and B1 contain dates. The formula below returns the number of full years between the dates (useful for calculating a person's age):

=DATEDIF(A1,B1,"y")

The formula below calculates the number of full months between the two dates:

=DATEDIF(A1,B1,"m")