# How to Use DATEDIF Function in Excel – 6 Examples

The** DATEDIF** function is made to make it easier for us to figure out how much time has passed between two dates. The number of days, months, or years among two dates can be easily determined using the **DATEDIF** function. The Excel Date/Time functions family include the **DATEDIF** function.

## Syntax of DATEDIF Function

`=DATEDIF(date1, date2, unit)`

## Argument of DATEDIF Function

*date1- **the beginning of the time period you would like to calculate.*

*date2**– end date of the time span you want to calculate*

*unit**– defines the **unit** of time or how you want to figure out how many dates has passed between two dates*

*Please see the table below:*

**Notes: **

**date1**and**date2**can be dates or the equivalent number of dates according to the**1900**system of Excel.**date1**should be any preceding date of**date2**. Otherwise, the**#NUM!**error will display in the output cell.

## Return of DATEDIF Function in Excel

Based on the unit provided in the argument section, the **DATEDIF** function returns the difference between the start and end dates of a particular time span.

### Example #1: Using the DATEDIF Function, Count Time Difference in Terms of Date, Month, or Year

In this example, we will count the difference between the dates given in the dataset below.

Apply the following formula to find the difference according to the units provided

`=DATEDIF(B3,C3,"D") //returns date difference between two dates`

`=DATEDIF(B3,C3,"M") //returns month difference between two dates`

`=DATEDIF(B3,C3,"Y") //returns year difference between two dates`

**Explanation:**

Focus solely on** row 6**, the **#NUM!** error was returned because the end date was earlier than the start date.

### Example #2: Application of the DATEDIF Function to Count Time Difference Excluding Date, Months, or Year.

With reference to the previous example, apply the following formula to get the time difference excluding the date, month, or year.

`=DATEDIF(B3,C3,"MD") //returns date difference excluding month and year`

`=DATEDIF(B3,C3,"YD") //returns date difference excluding year `

`=DATEDIF(B3,C3,"YM") //returns date difference excluding year and days`

### Example #3: Ignoring #NUM! Error Using the DATEDIF Function

You may notice that the **#NUM! **error returned in the examples above. In this example, let’s find a solution to this problem.

Use the** IF function** to check whether the “**Start Date**” is preceding or succeeding the “E**nd date**”.

`=IF(B3>C3,DATEDIF(C3,B3,"D"), DATEDIF(B3,C3,"D"))`

**Explanation:**

- According to the argument of the
**DATEDIF**function, the**Start Date**should be earlier than the**End Date**. - The
**IF**function checks the condition**B3>C3**. Where**B3>C3**means the ‘’**Start date**’’ later than the ‘’**End Date**’’. - If
**B3>C3**satisfies, then the IF function triggers the**DATEDIF(C3,B3,”D”)**function to count the date difference. - In the formula
**DATEDIF(C3,B3,”D”)**– the value of cell**C3**is provided as the start date and**B3**as the end date. As the Start Date and the End Date swapped, the**#NUM!**Error eliminated. - If
**B3>C3**is not satisfied, then the IF function triggers the function**DATEDIF(B3,C3,”D”)**to count the date difference.

### Example #4: Counting Weeks of Time Span Using the DATEDIF Function

If we divide the counted date by seven, then we will have the week difference.

But, the result can have some fractional value. So, we will use the **ROUND** function, which will round the result to the nearest value of zero.

`=ROUND(DATEDIF(B3,C3,"D")/7,0)`

### Example #5: Getting the time Difference of Date, Month, and Year in a Single Formula.

Now, we want to calculate the time difference in terms of date, month, and year using a single formula, and the output will be displayed in a single cell.

`=DATEDIF(B4, C4, "md") &" days, "&DATEDIF(B4, C4, "ym") &" months, "&DATEDIF(B4, C4, "y") &" years"`

Now, We want to eliminate the texts like **0 years**, **0 months**, or **0 days**

The following formula will return just non-zero elements

`=IF(DATEDIF(B3,C3,"md")=0,"",DATEDIF(B3,C3,"md")&" days ")&IF(DATEDIF(B3,C3,"ym")=0,"",DATEDIF(B3,C3,"ym")&" months ")&IF(DATEDIF(B3,C3,"y")=0,"",DATEDIF(B3,C3,"y")&" years ")`

### Example #6: Make an Age Calculator Using the DATEDIF Function

In general, to calculate age, we have to find the difference between the present date to the birth date.

To pick the present date, we will use the **YEAR** function as below:

`=DATEDIF(B3,TODAY(),"Y") //returns the year difference from the birth date to the present date.`

where **B3** is the cell containing the birth date.

Now, we want to create a formula to return the age in terms of date, month, and year.

`=DATEDIF(B3,TODAY(),"y") & " years, " & DATEDIF(B3,TODAY(),"ym") & " Months, " & DATEDIF(B3,TODAY(),"md") & " Days"`

## Things to Keep in Mind about DATEIF Function:

Excel emits the** #Value! **error when it fails to identify the date provided due to the failure to recognize the data argument.

**#NUM! error **– This emerges when the start date in the argument exceeds the end date.

## Difference Between DATEDIF Function and MONTH, YEAR Function

If you want to calculate the month difference between two dates, you can simply apply the **DATEDIF **function as below.

`=DATEDIF(B3,C3,"M")`

The same result can be obtained by using the **YEAR** and **MONTH** functions as below:

`=(YEAR(C3) - YEAR(B3))*12 + MONTH(C3) - MONTH(B3)`

The difference result is provided for **rows 6** and **7**.

The **YEAR **and **MONTH **functions don’t follow any preceding rule. Unlike the **DATEDIF** function, the **YEAR** and **MONTH** functions just return negative or positive values.

The **DATEDIF** function counts a complete chronological calendar. In** row 7**, the date difference is **178**. As the complete six months are not covered yet, five months are returned by the **DATEDIF** function.

But the **YEAR **and **MONTH** functions don’t care about a complete chronological calendar. as the dates stay six months apart. So, the return is six.

https://www.Waste-ndc.pro/community/profile/tressa79906983/Thanks for your personal marvelous posting! I truly enjoyed reading it, you may be a great author.

I will ensure that I bookmark yoyr blog and will come back later

on. I want to encourage one too continue youur greatt work,have a nice

day! https://www.Waste-ndc.pro/community/profile/tressa79906983/