**What is Excel DATEDIF Function?**

Excel DATEDIF function is one of the undocumented functions in excel. This means that it is a hidden function in Excel.

When you type a formula name (=DATE) in Excel, the DATEDIF function does not come up in the prompt list (IntelliSense) of function names that Excel displays and even when using the DATEDIF function, Excel will not fill in the arguments like the other functions.

Therefore, you need to know and understand the syntax of this function thoroughly.

Yet, Excel DATEDIF function remains a very useful function for specific problems.

**How Excel DATEDIF Function Works?**

Excel DATEDIF function calculates the number of days or months or years between two specified dates. The DATEDIF function calculates the difference between two date values and returns a number that represents the specified unit type.

**Syntax**

** =DATEDIF(start_date,end_date,unit)**

**Input Arguments**

**start_date**: This is the initial date of the period. It can be entered as a serial number, or text strings in double quotes, or as a date value result from other formulas.**end_date**: This is a date value that represents the last or ending date of the period. It can be entered as a serial number, or as text strings in double quotes, or as a date value result from other formulas.**unit**: This indicates the time unit we want the information, either in days, months or years. It determines the type of result you get from this function.

There are six available unit values from the DATEDIF function and each give a different output. The units can be entered in upper or lower case (i.e. “y” or “Y”).

**When Excel DATEDIF Function is Used?**

Excel DATEDIF function is used when you want to calculate the number of days, months or years between two specific dates.

A good example is when you want to calculate the current age of employees from a birth date or current date. Also, if you want to calculate the number of days outstanding debts have been due.

**Additional Notes**

- Excel does not display or fill in the DATEIF function like other functions when typing it in a cell. You need to know the arguments to use. However, the function is available in all versions of Excel.
- You can enter the dates as text strings (“20/5/2020”), or as serial numbers (43971 which represents 20
^{th}May, 2020), or as the results of other formulas. - Microsoft recommends not using the “MD” unit value because it result in a zero, a negative number or inaccurate result.

**How to Use Excel DATEDIF Function: Formulas & Examples**

There are different real-life scenarios in which you can use Excel DATEIF function.

For example, if you want to calculate the number of days your debtors are required to pay up their debts as at today’s date, from the date the payment was initially due, then you use Excel DATEDIF function.

You can also use DATEDIF function to calculate and set up an anniversary due date for your client’s list.

Now, let us look at some examples to understand how to use the DATEDIF function.

**Example 1: Basic Excel DATEDIF Function**

In the above example, we use DATEDIF function to calculate the number of days, months and years between two dates using these formulas:

** =DATEDIF(A5,B5,”d”)** – for number of days

** =DATEDIF(A6,B6,”M”)** – for number of months

** =DATEDIF(A7,B7,”Y”)** – for number of years

Here, we have used cell references in the formula. However, you can also enter the date as text string like this:

** =DATEDIF(“01/01/2015”, “31/10/2020”, “d”)**

or you can enter the serial numbers representing the dates like this:

**=DATEDIF(42005, 44135, “D”)**

But you need to be careful when using the serial numbers as it is not fully reliable. This is because date numbering for different computer operating systems vary.

Note that the unit value can take an UPPER or lower case.

**Example 2: Calculate Difference in Days**

The Excel DATEDIF function calculates the number of days between two dates in three different ways using different units in the formula as shown below:

**=DATEDIF(A5,B5,”d”)**

** =DATEDIF(A6,B6,”MD”)**

** =DATEDIF(A7,B7,”yd”)**

Note that the first formula can also be replaced with a simple calculation like this:

**=”31/10/2021” – “01/03/2015”**

And you will get the same result as: =DATEDIF(A5,B5,”d”). This is because Excel dates are basically serial numbers that you can perform calculations with.

In the above example, DATEIF function returns 2436 in the 1st row, which is the total number of all days from 01/03/2015 – 31/10/2021. In the 2^{nd} row, it returns 30, which is the total number of days between 01 – 31 (ignoring the months and years). And the 3^{rd} row returns 244, which is the total number of days between 01/03 – 31/10 (ignoring the years).

**Example 3: Calculating Difference in Months**

DATEDIF function calculates the difference in months in two ways. It calculates in the complete months including the years, as well as the complete months ignoring the years.

In the above example, we use both formulas to calculate the difference in the number of completed months between the 01/03/2015 and 31/10/2021.

The first formula counts all the completed months and returns 79 months. The second formula returns 7 (10 – 3 = 7) which counts only the months and ignores the years.

This can be used when you want to calculate the number of months between the start and end date of any project.

**Example 4: Calculate Difference in Years**

Excel DATEDIF function calculates the difference in number of years between two dates in complete years.

There is only one method to do this using the formula:

**=DATEDIF(A5,B5,”y”)**

In the above example, Excel DATEDIF function calculates the number of complete (12 months) years between 01/03/201 and 31/10/2021 and returns 6 and 5 years respectively. It ignores the months and days after it.

If you look at the second row, the difference is almost 6 years, but because Excel DATEDIF formula calculates only the number of complete years (12 months), it returns a result of 5.

To get a more accurate result, use Excel YEARFRAC function as shown above.

This formula is commonly used to calculate the age in years.

**Example 5: Use DATEDIF Function with other Function**

Excel DATEDIF function can be used alone or with other excel functions.

For example, you can use DATEDIF function and TODAY function to calculate the current age from the date of birth using the formula:

**=DATEDIF(A52,TODAY( ),”y”)**

For instance, if you want to know how many years and months in the date, then you can use YM in addition to get the months as shown below:

The first row is 63years 9months, the second row is 42years 4months, and the third row is 22years.

**Common Errors**

- #NUM! error – This error occurs when the start_date is greater than the end_date.
- #VALUE! error – This is the error you get when you enter an invalid date. This means that Excel is unable to recognize the date specified.

**Other Related Excel Functions**

Thank you for taking your time to learn Excel DATEDIF function. You can master Microsoft Excel by learning other Excel functions.

To learn more, check out these other functions in Excel:

- EDATE Function

Hey, you used to write wonderful, but the last few posts have been kinda boring… I miss your great writings. Past several posts are just a bit out of track! come on!

It is in reality a great and helpful piece of information. I’m happy that you simply shared this useful info with us. Please keep us up to date like this. Thanks for sharing.

I am constantly thought about this, thanks for posting.

에볼루션접속 먹튀검증 안전노리터 go

buy sumatriptan 50mg sale – order imitrex 50mg pills sumatriptan 50mg sale

Woah! I’m really loving the template/theme of this site. It’s simple, yet effective. A lot of times it’s challenging to get that “perfect balance” between user friendliness and visual appeal. I must say you have done a excellent job with this. In addition, the blog loads super fast for me on Safari. Exceptional Blog!

casino slot

[url=”https://2-free-slots.com”]slots s[/url]

gw2 jewry slots

double hit casino

[url=”https://2-free-slots.com”]all slots casino[/url]

free games

This is an excellent post! iphone mobile ringtone

Your work is top-notch!

Very neat post.Much thanks again. Awesome.

Very insightful!

Hello! I simply wish to offer you a big thumbs up for the excellent information you have here on this post. I am coming back to your web site for more soon.

Itís hard to find knowledgeable people for this topic, but you seem like you know what youíre talking about! Thanks

Howdy! I just wish to offer you a big thumbs up for your excellent info you have got here on this post. Ill be returning to your site for more soon.

This is really interesting. Check out my site

Hello, Neat post. There is an issue with your website in internet explorer, would test thisK IE nonetheless is the marketplace leader and a good element of people will pass over your wonderful writing due to this problem.

I went over this internet site and I believe you have a lot of good information, saved to fav (:.

Thank you a lot for sharing this with all people you actually recognise what you are speaking about! Bookmarked. Kindly also discuss with my site =). We could have a hyperlink exchange contract among us!

hi!,I love your writing so so much! share we be in contact more approximately your post on AOL? I require an expert on this house to unravel my problem. May be that is you! Looking ahead to see you.

The more you play, SLOTthe more fun The more you spin, the more chances you have to enter free spins. Auto type must only spin PG SLOT slots.