**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.

**Table of Contents**Expand

## 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.

## How to Use DATEDIF Function in Excel: 2 Ideal Examples

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 the 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 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.

### 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**.

#### 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 by 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**.

## 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 |

**Download Practice Workbook**

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

## 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.

**<< Go Back to Excel Functions ****|**** Learn Excel**