Calculating Totals Between Dates

Examples:

Formula                                                                        Answer

Excel 2000 offers the DATEDIF function, which takes the form =DATEDIF(beginning_date,ending_date,unit_of_measure) to calculate the difference in time between two dates.

The DATEDIF's first two arguments are the beginning and ending dates of the period in question. The usual rules apply to the kinds of dates you can supply. The options include the results of other formulas or functions such as NOW and DATEVALUE, text strings in the form YYYY/MM/DD, or values such as 36807 (New Year's Day on the 1900 date system).

The third argument, the unit of measure argument tells Excel what calculation you want based on the dates you supply. The options for the unit argument are Y, M, D, MD, YM, and YD.

They return:

* Y: Complete years in between the dates specified

* M: Complete months in between the dates specified

* D: Complete days in between the dates specified

* MD: Number of days between the first and second dates, without regard to month or year

* YM: Number of months between the dates, without regard to day or year

* YD: Number of days between the dates, without regard to the year

For instance, the expression =DATEDIF("2002/09/01","2002/10/01","D") returns 30, but the expression =DATEDIF("2002/09/01","2002/10/01","MD") returns 0 because the argument MD tells Excel to subtract the second date from the first date, without regard to month or year. The expression =DATEDIF("2002/09/01","2002/10/01","Y") also returns 0; this is because the two dates do not span at least one complete year.

Examples:

Formula                                                                        Answer