Date Difference in Excel DATEDIF in 3 Easy Examples

If you work with dates in Microsoft Excel then you might need to learn function of DATEDIF. This function is not very common tough. But can be useful for specific calculations. So here, we will explore everything you about this formula to get better in Excel.

Introduction to date difference in excel: DATEDIF in Excel

Excel, our lovely free program can help you make complex calculations. And its DATEDIF function can be useful in many scenarios. This short name stands for Date Difference. And as the name suggests, it calculates the difference between two dates. This is available in all versions of Excel. And you can use it with combination of other formulas.

Syntax and Arguments of excel date difference

The syntax of the DATEDIF function is as follows in below.

Here, the start_date and end_date arguments are the dates you want to calculate the difference. The unit argument specifies the unit of time for the calculation. There are six units available in Excel tough.

• “Y” – calculates the number of complete years between the two dates.
• “M” – calculates the number of complete months between the two dates.
• “D” – calculates the number of days between the two dates.
• “YM” – calculates the number of months between the two dates, ignoring the years.
• “YD” – calculates the number of days between the two dates, ignoring the years.
• “MD” – calculates the number of days between the two dates, ignoring the years and months.

You need to specify one of these units in the third argument of the function.

Examples of excel datedif Function

Example 1: Calculating the Number of Years: difference between 2 dates in excel

We suppose you want to calculate the number of years between 1st January 2010 and 31st December 2022. So you can do this calculation:

The result will be 12. And ith means that there are 12 complete years between these two dates.

Example 2: Calculating the Number of Months with datedif formula

If you wanna calculate the number of months between two dates like 1st January 2010 and 31st December 2022. You can copy and paste below formula string.

Here the end result will be 144. If you ask what it means, wee know there are 144 complete months between these two dates.

Example 3: Calculating the Number of Days

Now if we are going to calculate between 1st January 2010 and 31st December 2022, you can apply below formula.

You will find 4748 and it means that there are 4748 days between these two dates.

Frequently Asked Questions on excel formula for date difference

how to use datedif in excel to calculate the age?

Yes, for sure. Even it is age, it is basically date difference question. So, for example, if the person’s date of birth is in cell A1 and the current date is in cell A2. You can use the following formula right now.

Hence, this will give you the person’s age in years.

Can I use excel date if formula to calculate the number of days between two dates. But excluding weekends?

No, this function cannot exclude weekends from the calculation. However, you can use a combination of the DATEDIF and the NETWORKDAYS functions. So, you can  calculate the number of working days between two dates. The NETWORKDAYS function returns the number of working days between two dates. So, this one is excluding weekends and any holidays in case you specify.

Is it Possible the DATEDIF function to calculate the number of years, months and days between two dates?

No, this function cannot calculate the number of years, months and days between two dates in a single formula. However, you can use multiple of these functions to calculate the difference in each unit of time. For example, you can use one DATEDIF function to calculate the number of years, another DATEDIF to calculate the number of months. And lastly a third one to calculate the number of days.

Q4. Can the DATEDIF function Work with dates in different time zones?

Yes, it can. Yet, this applies as long as you enter the dates with correct format. You can also use the TIME function to add or subtract a specific amount of time from a date.

So, How to Use the DATEDIF function to calculate the number of weeks between two dates?

No, it does not have a weeks unit. However, you can calculate the number of weeks by dividing the number of days by 7.

What happens if the start_date is greater than the end_date?

If the start_date is greater than the end_date in the DATEDIF function, the result will be negative.

The result will be -12. And this negative result means that there are 12 complete years between these two dates. But, these are in the opposite direction.

Conclusion

The DATEDIF function is very useful in many scenarios where you need to calculate the difference between two dates. It is a simple function to use. Yet, it can surely save you a lot of time. Here we gave you several syntax and arguments of the function. As well as some examples of how to use it. We also answered some common questions for you. So, you can also read these articles if you are interested in Excel tutorial type content tough.