Excel: Calculate Years & Months Between Two Dates


You can use the following formula to calculate the number of years and months between two dates in Excel:

=DATEDIF(A2, B2,"y")&" years "&DATEDIF(A2, B2,"ym")&" months"

This particular formula will return the number of years and months between the start date in cell A2 and the end date in cell B2.

For example, if cell A2 contains 1/4/2005 and cell B2 contains 1/1/2022 then this formula will return 16 years 11 months.

The following examples show how to use each formula in practice.

Example: Calculate Years & Months Between Two Dates in Excel

Suppose we have the following list of start and end dates in Excel:

Suppose we would like to calculate the number of full years and months between the start and end dates in each row.

We can type the following formula into cell B2 to do so:

=DATEDIF(A2, B2,"y")&" years "&DATEDIF(A2, B2,"ym")&" months"

We can then click and drag this formula down to each remaining cell in column B:

Excel calculate years and months between two dates

Column C now displays the number of full years and months between the start date and end date in each row.

Note that you can also format the output in a different way if you’d like.

For example, you could use the following formula to add a comma between the years and months:

=DATEDIF(A2, B2,"y")&" years, "&DATEDIF(A2, B2,"ym")&" months"

The following screenshot shows how to use this formula in practice:

The years and months now have a comma in between them in the output of column C.

Note: You can find the complete documentation for the DATEDIF function in Excel here.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate the Number of Months Between Dates in Excel
How to Convert Date to Month and Year Format in Excel
How to Calculate Average by Month in Excel

4 Replies to “Excel: Calculate Years & Months Between Two Dates”

  1. Zach your explanation is fine for date AFTER 1900. I am doing family research going back to the 11th century.
    Here to dates as an example: Start date 1/1/1532 End date 12/12/1612.
    I have over 200 such dates over the centuries.

    I need a formula to calculate Years and Months.

    Can you supply that?

    1. Hi John…To calculate the years and months between two dates in Excel, you can use a combination of functions to achieve this. Here’s how you can do it:

      1. **Ensure your dates are properly formatted in Excel:**
      – Start Date in column A
      – End Date in column B

      2. **Use the `DATEDIF` function to calculate the difference in years and months:**
      – The `DATEDIF` function can calculate the difference between two dates in various units, but it is undocumented in Excel’s help files.

      ### Step-by-Step Guide:

      1. **Open your Excel sheet and enter your dates in columns A and B:**

      | A | B |
      |————|————-|
      | Start Date | End Date |
      | 1/1/1532 | 12/12/1612 |

      2. **Enter the following formulas to calculate the years and months between the dates:**

      – **Years Calculation:**

      In cell C2 (or any other cell), enter:
      “`excel
      =DATEDIF(A2, B2, “Y”)
      “`

      – **Months Calculation:**

      In cell D2 (or any other cell), enter:
      “`excel
      =DATEDIF(A2, B2, “YM”)
      “`

      3. **Combine Years and Months in a single cell (Optional):**

      If you want to have a combined result of years and months in a single cell, you can use:
      “`excel
      =DATEDIF(A2, B2, “Y”) & ” Years, ” & DATEDIF(A2, B2, “YM”) & ” Months”
      “`

      ### Example in Excel:

      | A | B | C | D | E |
      |————|————-|——-|——-|———————-|
      | Start Date | End Date | Years | Months| Years and Months |
      | 1/1/1532 | 12/12/1612 | =DATEDIF(A2, B2, “Y”) | =DATEDIF(A2, B2, “YM”) | =DATEDIF(A2, B2, “Y”) & ” Years, ” & DATEDIF(A2, B2, “YM”) & ” Months” |

      ### Result:

      For the example dates (1/1/1532 and 12/12/1612):
      – **Years**: 80
      – **Months**: 11
      – **Combined**: 80 Years, 11 Months

      ### Applying to Multiple Rows:

      If you have multiple rows of dates, you can drag the formulas down to apply them to all rows. Excel will adjust the cell references automatically.

      1. Enter your start and end dates in columns A and B.
      2. Copy the formulas in columns C, D, and E to all rows containing dates.

      This will give you the difference in years and months for all your date pairs.

  2. Zach thanks for your prompt response.
    I had already tried those iterations. I always get a #VALUE! error. I have entered your example thus:
    =DATEDIF(A8, B8, “Y”)=DATEDIF(A8, B8, “YM”)=DATEDIF(A8, B8, “Y”) & ” Years, ” & DATEDIF(A8, B8,“YM”) & ” Months”
    And get the same result:
    1/1/1532 12/12/1612 #VALUE!
    This is exactly as you gave to me. What do you think is wrong??
    Regards
    John

    1. Hi John…Without having access to your Excel file I cannot state for certain, however some ideas below may help.

      The `#VALUE!` error in Excel typically indicates that there is an issue with the formula, such as incorrect syntax or incompatible data types. In your case, it seems like there may be formatting or syntax issues with the `DATEDIF` function and the way you are combining the results. Here is a corrected version of your formula:

      1. **Correct Formula**: The formula should be split into parts and combined correctly:
      “`excel
      =DATEDIF(A8, B8, “Y”) & ” Years, ” & DATEDIF(A8, B8, “YM”) & ” Months”
      “`

      2. **Check Date Formats**: Ensure that the cells `A8` and `B8` contain valid dates. Excel needs to recognize the values as dates to perform the calculation correctly.

      3. **No Smart Quotes**: Ensure you are using straight quotes (`”`) rather than smart quotes (`“ ”`), which can cause issues in formulas.

      Let’s break it down step by step:

      1. **Enter Valid Dates in Cells**:
      – Cell `A8`: 1/1/1532
      – Cell `B8`: 12/12/1612

      2. **Enter the Correct Formula**:
      – Select the cell where you want the result to appear.
      – Enter the following formula:
      “`excel
      =DATEDIF(A8, B8, “Y”) & ” Years, ” & DATEDIF(A8, B8, “YM”) & ” Months”
      “`

      3. **Check for Proper Formatting**:
      – Ensure that the dates in cells `A8` and `B8` are recognized as date values. You can check this by clicking on the cells and verifying that Excel shows them as dates in the formula bar.

      ### Example Steps in Excel:

      1. **Input Dates**:
      – In cell `A8`, type `1/1/1532`.
      – In cell `B8`, type `12/12/1612`.

      2. **Input Formula**:
      – In cell `C8` (or any cell where you want the result), enter:
      “`excel
      =DATEDIF(A8, B8, “Y”) & ” Years, ” & DATEDIF(A8, B8, “YM”) & ” Months”
      “`

      This should give you the correct result without any `#VALUE!` error.

      If you still encounter the `#VALUE!` error, please check:

      – **Date Formats**: Ensure the dates are in a format Excel recognizes. You can try re-entering the dates.
      – **Formula Quotes**: Make sure you are using straight quotes in the formula.

Leave a Reply

Your email address will not be published. Required fields are marked *