The DATEDIF is a date function that is generally used to find the number of total days, months, and years between two dates. It takes two dates as an argument with one keyword to specify which type of difference is required as an output. The output of the function will always be an integer form.
DatedIF Function in Excel
The DATEDIF date function is used to find the difference between two dates(age in simple language). These differences can be in any form years, months, or days. It takes two dates as input with one keyword, where the one is the Start_date and the Second is the End_date, and the keyword is used to determine which type of difference is desired for the output.
Syntax
DATEDIF(start_date,End_date,unit)
Arguments
Datedif is the date function which is the in-built function in Excel.
Start_date: The beginning date from where you want to calculate the difference.
End_date: It is the last date of the period.
Unit: The type of information that you want to be returned.
|
"Y" or "y"
| The difference in complete years
|
"M" or "m"
| The difference in complete months
|
"D" or "d"
| The difference in complete days
|
"MD" or "md"
| The difference in days, ignoring months and years
|
"YM" or "ym"
| The difference in months, ignoring years
|
"YD" or "yd"
| The difference in days, ignoring years.
|
The time unit to utilize while ascertaining the difference between two dates.
Excel DATEDIF Formula
To find the difference between the two dates in Excel, the important thing is to provide the start and end dates to the DATEDIF function. This can be done in multiple ways as follows:
Cell References
This is the easiest way to make a DATEDIF formula in Excel is to input two valid dates in separate cells and refer to those cells. For example, the following formula counts the number of days between the dates in cells A1 and B1:
=DATEDIF(A1, B1, "d")
Text strings
Excel takes dates as a string for example "1-Jan-2023", "1/1/2023", "January 1, 2023", etc. The dates as text strings enclosed in quotation marks can be typed directly in a formula's arguments. For instance, this is how you can calculateinto the number of months between the specified dates:
=DATEDIF("1/1/2023", "12/31/2025", "m")
Serial numbers
Microsoft Excel stores each date as a serial number beginning with January 1, 1900, you use numbers corresponding to the dates. But this method is not reliable because date numbering varies on different computer systems. In the 1900 date system, you can use the below formula to find the number of years between two dates, 1-Jan-2023 and 31-Dec-2025:
=DATEDIF(44927, 46022, "y")
Results of other functions
To find out how many days there are between today and 20 May 2025, this is the formula to use.
=DATEDIF(TODAY(), "5/20/2025", "d")
How to Use DATEDIF Function in Excel
There are three formulas to calculate year, month, and days:
For the total number of years = datedif(a3,b3,"y")
Example 1: The Total Number of Years Between the Two Dates
Step 1: Enter the Start Date and End Date.
Step 2: Now enter the formula in a cell where you want to calculate the age.
This formula will give you the total number of years between the two dates.
Example 2: The Total Number of Months Between the Two Dates
For a total number of months
=datedif(a3,b3, "m")
Where the first argument is the field and the "m" represents the month.
Step 1: Enter the Start_date and End_date.
Step 2: Enter the formula in a cell where you want the number of months between two dates.
In the below image, you can see the total number of months between the two dates.
Example 3: Number of Days Between Two Dates
For the total number of days =datedif(a3,b3, "d")
Where the first argument is the field and the "d" represents the day.
Step 1: Enter the Start_date and End_date.
Step 2: Enter the formula in the cell where you want the total number of months.
Total number of days between the two dates (Start_date and End_date).
Arguments
1. The start date is typed in DDMMYYYY format. You can select the date format in the home tab from the number.
2. The last date or end date is typed in DDMMYYYY format.
3. The "y" for the years unit, "m" for the month unit, and "d" for the days unit.
4. The sixth type of unit ("y", "m", "d", ) ("ym","md", "yd")
- "ym" means the number of months without years, "md" means the number of days without years and months,
- "yd" means the number of days without years
Example 4: Find the Number of Months Excluding Days and Years
By this function, we can find our age (in years months, and days) from our date of birth to the current date. Suppose any person's birth date is 26 January 1969 and the current date is 05 May 2020 (exact in years, months, and days ).
Step 1: Enter the DOB of the Person and the Current date(You can use Today() instead of the current date).
Step 2: Now Use the formula (=datedif(dob,current_date) in a cell where you want the age of the person.
In the image given below, we can see the total number of months excluding years and days.
Example 5: Total Number of Days Excluding Months and Years
Step 1: Enter the DOB and current date of the person.
Step 2: Enter the formula (=datedif(DOB, Current_date) in a cell, where you want the age in days excluding years and months.
You can see the output in the below image, the number of days excluding years and months.
What are the advantages of using the DATEDIF function?
1. It is easy to use and understand.
2. This function is useful to see the difference between two dates in Years, Months, Days.
What are the Disadvantages of using the DATEDIF function?
- Excel supports only six types of intervals in DATEDIF function.
- It cannot give the output for so many units like the first day of the week.
- May lead to confusion because of too many parameters in a single formula.
- It might give wrong output if correct date format has not been used.
Similar Reads
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a project—like developers and designers—understand how the system is organized and how its components interact
12 min read
Steady State Response In this article, we are going to discuss the steady-state response. We will see what is steady state response in Time domain analysis. We will then discuss some of the standard test signals used in finding the response of a response. We also discuss the first-order response for different signals. We
9 min read
Backpropagation in Neural Network Back Propagation is also known as "Backward Propagation of Errors" is a method used to train neural network . Its goal is to reduce the difference between the model’s predicted output and the actual output by adjusting the weights and biases in the network.It works iteratively to adjust weights and
9 min read
Polymorphism in Java Polymorphism in Java is one of the core concepts in object-oriented programming (OOP) that allows objects to behave differently based on their specific class type. The word polymorphism means having many forms, and it comes from the Greek words poly (many) and morph (forms), this means one entity ca
7 min read
3-Phase Inverter An inverter is a fundamental electrical device designed primarily for the conversion of direct current into alternating current . This versatile device , also known as a variable frequency drive , plays a vital role in a wide range of applications , including variable frequency drives and high power
13 min read
What is Vacuum Circuit Breaker? A vacuum circuit breaker is a type of breaker that utilizes a vacuum as the medium to extinguish electrical arcs. Within this circuit breaker, there is a vacuum interrupter that houses the stationary and mobile contacts in a permanently sealed enclosure. When the contacts are separated in a high vac
13 min read
AVL Tree Data Structure An AVL tree defined as a self-balancing Binary Search Tree (BST) where the difference between heights of left and right subtrees for any node cannot be more than one. The absolute difference between the heights of the left subtree and the right subtree for any node is known as the balance factor of
4 min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read