**DATEDIF **is one of the most used Excel functions. In this article, you’ll learn how you can use this **DATEDIF **function efficiently with different **criteria **and by combining it with other **functions **in Excel.

## Download Practice Workbook

You are welcome to download the practice workbook from the link below.

## Introduction to DATEDIF Function

**Summary:**

**The DATEDIF function**returns the difference of days, months or years between two dates, based on three arguments, one**starting end**, one**ending date**and one argument called**unit**.- The
**unit**argument tells Excel whether the difference of days, months or years is to be provided. - The
**DATEDIF**function includes only the**ending date**while counting, not the**starting date**. - However,
**DATEDIF**is a hidden function of Excel. That means you can use it, but Excel will not help you by providing any syntax or short notes while you are typing it.

**Syntax:**

`=DATEDIF(start_date,end_date,unit)`

**Arguments:**

Argument |
Required/Optional |
Value |

start_date | Required | The date from where you want to count the difference. It can be a single date or an array of dates. |

end_date | Required | The date where you want to stop counting. Additionally, it also can be a single date or an array of dates. |

unit | Required | A text value that tells Excel whether the difference of days, months or years is to be returned. |

**DATEDIF**counts a total of six different types of differences. It depends on the argument**unit**.

Unit |
Difference Type |

“d” | Days between two dates. |

“m” | Months between two dates. |

“y” | Years between two dates. |

“md” | Days between two dates ignoring months and years. |

“ym” | Months between two dates ignoring years. |

“yd” | Days between two dates ignoring years. |

- It includes only the
**ending date**, not the**starting date**while counting. - The
**DATEDIF**function has the**Array Formula**for both the**start_date**and the**end_date**arguments. That means, it can accept an array of dates in place of a single date in the**start_date**argument and can return the output for each of the**start_dates**together. Same for the**end_date**argument.

**Return Value:**

It returns a total of six different types of outputs.

- Days between two dates.
- Months between two dates.
- Years between two dates.
- Days between two dates ignoring the months and years.
- Months between two dates ignoring the years.
- Days between two dates ignoring the years.

## 2 Ideal Examples Utilizing Excel’s DATEDIF Function

We can use the **DATEDIF **function in Excel to count **days**, **months **or **years **between **two days**. Additionally, it can be used to **calculate age **from a given **birthdate **combined with the **TODAY **function.

### 1. Calculate Difference Between Two Dates Using DATEDIF Function

Here, we have the **Start Date **and **End Date **of six employees of a company. Now, we will show you how you can **calculate **the **difference **between these **two dates **using the **DATEDIF **function.

In the function, we will insert the value of the **unit **on the basis of which **parameter **we want to get.

#### 1.1 Determine Number of Days

Firstly, we will show you how you can determine the number of **days **between **two dates **using the **DATEDIF **function.

**Steps:**

- In the beginning, select
**Cell D5**and insert the following formula.

`=DATEDIF(B5,C5,"d")`

- Then, press
**Enter**. - After that, drag-down the
**Fill Handle**tool to**autofill**this formula for the rest of the cells.

**DATEDIF**function, we inserted

**Cell B5**as

**start_date**,

**Cell C5**as

**end_date**and

**“d”**as

**unit**.

- Thus, you can get the
**days**between**two dates**using this function.

#### 1.2 Count Number of Months

Secondly, here you will find a way to count the number of months between two dates.

**Steps:**

- Firstly, select
**Cell D5**and insert the following formula.

`=DATEDIF(B5,C5,"m")`

- Then, press
**Enter**. - After that, drag-down the
**Fill Handle**tool to**autofill**this formula for the rest of the cells.

**start_date**and

**end_date**like the previous example. Then, we inserted

**“m”**as the unit to find the

**month**between the

**dates**.

- Thus, you can get the
**month**differences between**two dates**.

**Note:** When the day of the **end_date** is less than the day of the **start_date**, it doesn’t count the** last month**.

#### 1.3 Determine Number of Years

Similarly, we can determine the number of **years **between **two dates **by just changing the **unit **value.

**Steps:**

- To start with, insert the following formula in
**Cell D5**.

`=DATEDIF(B5,C5,"y")`

- Then, press
**Enter**and lower the cursor to fill the rest of the data cells.

**unit**argument is

**“y”**. So, the function returns the plane difference of

**years**between the

**starting date**and the

**ending date.**

- As a result, we will have a difference in
**years**unit.

**Note:** When the month of the **end_date** is less than the month of the **start_date**, it doesn’t count the** last year**.

#### 1.4 Calculate Number of Days Ignoring Years

In this demonstration, we will use the **DATEDIF **function to calculate the difference between **two dates **in **days**, ignoring **years**.

**Steps:**

- Firstly, choose the
**Cell D5**and type the following formula.

`=DATEDIF(B5,C5,"yd")`

- After that, press
**Enter**and lower the cursor to the last data cell to get results according to the formula.

**days ignoring years**between two days, we inserted

**“yd”**as the

**unit**.

- Consequently, we will find the differences in
**days**after**ignoring**the**years**while calculating all the values.

**Note:** When the day of the **end_date** is less than the day of the **start_date**, it counts days from one month before the **end_day** month.

#### 1.5 Find Out Days Excluding Years & Months

After that, we will show you how you can find out the **days **excluding the **years **and **months**.

**Steps:**

- In the beginning, choose Cell
**D5**and write the following formula down.

`=DATEDIF(B5,C5,"md")`

- Then, press the
**Enter**button. - Consequently, we will find a difference in
**days**after ignoring both the**years**and**months**while calculating. - Finally, move the cursor down to the last data cell to get results according to the formula.

- Here, as the
**unit**argument is**“md”**, the function returns the difference of**days**ignoring the**years**and**months**between the**starting date**and the**ending date.**

#### 1.6 Count Months Between Two Dates Ignoring Years

Lastly, in this final example, we will utilize the Excel **DATEDIF **function to calculate the difference between **two dates **in **months**, ignoring **years**.

**Steps:**

- Firstly, choose
**Cell D5**and type the following formula.

`=DATEDIF(B5,C5,"ym")`

- After that, press
**Enter**and lower the cursor to the last data cell to get results according to the formula.

**months ignoring years**between two days, we inserted

**“ym”**as the

**unit**.

- Consequently, we will find the differences in
**months**after**ignoring**the**years**while calculating all the values.

**Read more:** **How to Use DATE Function in Excel (10 Ideal Examples)**

### 2. Apply DATEDIF & TODAY Functions to Count Age in Excel

In the second example, we have a dataset containing the **Name **and **Birthday **of some students. Now, we will show you how you can count their **age **using the **DATEDIF **and **TODAY** functions in Excel.

However, the **DATEDIF** function can also take an array of dates as the **start_date **argument. Thus, instead of using one date at a time and then dragging the **Fill Handle**, we will use an **Array Formula** to enter all the **starting dates** together and can receive all the values of **ages **together in this example.

#### 2.1 Determine Years

Firstly, we will determine the age in years. To do that, follow the steps given below.

**Steps:**

- In the beginning, insert the following
**Array Formula**in**Cell D6**.

`=DATEDIF(C6:C10,TODAY(),"y")`

🔎** How Does the Formula Work?**

- Firstly, in the
**DATEDIF**function, we inserted cell range**C6:C10**as**start_date**. - Then, we used the
**TODAY**function to get the**date**of**today**as**end_date**. - Finally, we inserted
**“y”**as the unit to get the value in**years**.

- Then, press
**Enter**if you are using**Excel 365**, or else press**Ctrl + Shift + Enter**for other**previous versions**of Excel. - In either case, you will get the
**age**in**years**of each student up to**today**.

**Read More:** **How to Use DAY Function in Excel (3 Ideal Examples)**

#### 2.2 Calculate Months

After that, we will count the **months **ignoring the **years **as we have counted the **age **in **years **above.

**Steps:**

- Firstly, to count the
**months**of the ages, enter this**Array Formula**in**Cell E6**.

`=DATEDIF(C6:C10,TODAY(),"ym")`

**months**between the

**start_date**(cell range

**C6:C10**) and

**today**ignoring the years, as we inserted

**“ym”**as the

**unit**.

- Finally, press
**Enter**or**Ctrl +Shift + Enter**depending on your Excel version.

#### 2.3 Find Out Days

Finally, you can get the age in **days **ignoring the **years **and **months **using both **DATEDIF **and **TODAY **functions in Excel going through the steps given below.

**Steps:**

- In the beginning, insert the following
**Array Formula**in**Cell D6**.

`=DATEDIF(C6:C10,TODAY(),"md")`

**days**between the

**start_date**(cell range

**C6:C10**) and

**today**ignoring both years and months, as we inserted

**“md”**as the

**unit**.

- Thus, you can get the required
**ages**in**days**.

**Read More:** **How to Use the Excel DAYS Function with a Practical Example**

## Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

## Common Errors with DATEDIF Function

Error |
When They Show |

#Value! | When an argument is not of the specified type. For example, when the start_date or end_date argument is a text value, or the unit argument is anything other than the six specific text values. |

#Num! | When the start_date is less than the end_date |

## Conclusion

Thus, you can use the Excel **DATEDIF** function in any formula to count the difference of days, months, or years between any two dates. Do you have any questions? Feel free to inform us.

## Further Readings

**How to Use DATEVALUE Function in Excel (6 Suitable Examples)****Use EDATE function in Excel (5 Simple Examples)****How to Use NETWORKDAYS Function in Excel (3 Suitable Examples)****Use NETWORKDAYS.INTL Function in Excel (2 Examples)****How to Use WORKDAY Function in Excel (5 Examples)****Use WORKDAY.INTL Function in Excel (A Complete Guideline)**