askvity

How to Calculate Years in Excel?

Published in Excel Date Calculations 4 mins read

To calculate years in Excel, you can extract the year component from a date or calculate the difference between two dates and display it in years. Here's a breakdown of common methods:

Extracting the Year from a Date

You can use the YEAR function to get just the year from a date.

  1. Select the cell containing the date you want to work with.
  2. Enter the formula =YEAR(cell_reference). For instance, if your date is in cell A1, you would enter =YEAR(A1). This formula will return only the year as a number.
  3. Example: If cell A1 contains 18-Aug-2024, then =YEAR(A1) will return 2024.

This result can be used within other calculations or formulas in Excel.

  • To get the first day of the year using the extracted year, you can use the DATE function in combination with YEAR. For example, to return a date that represents the start of the year from the date in cell A1, use the formula =DATE(YEAR(A1),1,1). This will return the date 01-Jan-2024 if A1 has 18-Aug-2024.

Calculating the Difference Between Two Dates in Years

To calculate the difference between two dates in years, you can use the YEARFRAC function or simple subtraction depending on your requirement.

Method 1: Using YEARFRAC Function

The YEARFRAC function returns the fraction of a year between two dates. It takes three arguments: start_date, end_date, and basis.

  1. Enter the formula: =YEARFRAC(start_date, end_date, [basis]).
    • start_date: The date the period begins.
    • end_date: The date the period ends.
    • basis: An optional argument indicating how days are counted, like "actual/actual", "actual/360" (with a 360-day year), and others. If omitted, the default is 0 or actual/actual.
  2. Example: If cell A1 has 01/01/2020 and B1 has 01/01/2024, the formula =YEARFRAC(A1,B1) will return 4. The formula =YEARFRAC(A1,B1,1) (using Actual/365 basis) may return 4.0.

Method 2: Subtracting Years Using the YEAR Function

For a simpler calculation you can subtract the YEAR function on two dates. Note that this may not always be what you need if you also need a fractional year value.

  1. Enter the formula: =YEAR(end_date) - YEAR(start_date)
  2. Example: If cell A1 contains 01/01/2020 and B1 contains 01/01/2024, then the formula =YEAR(B1)-YEAR(A1) will return 4.

Examples of Usage

  • Age Calculation: If you have a birth date, you can calculate the current age using the YEARFRAC formula with the TODAY() function to get the current date or subtract the years using the YEAR function and the current year. For instance, if B1 is the birthdate and you want to calculate age, you can use =YEARFRAC(B1,TODAY()) or =YEAR(TODAY()) - YEAR(B1).
  • Project Duration: Calculate how many years a project took by subtracting the start date from the end date and use the YEARFRAC function to get a more precise value, including fractional years.
  • Historical Data Analysis: Extract year values from a column of dates to analyze year-specific data.

Summary

Excel provides several ways to calculate years, from extracting the year from a single date to calculating the difference in years between two dates. You can choose the method that best fits your specific needs.

Related Articles