Duration between two dates can be determined by using the DATEDIF function. This step by step tutorial will assist all levels of Excel users in calculating the difference between dates in days, months and years.
Figure 1. Final result: Get days, months, and years between dates
Final formula:
=IF(DATEDIF(B3,C3,"y"),DATEDIF(B3,C3,"y")&" years","")&IF(DATEDIF(B3,C3,"ym"),", "&DATEDIF(B3,C3,"ym")&" months","")&IF(DATEDIF(B3,C3,"md"),", "&DATEDIF(B3,C3,"md")&" days","")
Syntax of IF Function
IF function evaluates a given logical test and returns a TRUE or a FALSE
=IF(logical_test, [value_if_true], [value_if_false])
- The arguments “value_if_true” and “value_if_false” are optional. If left blank, the function will return TRUE if the logical test is met, and FALSE if otherwise.
Syntax of DATEDIF Function
DATEDIF function returns the difference between two dates in number of years, months or days; it returns a #NUM! error when start_date is greater than the end_date
=DATEDIF(start_date,end_date,unit)
- start_date – a date representing the start date
- end_date – a date representing the end date
- Dates may be entered as serial numbers, text strings in quotation marks, or cell reference
- unit – represents the type of information we want to obtain; we can refer to the table below:
Figure 2. Unit options and corresponding results for DATEDIF function
Setting up the Data
Our table has three columns: Start Date (column B), End Date (column C) and Duration between dates (column D). We want to determine the duration between the start date and end date in number of years, months and days. The results will be recorded in column D.
Figure 3. Sample data to get days, months, and years between dates
Duration between dates in days, months and years
In order to determine the difference between two dates in number of days, months and years, we use the DATEDIF function. Let us follow these steps:
Step 1. Select cell D3
Step 2. Enter the formula:
=DATEDIF(B3,C3,"y")&" years"&", "&DATEDIF(B3,C3,"ym")&" months"&", "&DATEDIF(B3,C3,"md")&" days"
Step 3. Press Enter
Step 4: Copy the formula in cell D3 to cells D4:D7 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down
Figure 4. Entering the DATEDIF function to get days, months, and years between dates
Our formula consists of three DATEDIF functions where the number of years, months and days are calculated separately. The unit arguments used for number of years, months and days are “y”, “ym” and “md”, respectively.
Each DATEDIF function calculates the difference between the dates in the specified unit, then links the appropriate text string for “years”, “months” and “days”. The ampersand “&” is used to link text strings together.
We can break down the formula into three components:
For years: DATEDIF(B3,C3,"y")&" years"
For months: &", "&DATEDIF(B3,C3,"ym")&" months"
For days: &", "&DATEDIF(B3,C3,"md")&" days"
As a result, the values in D3:D6 represent the duration between the two dates in columns B and C in number of years, months and days. For cell D7, our formula returns a #NUM! error because the start date “1/27/2017” is greater than the end date “1/27/2016”.
Duration between dates without zero values
Note that the results in column D in the previous example include some zero values. We want to determine the difference between two dates without having to show the zero values. We can do this by using a formula that combines the IF and DATEDIF functions. Let us follow these steps:
Step 1. Select cell D3
Step 2. Enter the formula:
=IF(DATEDIF(B3,C3,"y"),DATEDIF(B3,C3,"y")&" years","")&IF(DATEDIF(B3,C3,"ym"),", "&DATEDIF(B3,C3,"ym")&" months","")&IF(DATEDIF(B3,C3,"md"),", "&DATEDIF(B3,C3,"md")&" days","")
Step 3. Press Enter
Step 4: Copy the formula in cell D3 to cells D4:D7 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down
Figure 5. Entering the IF and DATEDIF functions to show only non-zero values
This time, our DATEDIF formula is embedded in an IF formula. The IF function evaluates non-zero results as true. Hence, if the DATEDIF function returns a value other than zero, the IF function will return the result of the DATEDIF function; otherwise it returns an empty string “”.
Below table shows the simplified results of our formula, where zero values are not displayed.
Figure 6. IF and DATEDIF showing the difference in dates without zero values
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment