To apply the DATEDIF function, follow these steps:
- select the cell where you wish to display the result,
- switch to the Formula tab,
- click the Date and time button,
- choose the DATEDIF function from the list,
- enter the required arguments separating them by commas.
Alternatively, you can add the DATEDIF function in one of the following ways:
- start adding the function:
- switch to the Home tab,click the Insert Function
icon situated at the top toolbar and select the Additional option,
- or right-click within a selected cell and select the Insert Function option from the contextual menu,
- or click the
icon situated before the formula bar,
- in the Insert Function window, select the Date and time function group from the list,
- click the DATEDIF function and press the OK button,
- enter the required arguments separating them by commas.
The
start-date and
end-date arguments can be represented as:
- nested functions that return dates, e.g.
DATE(2015,3,23)
- references to cells that contain dates, e.g. B2
- text strings surrounded by quotation marks, e.g. "4/27/2017"
- serial numbers of dates, e.g. 42852 that represents the date of April 27, 2017
The unit argument must be enclosed into the quotation marks.
In the examples below, we will consider, which of the unit arguments should be used depending on your tasks.
When the arguments are specified, press the Enter key. The result will be displayed in the selected cell.
In this example, we use the following function:
=DATEDIF(DATE(2015,3,23),"4/27/2017","Y") It returns the number of completed years in the entire period between March 23, 2015 and April 27, 2017. The result is 2 years.
Let's change the unit argument to calculate the difference between the same dates based on other intervals:
=DATEDIF(DATE(2015,3,23),"4/27/2017","M") - the function calculates the number of completed months in the entire period from March 23, 2015 to April 27, 2017. The result is 25 months.
=DATEDIF(DATE(2015,3,23),"4/27/2017","D") - the function calculates the total number of completed days in the entire period from March 23, 2015 to April 27. The result is 766 days.
=DATEDIF(DATE(2015,3,23),"4/27/2017","MD") - the function calculates the number of days between March 23, 2015 and April 27, 2017 ignoring months and years, i.e. it returns the difference in days between the 23-th and 27-th day. The result is 4 days.
=DATEDIF(DATE(2015,3,23),"4/27/2017","YM") - the function calculates the number of months between March 23, 2015 and April 27, 2017 ignoring days and years, i.e. it returns the difference in months between March and April. The result is 1 month.
=DATEDIF(DATE(2015,3,23),"4/27/2017","YD") - the function calculates the number of days between March 23, 2015 and April 27, 2017 ignoring years, i.e. it returns the difference in days between March 23 and April 27. The result is 35 days.
That's all. Now you can easily calculate the difference between two dates using the DATEDIF function.