DATEDIF Function – What is it and How to Use it

When you have to find out your exact age or exact date difference in Excel from one date to another date, you use the DATEDIF function. As such the Excel sheets have an in built formatting to detect dates that are being entered into the sheet. These dates can remain static or can change according to the particular day’s date.

Be it Dd/mm/yyyy or mm/dd/yyyy, both the formats are detected by the sheet. So if you enter 10/08/2011, the sheet will automatically read that as 10th Aug 2011. To create dynamic date that changes automatically use =DATE() function. And Ctrl+; will enter the current date automatically, which WILL NOT change.

DATEDIF function

To calculate differences in two dates the DATEDIF function can be used. For example, open a new workbook and in the A1 cell enter an earlier date (say, 10/10/1986). Then in A2 enter a recent date (Say, 10/8/2011). To find the difference in days, a simple =A2-A1 will give you the answer as 9070 days. But to get answer in no. of years and months, DATEDIF can help.

=DATEDIF(start_date, end_date, “interval”) is the syntax. Start date is an earlier date and end date is a more recent date. The end date should always be a later date than start date. Else you will get a ‘#VALUE!’ error.
The interval has 5 different parameters. If you want answer in days, enter “d”. For months, “m”, years, “y”, for so-and-so months and so-and-so days, “md” and for so-and-so years and so-and-so months, “ym”.
In the worksheet, taking 1/10/1986 as start date, 10/8/2011 as end date, in cell A4 enter the syntax =DATEDIF(A1,A2, “d”) for number of days, =DATEDIF(A1,A2, “m”) for no. of months, =DATEDIF(A1,A2, “y”) for no. of years, =DATEDIF(A1,A2, “md”) for no. of months and days, =DATEDIF(A1,A2, “ym”) for no. of years and months.
Purpose

This function can be useful in calculating age of just anything. It is accurate and you can save that sheet for using the formula for many other cases.

Bugs

Sometimes your excel sheet might not show the datedif function at all. Or it may keep giving you a #NAME? error. For that, you need to enter your dates as date values. In cell A1 and A2, enter the two dates in the =DATEVALUE(yyyy/mm/dd) form, respectively. You will get series of numbers as 31695 and 40824 correcpoding to 1986/10/10 and 2011/08/10, respectively. Now you can follow the same datedif(a1, a2, “d”) and so on and you will get the right outputs. Your outputs for days, months, years, md and ym will be 9070,298,24,0 and 10, respectively.

You can also use DATE function =DATE(tear, month, day).

Instead of getting all this as single cells, you can perform the same operation in one single function. The syntax for that concatenating function.

So if you want to know the exact age on 10th Aug 2011, using the excel date difference formula you can read the output as 24 yrs, 10 months and 0 days.

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.