Table of Contents
- INTRODUCTION
- PURPOSE OF DATEDIF FUNCTION IN EXCEL
- PREREQUISITES TO LEARN DATEDIF FUNCTION
- SYNTAX: DATEDIF FUNCTION IN EXCEL
- CAN’T FIND DATEDIF IN MY EXCEL WHY?
- PROBLEM IN DATEDIF FUNCTION
- EXAMPLE:DATEDIF FUNCTION IN EXCEL
INTRODUCTION
DATEDIF FUNCTION is a DATE BASED function present in Excel.It is specially kept in Excel to support the older versions of LOTUS 1-2-3.
DATEDIF function is very useful in calculating any age in Excel. The function takes the arguments of start date, end date and the option for the output type.
DATEDIF function is not found in the standard function library of Excel because of some erroneous results it might show in certain circumstances which will be discussed later in the article.
DATEDIF FUNCTION CALCULATES THE AGE IN THE FORM OF YEARS OR MONTHS OR DAYS AS SPECIFIED, BETWEEN THE TWO GIVEN DATES.
DATEDIF function is quite useful to find the difference between the two dates easily.
PURPOSE OF DATEDIF FUNCTION IN EXCEL
DATEDIF FUNCTION CALCULATES THE NUMBER OF DAYS , MONTH OR YEARS BETWEEN THE TWO SPECIFIED DATES.
This function calculates the age if we provide it by a starting date, an ending date and the output type[ the form in which we need the result. It can be years, or months or days etc.]
PREREQUISITES TO LEARN DATEDIF FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- If we know the way Excel handles the DATES, the function becomes a bit easier to learn.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: DATEDIF FUNCTION IN EXCEL
The Syntax for the DATEDIF function in Excel is
=DATEDIF(START_DATE, END_DATE, OUTPUT_OPTION)
START_DATE is the starting date from which the counting would start or the starting date of the period. This argument needs to be given in “” or as a date number. It can also be a result of any function resulting in Date.
END_DATE is the ending date till which the counting would be done or the end date of the period. This argument should also be given in “” or as a date number.
OUTPUT_OPTION is the unit in which the result is required. Refer the following table.
Use the option as per the result required.
The type of information that you want returned, where:
| OPTION | RESULT |
|---|---|
| “Y“ | The number of complete years in the period. |
| “M“ | The number of complete months in the period. |
| “D“ | The number of days in the period. |
| “MD“ | The difference between the days in start_date and end_date. The months and years of the dates are ignored.This option is known to have issues which will be discussed later. |
| “YM“ | The difference between the months in start_date and end_date. The days and years of the dates are ignored |
| “YD“ | The difference between the days of start_date and end_date. The years of the dates are ignored. |
CAN’T FIND DATEDIF IN MY EXCEL WHY?
When you first learnt that there is a function called DATEDIF, you must have tried it searching in your Excel and probably you’d have failed.
The reason is that DATEDIF function is not kept in the standard function library which is found under the formulas TAB.
BUT DON’T WORRY. IF YOU KNOW THE SYNTAX OF THE FUNCTION, IT’LL WORK IN ALL THE EXCEL VERSION BUT EXCEL
WON’T SHOW ANY HELP FOR THAT, AS IT SHOWS FOR OTHERS.
Excel kept this function for LOTUS 1 2 3 compatibility and as it is known to have certain issues, it doesn’t recommend this function although
the function support is present.
SO GO AHEAD, AND USE THIS FUNCTION BUT BE CAREFUL ABOUT THE RESULT.
PROBLEM IN DATEDIF FUNCTION
When we used the DATEDIF function with the “md” option, it can give us problematic answers which can be 0 or negative too. So this is the reason why it is not recommended especially this option.
Let us have an example.
md option provides us the difference of the days by ignoring the month and the year. But this function fails sometimes.
e.g. try this.
Find the difference between the following dates and check if the answer is correct or not.
| START DATE | END DATE |
| 25-01-2014 | 25-02-2014 |
| 22-01-2019 | 01-02-2019 |
| 25-01-2020 | 27-02-2020 |
EXAMPLE:DATEDIF FUNCTION IN EXCEL
DATA SAMPLE
For the sake of understanding, let us find out the difference of various combinations to understand the working of the datedif function.
| START DATE | END DATE | OPTION | |
| a | 19-06-2019 | TODAY | Y |
| b | 02-01-2017 | 02-02-2020 | M |
| c | 28-06-2020 | 11-03-2020 | D |
| d | 27-08-2019 | 28-06-2020 | YM |
| e | 29-12-2018 | CURRENT DATE | YD |
| f | 01-01-2020 | 25-01-2020 | MD |
STEPS TO USE DATEDIF FUNCTION
Six examples have been shown to use the datedif function.
The function used is also shown . Let us discuss the examples.
a. START DATE is 19-06-2019 END DATE is TODAY i.e. whatever date is today. It is 28.6.2020 OPTION is Y which means that we want to retrieve the number of years between the dates.
The function used is =DATEDIF(H47,TODAY(),”Y”) . The result comes out to be 1 which is correct as there is the difference of one year only.
b. START DATE is 02-01-2017 END DATE is 02-02-2020 . OPTION is M which means that we want to retrieve
the number of months between the dates.The function used is =DATEDIF(H48,I48,”M”) . The result comes out to be 37 which is correct.
c. START DATE is 11-03-2020 END DATE is 28-06-2020. OPTION is D which means that we want to retrieve
the number of days between the dates.The function used is =DATEDIF(H49,I49,”D”) . The result comes out to be 109 which is correct.
d. START DATE is 27-08-2019 END DATE is 28-06-2020. OPTION is YM which means that we want to retrieve
the number of months between the dates. Years and days are ignored.The function used is =DATEDIF(H50,I50,”YM”) . The result comes out to be 10 which is correct.
e. START DATE is 29-12-2018 END DATE is CURRENT DATE. OPTION is YD which means that we want to retrieve
the number of days between the dates. The years are ignored for the calculations.The function used is =DATEDIF(H51,TODAY(),”YD”) . The result comes out to be 181 which is correct.
f. START DATE is 01-01-2020 END DATE is 25-01-2020. OPTION is MD which means that we want to retrieve
the number of days between the dates. The Month and Year are ignored.The function used is =DATEDIF(H52,I52,”MD”) . The result comes out to be 24 which is correct.There are several cases where this option can result in wrong results. There it is not advisable to use this option.