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 compat-
ibility 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)
=DATE(YEAR(A1),12,31)-A1
When you enter either of these formulas, Excel applies date formatting to the cell. You need to
apply a non-date number format to view the result as a number.
To convert a particular day of the year (for example, the 90th day of the year) to an actual date
in a specified year, use the formula that follows. This formula assumes that the year is stored in
cell A1 and that the day of the year is stored in cell B1.
=DATE(A1,1,B1)