Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Use the Excel DATEDIF Function? 3 Easy Examples

How to Use the Excel DATEDIF Function? 3 Easy Examples

The Excel DATEDIF formula calculates the gap between two date ranges in the format of years, months, or days.

It was originally introduced in a very old Excel version and documented only in Excel 2000. But, it can still be used in the latest versions of Excel including Office 365. 

DATEDIF function in Excel
DATEDIF function in Excel

In this tutorial, I’ll show you how to use Excel DATEDIF with the help of some examples. 

You’ll learn:

  • Excel DATEDIF Syntax
  • How to Use the Excel DATEDIF Function?
    • Example 1: Basic Uses of DATEDIF
    • Example 2: Calculate the Difference in Days Using DATEDIF
    • Example 3: Calculate the Difference in Months Using DATEDIF
    • Example 4: Calculate the Difference in Years Using DATEDIF

Related:

How to Use the NPER Excel Formula: 2 Easy Examples

The Excel SMALL Function – 3 Best Examples

The Excel CHOOSE Function – 4 Best Uses

Excel DATEDIF Syntax

=SYNTAX(start_date,end_date,Unit)

Where start_date is the beginning date of the time period

end_date is the ending date of the time period and

Unit is the type of difference you are looking for ( days, months, or years) 

You can use the following time unit options inside the DATEDIF formula:

‘y’ – to find the difference in terms of complete years. 

‘m’ – to find the difference in terms of complete months. 

‘d’ – to find the difference in terms of complete days.

‘ym’ – to find the difference in months, ignoring months. 

‘md’ – to find the difference in days, ignoring months

‘yd’ – to find the difference in days, ignoring years. 

Note: Excel will not display or suggest the syntax for DATEDIF as it does for other normal functions.  

Also Read:

How to Use Goal Seek in Excel? (3 Simple Examples)

How to Insert Multiple Rows in Excel? The 4 Best Methods

How to Autofit Excel Cells? 3 Best Methods

How to Use the Excel DATEDIF Function?

The following examples will help you appreciate the formula’s usage better. I recommend you open a rough spreadsheet and follow along to gain a better understanding.

Example 1: Common Uses

In this example, column D contains the date March 1, 2019 and column E contains the date April 5, 2021. In column F, we can use the following variations of the DATEDIF function to calculate the difference in years, months and days respectively.

F5=DATEDIF(D5,E5,”y”) // returns 2

F6=DATEDIF(D6,E6,”m”) // returns 25

F7=DATEDIF(D7,E7,”d”) // returns 766

Excel DATEDIF Basic Use
Excel DATEDIF Basic Use

Example 2: Calculate the Difference in Days Using DATEDIF

In this example, we use the DATEDIF formula to calculate the difference in days between dates. Please note that you can use three different time units for days inside the DATEDIF function: (1) total days, (2) days ignoring years, and (3) days ignoring months and years. 

=DATEDIF(D11,E11,”d”) //  returns the difference in total days i.e 766 days

=DATEDIF(D12,E12,”yd”) // returns the difference in days ignoring years i.e 35 days

=DATEDIF(D13,E13,”md”) // returns the difference in days ignoring months and years i.e 4 days

Excel DATEDIF - Difference in days
Excel DATEDIF – Difference in days

Example 3: Calculate the Difference in Months Using DATEDIF

Use the DATEDIF formula to calculate the difference in months between dates. You can use two different time units for months inside the DATEDIF formula: (1) complete months and (2) in terms of months but ignoring years

=DATEDIF(D17,E17,”m”) // result: difference in complete months

=DATEDIF(D18,E18,”ym”) // result: difference in terms of months but ignoring years 

Using DATEDIF to find difference in months
Using DATEDIF to find the difference in months

Example 4: Calculate the Difference in Years Using DATEDIF

Use the DATEDIF formula to calculate the difference in years between two dates. 

=DATEDIF(D21,E21,”y”) // difference in complete years

DATEDIF and YEARFRAC to find the difference in years

A small caveat: Since, DATEDIF rounds off the difference to the next lowest value, it is advisable to use YEARFRAC to find a more accurate difference in years.

Important Note:

The DATEDIF function returns the number of complete days, months, or years by rounding them off to the next smallest value. This means that it may give incorrect results when calculating the date difference in some cases. It is advisable to be careful while using DATEDIF and double-check the results before proceeding further.

The #NUM! error usually arises when the start_date is greater than the end_date.

The #VALUE! Error usually occurs when invalid arguments are used inside the formula. 

Suggested Reads:

How to Group Worksheets in Excel? (In 3 Simple Steps)

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)

Closing Thoughts

In this short guide, I have shown how to properly use the DATEDIF function in Excel. I hope you find this very useful in your spreadsheet calculations. If you have any questions about this or any other Excel feature, feel free to let us know in the comments. We’re always happy to help. 

Visit our free Excel Resources Centre for more high-quality Excel guides. 

Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. 

You can train your entire team in Excel and other business software, for a single low monthly fee by clicking here.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Similar Posts

How to use the OFFSET function in Excel

How to Set Costs for Tasks in Microsoft Project 2016

How to Make a Scatter Plot in Excel? 4 Easy Steps

How to Print Gridlines in Excel?

Advanced Word Tutorial: Tips and Tricks in MS Word – Recorded Webinar

Excel Quick Analysis Tool – The Best Guide (5 Examples)

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)