Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

Where’s the DATEDIF function?

In several places throughout this chapter, I refer to the DATEDIF function. You may notice that

this function does not appear in the Insert Function dialog box, is not listed in the Date & Time

drop-down list, and does not appear in the Formula AutoComplete list. Therefore, to use this

function, you must always enter it manually.

The DATEDIF function has its origins in Lotus 1-2-3, and apparently Excel provides it for

compatibility purposes. For some reason, Microsoft wants to keep this function a secret. You won’t even

find the DATEDIF function in the Help files, although it’s available in all Excel versions. Strangely,

DATEDIF made an appearance in the Excel 2000 Help files but hasn’t been seen since.

DATEDIF is a handy function that calculates the number of days, months, or years between two

dates. The function takes three arguments: start_date, end_date, and a code that represents the

time unit of interest. Here’s an example of a formula that uses the DATEDIF function (it assumes

cells A1 and A2 contain a date). The formula returns the number of complete years between

those two dates.

=DATEDIF(A1,A2,”y”)

The following table displays valid codes for the third argument. You must enclose the codes in

quotation marks.

Unit Code

Returns

“y”

The number of complete years in the period.

“m”

The number of complete months in the period.

“d”

The number of days in the period.

“md”

The difference between the days in start_date and end_date. The months and years

of the dates are ignored.

“ym”

The difference between the months in start_date and end_date. The days and years

of the dates are ignored.

“yd”

The difference between the days of start_date and end_date. The years of the dates

are ignored.

The start_date argument must be earlier than the end_date argument, or the function returns an

error.

Here’s a similar formula that returns the day of the year for the current date:

=TODAY()-DATE(YEAR(TODAY()),1,0)