Calculate Years Between Dates

Explanation

In this example, the goal is to calculate the number of years between a start date in column B and an end date in column C. An easy way to solve this problem is to use the YEARFRAC function, which returns the number of years between any two dates as a decimal number.

YEARFRAC function

The YEARFRAC function returns a decimal number representing the fraction of a year between two valid Excel dates . the generic syntax for YEARFRAC looks like this:

=YEARFRAC(start_date,end_date)

For example, here are a few results from YEARFRAC with some hardcoded dates:

=YEARFRAC("1-Jan-2019","1-Jan-2020") // returns 1
=YEARFRAC("1-Jan-2019","1-Jul-2020") // returns 1.5
=YEARFRAC("1-Jan-2019","1-Jan-2021") // returns 2

In the worksheet shown, the formula used to calculate years between dates in cell D5 looks like this:

=YEARFRAC(B5,C5) // returns 1

As the formula is copied down, it returns the number of years between each start and end date as a decimal number.

Rounding results

Once you have the decimal number from YEARFRAC, you can round the number as you like. For example, you could round to the nearest whole number with the ROUND function :

=ROUND(YEARFRAC(A1,B1),0)

Whole years only

You might also want to keep only the integer portion of the result with no decimal value so that you are only counting whole years. In that case, you can wrap YEARFRAC in the INT function like this:

=INT(YEARFRAC(A1,B1))

The INT function removes the decimal portion of the number altogether. If you need to calculate years on an ongoing basis, for example, to calculate current age based on a birthday, see the example here .

Note: The YEARFRAC function has an optional 3rd argument that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The YEARFRAC page provides more information.

Explanation

When date information from other systems is pasted or imported to Excel, it may not be recognized as a proper date or time. Instead, Excel may interpret this information as a text or string value only. In this example, the goal is to extract valid date and time information from a text string and convert that information into a datetime . It is important to understand that Excel dates and Excel times are numbers so at a high level, the main task is to convert a text value to the appropriate numeric value.

Date

To get the date, we extract the first 10 characters of the value with the LEFT function :

LEFT(B5,10) // returns "2015-03-01"

The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE , which converts the text into a proper Excel date .

Time

To get the time, we extract 8 characters from the middle of the text with the MID function :

MID(B5,12,8) // returns "12:28:45"

Again, the result is text. To get Excel to interpret this value as time, we wrap MID in TIMEVALUE , which converts the text into a proper Excel time .

Datetime

To get a final datetime , we just add the date value to the time value. The formula in E5 is:

=C5+D5

Once you have a valid datetime, use custom number formatting to display the value as desired.

All in one formula

Although this example extracts the date and time separately for clarity, you can combine formulas if you like. The following formula extracts the date and time, and adds them together in one step:

=LEFT(date,10)+MID(date,12,8)

Note that DATEVALUE and TIMEVALUE aren’t necessary in this case because the math operation (+) causes Excel to automatically coerce the text values to numbers.