Forum Discussion

Caroline1's avatar
Caroline1
Copper Contributor
Sep 20, 2025

calculating years between 2 dates help

Hi I am having an issue calculating number of years between 2 dates on excel. When i used the dateif function, i get a #name? error and when i yearfrac i get #value! or else a number like 1/3/00

please help

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 23, 2025

    DATEDIF would return a #NAME? error if you actually typed the function as "dateif".

    = DATEDIF(startDate, endDate, "Y")

    should work.  The problem with 

    = YEARFRAC(startDate, endDate)

    is that you are formatting the count of years as a date.  If you are in the US, 1/3/00 is the 3rd January and General format will show it as 3.  Anywhere else in the world the date is the 1st March which has a date value of 61.

  • mathetes's avatar
    mathetes
    Silver Contributor
    Sep 23, 2025

    Since you haven't shared a link with your workbook, you're leaving us to speculate as to the nature of the dates you're working with. I've not been able to force Excel to give me the error messages you refer to, so I'd be hard-pressed to even guess what's happening.

    Perhaps this reference will help you troubleshoot for yourself.

Resources