Use the DATEDIF Function (WS)

Course- Excel >

This Excel tutorial explains how to use the Excel DATEDIF function with syntax and examples.

Description

The Microsoft Excel DATEDIF function returns the difference between two date values, based on the interval specified.

Syntax

The syntax for the DATEDIF function in Microsoft Excel is:

DATEDIF( start_date, end_date, interval )

Parameters or Arguments

start_date and end_date

The two dates to calculate the difference between.

interval

The interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.

Interval	Explanation
Y	The number of complete years.
M	The number of complete months.
D	The number of days.
MD	The difference between the days (months and years are ignored).
YM	The difference between the months (days and years are ignored).
YD	The difference between the days (years and dates are ignored).

Applies To

Excel 2003, Excel 2000

Type of Function

Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel DATEDIF function examples and explore how to use the DATEDIF function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the Excel spreadsheet above, the following DATEDIF examples would return:

=DATEDIF(A1, A2, "Y")
Result: 1

=DATEDIF(A1, A3, "D")
Result: 455

=DATEDIF(A1, A3, "YD")
Result: 90

Frequently Asked Questions

Question: In Microsoft Excel, I have a cell that gives the result of someone's age using this formula:

=DATEDIF(B5,TODAY(),"Y") & " "

The result works fine. I then want to take the answer and if the age is 11 or over, give a result of 250. If the age is 10 and under, give the result 200.

I have tried using the IF formula, but it won't work on the cell with the DATEDIF formula as a value. I can get the IF formula to work on blank cells if I put the age in manually as a number but not if I use the result from the DATEDIF cell.

Answer: The first thing that I notice when looking at your formula is that you have two components to your formula. The first part of your formula is the DATEDIF function:

=DATEDIF(B5,TODAY(),"Y")

And the second part of your formula, you are concatenating a space at the end of your result:

& " "

This space at the end is probably what is causing your problem, as your formula is not returning a pure number, but rather the age with a space concatenated at the end.

When you are testing your age with the IF function, make sure you don't include the concatenated space within the IF formula itself.

You can try the following formula:

=IF(DATEDIF(B5,TODAY(),"Y")>=11,250,200) & " "

This formula will return either 250 or 200 depending on the age and then concatenate a space at the end. Notice that the & " " is not included in the IF function itself.

Question: In Excel, I have an operating field which is cell C4. I need to know how to return a zero (0) if the operating field is "blank."

I have the following formula:

=DATEDIF(C4,TODAY(), "y")

I will have occasions when the C4 data field will be blank, and in these cases, I need the formula to return zero instead of 115. Please help!

Answer: You can use the ISBLANK function to test whether a cell value is blank as follows:

=IF(ISBLANK(C4),0,(DATEDIF(C4,TODAY(),"y")))

This formula will return 0 if C4 is blank. Otherwise, it will return the DATEDIF calculation.

PRV MENU NEXT