MS Excel: DATEDIF Function (WS)
The Microsoft Excel DATEDIF function returns the difference between two date values, based on the interval specified.
DATEDIF Function (Syntax)
The syntax for the Microsoft Excel DATEDIF function is:
DATEDIF( start_date, end_date, interval )
start_date and end_date are the two dates to calculate the difference between.
interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.
|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).|
The DATEDIF function can be used in the following versions of Microsoft Excel:
- Excel 2003, Excel 2000
Type of Excel Function
The DATEDIF function can be used in Microsoft Excel as:
- Worksheet function (WS)
DATEDIF Examples (as Worksheet Function)
Let's look at some Excel DATEDIF function examples and explore how you would use the DATEDIF function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following DATEDIF function examples would return:
|=DATEDIF(A1, A2, "Y")||would return 1|
|=DATEDIF(A1, A3, "D")||would return 455|
|=DATEDIF(A1, A3, "YD")||would return 90|
Frequently Asked Questions (DATEDIF Function)
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:
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.