The DATEDIF function

Welcome back to our usual blog of Excel functions from A to Z. Today we look at the DATEDIF function.

The DATEDIF function

This function has been described in the past MVP Chip Pearson as “… the drunk cousin of the formula family …”. The DATEDIF The function calculates the number of days, months, or years between two dates. Excel provides DATEDIF function to support older Lotus workbooks 1-2-3 and it can calculate incorrect results in certain scenarios (look down). Use DATEDIF, you must type the function manually; it may not appear to be recognized by Excel.

The DATEDIF The function uses the following syntax to operate:

The DATEDIF The function has the following arguments:

• start date: this is the date that represents the first date or the start date of the period. Dates can be entered as text strings in quotes (as an example, “17 from September 1967”), as serial numbers (as an example, 36921, that represents the 30 of January of 2001, if you use the date system 1900) or as the results of other formulas or functions (as an example, DATEVALUE (“1 from January 2000”))
• final date: this is the date that represents the last date or the end date of the period
• break: this must be entered and specifies whether the function should return the number of days (“d”), complete months (“m”) or complete years (“Y”) between the two specified dates. the interval syntax is strict: the letters must be entered in quotes. In reality, the break the argument can also contain a combination of days, months, and years to increase the variety of results returned by the function. As an example:
  • “ym”: calculates the number of complete months between two dates as if they were in the same year
  • “yd”: calculates the number of days between two dates as if they were in the same year
  • “md”: calculates the number of days between two dates as if the dates were in the same month and year. Be careful with this option: Microsoft knows there are issues with this combination and does not recommend relying on its results break.

Beware of two common error messages with this tool:

#VALUE! appears in the response cell if one of the DATEDIF arguments is not a valid date (p.ej the date was entered as text)

• #ON ONE! occurs in the result cell if the start date is largerIn other words later in the year) that he final date argument.

It should be noted at the same time that:

• dates are stored as sequential serial numbers so they can be used in calculations. By default, the 1 of January of 1900 is the serial number 1 and the 1 of January of 2008 is the serial number 39448 because it is 39,447 days after 1 of January of 1900.
• The DATEDIF The function is useful in formulas where you need to calculate an age.

Please, see my example below:

Known issues

The argument “md” may result in a negative number, a zero or an inaccurate result. If you are trying to calculate the remaining days after the last completed month, you may need to go back to basic algebra using the DÍA Y MONTHS (this determines the end of the month so many months from the given date) works instead:

Soon we will continue with our functions from A to Z of Excel. Keep checking: there is a new blog post every business day.

You can find a full page of the feature posts here.