How do you calculate years between dates in Excel? This is easier than you think, using the ‘DATEIF’ function. In this blog and summary video, I take you through each step using Excel on Windows 10. Though the formulas are also the same for Excel 2021 on Mac.

Here’s the video if you want to dive in and watch the demo. Read on for a written explanation, example uses, and to download the Excel template shown in the video. I’ll also include how to calculate days, weeks, and months between dates…

By the way, if you just want to know the even easier way of extracting the year from a date, that’s included too. Here’s a snapshot from the video and template on exactly how to do that, using the ‘YEAR’ function:

How to get year from date in Excel

Why Calculate Years Between Dates in Excel?

There are many reasons why you might want to calculate years between dates or extract years from dates in Excel. For example:

  • Years of Service: You might want to calculate someone’s length of service from their start date to today or when they left.
  • Age from Birthday: By using date of birth as the start date, and Excel’s ‘TODAY’ function, you can easily calculate someone’s age. This might be helpful for example in managing retirements, equality monitoring, or as the first step in grouping customers into age categories using a series of Excel ‘IF’ functions.
  • Extract Years: Extracting years from a date can be very helpful in summarising data or using in onward calculations. For example, sales transactions aligned to dates can be easily categorised by year using the ‘YEAR()’ function.
  • Countdown to Future Date: Using the techniques below, you can countdown to a future date by projecting a date in the future. The ‘DATEDIF’ will allow you to present this in years, months, and/or days. For example and as shown in the template provided, this would work for a ‘Days since last accident’ Simpson’s style, ‘How many sleeps until Christmas’, ‘Years until NASA land on the Moon again‘, or even ‘Years and Months until my mortgage is paid‘!

There many uses for calculating years, months and days between two dates, there’s even dedicated websites as online calculators! Whether this is for important dates, birthdays or other examples as the extract from my template below shows. Therefore another use is it saves you from having to use an online years calculator!

Example uses of calculating dates in Excel

How to Calculate Years Between Two Dates…

There are two main ways for calculating years between dates in Excel:

  1. For whole years, use the ‘DATEDIF’ function: This returns the number of whole years between two dates and ignores partial years. For example, a gap of 18 months will bring a result of 1 year. The formula is expressed as =DATEDIF(startdate,enddate,unit). As always, the start date must be earlier than the end date, then enter “Y” as your unit for years.
  2. To include part years, use the ‘YEARFRAC’ function: The YEARFRAC formula returns the number of years between two dates as a precise decimal. For example, a gap of 18 months brings back a result of 1.5 years. The formula is expressed as =YEARFRAC(startdate,enddate,basis), whereby for the ‘basis’ you simply type 1.

Examples are shown in the images and template download below…

How to calculate years between dates using DATEDIF in Excel
Calculate years between dates with YEARFRAC Excel function
Years Between Dates FormulasDownload

Bonus: Calculate Difference in Days, Weeks and Months

Calculate days weeks and months between dates in Excel

The image preview above provides bonus content in the Excel download provided with this blog. As shown, you can also use the ‘DATEDIF’ Excel function to calculate days, weeks or months between dates. The start date and end date are the same in each scenario, and referenced in the formula used. The main difference comes in choosing your units. As a reminder of the main unit definitions in Excel:

  • “Y” = Years
  • “M” = Months
  • “D” = Days

Of course and as shown, adding a simple ‘divide by 7’ with the use of the days units gives you weeks. Whether it’s counting down an important date or using as part of another Excel formula, I hope it helps! Let me know how you’re getting on with these functions and what you’re using them for in the comments below.

I hope you’ve enjoyed this demo on date formulas, and find the how-to video and template helpful. Subscribe to my EATO YouTube channel for more MS Office how-to videos and presentations. Liking the content? Please like and share with friends and colleagues who may also find it useful. If so inclined, supporting my work helps keep free content flowing. Want fantastic, time-saving templates just for you and your business? Then please get in touch to arrange a bespoke quote. Finally, please feel free to leave your feedback below and/or comment on what tutorials and templates you’d like created.

Kind Regards, Adrian