Menu

BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.CLICK HERE TO VISIT.

Age or year,month and days between two days need to be calculated frequently in Excel. We can do that in many ways but we do have few functions to help us directly.

USING DATEDIF FUNCTION

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

**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**

“**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.

Use DATEDIF FUNCTION with m , y or d option to find out the age in months , years or days simply by putting in the example.

- Select the cell where you want the result.
- Enter the formula =DATEDIF( START DATE CELL , END DATE CELL , “D”).
- For our example as shown in the picture below, the formula will be =DATEDIF(L9,M9,”D”) because the start date is in L9 , end date is in M9 and we want the days so the option chosen is “D”).
- The result will appear as the number of days as difference.

- Select the cell where you want the result.
- Enter the formula =DATEDIF( START DATE CELL , END DATE CELL , “M”).
- For our example as shown in the picture below, the formula will be =DATEDIF(L7,M7,”M”) because the start date is in L7 , end date is in M7 and we want the days so the option chosen is “M”).
- The result will appear as the number of months as difference.

- Select the cell where you want the result.
- Enter the formula =DATEDIF( START DATE CELL , END DATE CELL , “y”).
- For our example as shown in the picture below, the formula will be =DATEDIF(L8,M8,”Y”) because the start date is in L8 , end date is in M8 and we want the days so the option chosen is “Y”).
- The result will appear as the number of days as difference.

*THE EXAMPLES ARE SHOWN IN THE PICTURE BELOW.

Let us find out the exact age of any person in Excel.

Say, the date of birth of a person is 21-06-2000

Find his exact age today i.e. on 2-07-2020

- Put the age of the person in any cell.
- Current date will be taken from the function TODAY().
- Enter the following formula in the cell where the age is needed.
- =”THE EXACT AGE OF DAVID ON ” &TEXT(TODAY(),”DD/MM/YY”)&” IS ” &DATEDIF(C17,TODAY(),”Y”)&” YEARS, “&DATEDIF(C17,TODAY(),”YM”)&” MONTHS AND , “&DATEDIF(C17,TODAY(),”MD”)&” DAYS”

OTHER WAYS TO REACH THIS ARTICLE

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

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.