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 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)