 PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

# EXCEL FUNCTIONS-DATEDIF

## 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.

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.

## 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.

• ## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

• EXCEL
• JAVASCRIPT
• MORE TO COME…

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]