Excel DATEDIF Function (6 Practical Examples)

Excel DATEDIF Function

The Excel DATEDIF (DATE+DIF) function returns the difference between two dates in terms of years, months, or days. This function is useful for analysis with days, months, and years. Studies that need to find the impact of difference between dates require to use Excel DATEDIF Function. For example, in calculating age, users need to use DATEDIF Function.

In this section:

---

1. Syntax of DATEDIF Function
2. Example 1: Count number of days between two dates
3. Example 2: Count number of months between two dates
4. Example 3: Count number of years between two dates
5. Example 4: Count number of days between two dates, ignoring month and year
6. Example 5: Count number of month between two dates, ignoring year and day
7. Example 6: Count number of days between two dates, ignoring year.

---

1. Syntax of DATEDIF Function:

DATEDIF(start_date,end_date,unit)

where:

• start_date: The start date, which can be a text strings within quotation marks (for example, “2001/1/30”), a serial numbers (for example, 36921, which represents January 30, 2001, if you’re using the 1900 date system), or the results of other formulas or functions (for example, DATEVALUE(“2001/1/30”)).
• end_date: The date that represents the last, or ending, date of the period, and the format can as they are in the first argument.
• unit:

Unit	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.Important: We don’t recommend using the “MD” argument, as there are known limitations with it. See the known issues section below.
“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.

2. Count the number of days between two dates:

To count the number of days between two dates, the formula is =DATEDIF(C6, D6, “d”), which returns the difference between dates in terms of days.

3. Count the number of months between two dates:

To count the number of months between two dates, the formula is =DATEDIF(C6, D6, “m”), which returns the difference between dates in terms of months.

4. Count the number of years between two dates:

To count the number of Years between two dates, the formula is =DATEDIF(C6, D6, “y”), which returns the difference between dates in terms of years.

5. Count days between dates, ignoring month and year:

To count the number of days between two dates, ignoring the month and year, the formula is =DATEDIF(C6, D6, “md”), which returns the difference between dates in terms of days, ignoring moth and year.

Note: The “MD” argument may result in a negative number, a zero, or an inaccurate result; therefore, be careful in the use of “MD” argument.

6. Count the number of month between dates, ignoring Year and Day:

To count the number of months between two dates, ignoring the year and day, the formula is =DATEDIF(C6, D6, “dy”), which returns the difference between dates in terms of months, ignoring year and day.

7. Count number of days between two dates, ignoring year:

To count the number of days between two dates, ignoring the year, the formula is =DATEDIF(C6, D6, “yd”), which returns the difference between dates in terms of days, ignoring year.

More related readings:

1. MS Office post on DATEDIF Function
2. Excel DATE Function
3. Excel CONCAT Function
4. Extract first Name in excel (3 practical examples)
5. Extract Middle names (3 practical examples)
6. Excel ARRAYTOTEXT Function
7. Excel CLEAN Function with practical examples
8. Excel CONCAT Function to join text
9. Excel REPLACE Function to replace text
10. Excel TRIM Function with Practical Examples
11. Excel RIGHT Function
12. How to generate tomorrow’s date in Excel