HOW TO CALCULATE AGE OR NUMBER OF DAYS BETWEEN TWO DATES

INTRODUCTION

DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time.


It is quite obvious because when we don’t know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not.

Here we are talking about Dates and Time in Excel. These are the tricky formats which we need frequently in our reports or charts.
Many times, we need to do operations on them. We need to put them in the conditions to trigger some event which makes it very important for us to learn the exact procedures to perform a task concerned with the dates and time.


In this article we would learn different tricks and methods to handle and manipulate Dates and Time formats so that they don’t mess up with our reports.
One of the problem is to find out the days between two dates or finding out the exact age of any person or event.So, in this article we would try to learn about the both.


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.

CALCULATING NUMBER OF DAYS BETWEEN TWO DATES

CONCEPT:

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. 

COMPLETE INFORMATION OF DATEDIF FUNCTION CAN BE FOUND HERE.

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.

STEPS TO FIND OUT THE NUMBER OF DAYS BETWEEN TWO DATES:

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

STEPS TO FIND OUT THE NUMBER OF MONTHS BETWEEN TWO DATES:

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

STEPS TO FIND OUT THE NUMBER OF YEARS

BETWEEN TWO DATES:

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

FINDING THE DATE DIFFERENCE OR AGE IN EXCEL

FINDING THE EXACT AGE IN YEARS, MONTHS AND DAYS IN EXCEL

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

STEPS TO FIND OUT THE EXACT AGE

  • 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”
FINDING THE EXACT AGE IN EXCEL