[EXCEL] – DATEDIF FUNCTION IN MICROSOFT EXCEL

WHAT IS DATEDIF FUNCTION IN MICROSOFT EXCEL?

DATEDIF function in Microsoft Excel helps us to get difference of 2 dates in years or month or days. DATEDIF function is available from earlier of excel versions i.e. from excel 97 version. Microsoft removed this function in excel functions list from excel 2007 version onwards, and we can’t see this function in list.

DATEDIF function is hidden function, and this function works with out any issue.

SYNTAX FOR DATEDIF FUNCTION IN MICROSOFT EXCEL

Datedif function in microsoft excel

Datedif function in Microsoft Excel requires 3 functional arguments, all are required arguments. =DATEDIF(start_date,end_date,option)

— Start_date and end_date values should be in date format, and start_date is less than end_date.

— There are multiple options for third argument:

—-> Use “Y” for returning years value difference between two dates.

—-> Use “M” or “YM” for returning months value difference

—-> Use “D” or “MD” for returning days value difference

(check out the below video to learn more about DATEDIF function in Microsoft Excel) .

EXAMPLES OF USAGE DATEDIF FUNCTION IN MICROSOFT EXCEL

NODESCRIPTIONFORMULARESULT
1In cell A1, enter date as “01/12/1995” and enter “03/30/2020” in cell A2. Write formula to get the difference value in years=datedif(A1,A2,”Y”)25
2 In cell A1, enter date as “01/12/1995” and enter “03/30/2020” in cell A2. Write formula to get the difference value in months =datedif(A1,A2,”M”) 302
3 In cell A1, enter date as “01/12/1995” and enter “03/30/2020” in cell A2. Write formula to get the difference value in days =datedif(A1,A2,”D”) 9209
4 In cell A1, enter date as “01/12/1995” and enter “03/30/2020” in cell A2. Write formula to get the difference value in months =datedif(A1,A2,”YM”) 2
5 In cell A1, enter date as “01/12/1995” and enter “03/30/2020” in cell A2. Write formula to get the difference value in days =datedif(A1,A2,”MD”) 18

If you observe examples 2 and 4, in 2nd example only argument “M” entered, so the difference in months is 302, in 4th example given third argument “YM” result is 2. The difference between 2 examples is, if we use only “M” then will get total months difference, but if we use “YM” then it will subtract – Total Months – (Diff in Years x 12) . Like in the 4th example – Total Months = 302, Diff in Years=25, final value is = 302-(25*12)=2.

For days also, it is similar calculation, we need to enter the option depends on our need. If we want total days difference or only days excluding years and months.

TIPS & TRICKS:

  1. Use DATEDIF function for calculating difference in years, months, and days. Especially, to check a person’s age, or employee tenure in organizations etc.
  2. It is hidden function, so practice many times to remember the syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *