Open In App

How to Calculate Age Without DATEDIF Function

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we’ll explore different ways of finding age in Excel, other than the usual way of finding age using the DATEDIF function. However there is no special function to calculate age in Excel, But there are many different functions that can be used to convert the birth date into age. For this, we can use one of the two ways:

  • Using simple mathematics
  • Using the YEARFRAC function
  • Using the YEAR function
  • Using Excel’s Date Functions

How to Calculate Age in Excel Using Simple Math

We can easily find the age of a person using simple Math. All we need to do is, subtract the DOB from the current date and divide it by 365.25

So, the formula would be:

= (TODAY() – Date_of_birth)/365.25

Follow the below steps to Find Age using Simple Math:

Step 1: Enter DOB or Start_date into the cell.

Step 2: Now select a cell where you want to calculate the age. 

Step 3: Write the formula =(TODAY()-A2)/365.25.

Example:

Enter the DOB of the person.

In this case, the formula to calculate age would be:

=(TODAY() – A2)/ 365.25

use the formula =(TODAY()-A2/365.25

With the current date of 26.04.2021, the age of the above person is 16 years.

Remarks:

This formula returns the age in decimal form. In order to get age in integer form, we can wrap the formula in the INT function. 

Syntax

=INT ((TODAY() – date_of_birth)/365.25)

The problem with this formula is that it will produce the wrong result if you try to find the age of someone who hasn’t lived through a leap year.

How to Calculate Age Using YearFrac Function

YEARFRAC function in Excel returns a decimal value that represents fractional years between two dates i.e., the number of whole days between two dates. We can use this function to calculate age.

Syntax:

= INT(YEARFRAC( date_of_birth , Today(),[Basis]))

Arguments:

YEARFRAC: This is the inbuilt function in Excel that is used to find the age.

date_of_birth: This is the Start date. 

Today(): This is also an inbuilt function in Excel to find the end date.

Basis: Use basis 1 which tells Excel to divide the actual number of days per month by the actual number of days per year. This is the optional part of the formula.

Follow the steps to calculate the age using YEARFRAC():

Step 1: Enter the DOB of a person in the cell.

Step 2: Now Select the cell where you want the age of the person.

Step 3: Now write the formula in the cell =INT(YEARFRAC(A2,TODAY()))

Example:

Using YEARFRAC function to calculate age.

How to Calculate Age Using the YEAR() Function

You can also use the YEAR() with NOW() to calculate the age in Excel.

Follow the steps to calculate age with YEAR() and NOW()  functions

Step 1: Write the DOB of a person in a particular cell.

Step 2: Select the cell, where you want the age of the person.

Step 3: Write the formula =(YEAR(NOW())- YEAR(A2))

Age Formula

=(YEAR(NOW() ) – YEAR( start_date))

calculate age using YEAR().

The YEAR function gets the year from NOW() AND A2 cell and subtracts the two dates. The returned result is the number of years of age.

Note: You can also use TODAY() Function instead of NOW() Function.

For example: =(Year(Today()-Year(Start_date))

You can also write the year directly.

For example: =(Year(Today()-2000)

How to Calculate Age Using Excel’s Date Functions (DATE, YEAR, MONTH, and DAY) 

Function DAY returns the day of the month. Function YEAR returns the year of the date, and function MONTH returns the month of the date. 

The formula helps us to calculate the difference between the years involved, calculate the result by 12, and then add a difference between the months involved.

Formula: 

IF (DAY(A2)>=DAY(A1),0 , -1)+ (YEAR(B2) – YEAR(A1))*12 + MONTH(A2) – MONTH(A1) &” months old”

FAQs on Calculate Age in Excel

How to Calculate Age with DATEDIF in Excel?

Syntax: DATEDIF(start_date, end_date, unit)

start_date: This is the start date.

end_date: This is the end date, you can use today() function also.

unit: There are various options like “Y” will return number of complete years between two dates, “M” will return number of complete months between the two dates, “D” will return the number of complete days between the two dates.

How to Calculate Age in dd/mm/yyyy?

To calculate age in dd/mm/yyyy use the following steps:

Step 1: Select the cell, where you want the age.

Step 2: Type the formula =Datedif(Start_date,Today(),”Y”)&”Years,”&datedif(start_date,Today(),”YM”)&”Months,”&Datedif(start_date,Today(),”MD”)&”Days.

How to quickly Calculate Age in Excel Without Using the Datedif Function?

We can calculate age in Excel without using Datedif by using Yearfrac function.

Syntax of Yearfrac:

=Yearfrac(Start_date,End_date,[Basis])

What is a Basic Formula to Calculate Age in Excel?

Basic formula is: =( Today() – Date_of_Birth)/365


Last Updated : 14 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads