Excel DATEDIF function explained

DATEDIF is practically an insider tip, as the function is not even included in the internal list in the software. The function for calculating the difference (DIF) between two points in time (DATE) is a relic from Lotus 1-2-3, the predecessor to Microsoft Excel. DATEDIF is intended to calculate the length of a period of time and output the result in different units.

How many days are left until the deadline? How many months are left until my vacation? How many years have I been working with the customer? The major advantage of DATEDIF is not that it can calculate a difference between a start and an end date – you could do that using simple subtraction in Excel. The function lets you adapt the result unit. That means you can decide whether you want to show the corresponding time period in days, months or years.

DATEDIF calculates the time difference between a start and an end date. Both data points must be known for the function. You also have to specify the unit in the function to enable the result to be displayed.

The first three results are relatively easy to understand: no more than a year, no more than 13 months and no more than 397 days have passed between the two dates. Only full units are shown. Half years are not included in the count. However, the last three options are more interesting. YM is 1 because the year data is ignored entirely. There is just one month between January and February. MD is also just 1, due to the change from the first to the second day. The last option gives you the number of days – taking the different months into consideration – but ignores the different years.

DATEDIF is easy to implement as a form in Excel. Create one cell for the start date and another for the end date. The unit can also either be entered directly in a cell or selected from a drop-down menu.

If you want to find out how many years, months and days there are between two points in time, i.e. get three items of information in one result, the information can also be concatenated. You can do so using a function or the arithmetic operator "&".

For example, you can use this method to show your age. The spaces between the quotation marks and the actual text are required by the formula so that numbers and letters are not directly beside one another after concatenation. Another function can help keep the result up-to-date at all times: Excel always outputs the current date using the TODAY function. You can also insert it directly into the DATEDIF function. For example, this can help you calculate the length of a membership.