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:
Based on the Excel spreadsheet above, the following DATEDIF examples would return:
=DATEDIF(A1, A2, "Y")
=DATEDIF(A1, A3, "D")
=DATEDIF(A1, A3, "YD")
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:
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:
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:
This formula will return 0 if C4 is blank. Otherwise, it will return the DATEDIF calculation.