How to Calculate Age from Date of Birth in MS Excel

If you have a list of people with their dates of birth, it is possible to calculate their age using a few tricks in MS Excel. While there are no specific functions to calculate age in MS Excel, these methods will work just as good.

4 Methods of calculating age from date of birth using Microsoft Excel:

  • Use the DATEDIF function
  • Use the YEARFRAC function
  • Use Microsoft VBA code
  • Use the logical formula

Let us begin by looking at how to use these methods to get the age from a person’s date of birth.


Method #1: Use the DATEDIF Function

This method makes use of a function to calculate the elapsed time between today and the date of birth. An added advantage of this method is that you can also calculate the number of months or days that have passed since the Date of Birth.

Step #1: Open your MS Excel workbook

Create a new workbook and enter the necessary data, or just open an existing workbook.

Step #2: Place the cursor

Click on the cell where you would like to display the age, to place the cursor. 

You can confirm the cell by checking the Name box in the top left corner.

Step #3: Enter the Excel formula

The Excel formula is =DATEDIF(A2, TODAY(), “Y”) 

This means that you will start typing the formula into the cell, but replace A2 with the cell that contains the birth date in your spreadsheet. In our example, it is cell A2 that contains the date of birth.

This formula will help you calculate the age in years. You can also calculate months and days by changing one parameter in the function, for example:

  • To calculate the number of months, use =DATEDIF(A2, TODAY(), “M”)
  • To calculate the number of days, use =DATEDIF(A2,TODAY(),”D”)
  • To calculate the number of years, months, and days, use this complete formula: 

=(DATEDIF(A2,TODAY(),”Y”) & ” Years, ” & DATEDIF(A2,TODAY(),”M”) & ” Months, & ” & DATEDIF(A2,TODAY(),”D”) & ” Days “)

Step #4: Press Enter

Once you have type the formula, press Enter to see the formula in action.


Method #2: Use the YEARFRAC Function

This is a simple method that uses an Excel function to calculate age in fractions. We also provide an additional function that will remove the decimals.

Step #1: Open your MS Excel workbook

Open an existing workbook or create a new one and enter the required data.

Step #2: Place the cursor

Click on the cell where you would like to display the age, to place the cursor. 

You can confirm the cell by checking the Name box in the top left corner.

Step #3: Enter the Excel formula

Type the Excel formula into the selected cell. 

The formula is =YEARFRAC(A3, TODAY(), 1)

This means that you will start typing the formula into the cell, but replace A3 with the cell that contains the birth date in your spreadsheet. In our example, cell A3 contains the date of birth.

Step #4: Press Enter

Once you’ve entered the formula, press Enter

You can now see the number of years in decimals.

To show the age completed in years, you can remove the decimals. 

To remove the decimals you can use this slightly longer formula. 

=ROUNDDOWN(YEARFRAC(A3, TODAY(), 1), 0) 

Now, press Enter

You can see that the age is rounded off.

OR

Alternatively, on the Home Tab ribbon, you will find two icons which can increase the decimal, or decrease it. By clicking on the ‘Decrease decimal’ icon repeatedly, less decimals will show.


Method #3: Using Microsoft VBA Code

This is a method that uses Microsoft’s Visual Basic application to get the age. It uses programming code to calculate the age, and is useful to keep this code in a spreadsheet where you will continuously need to calculate people’s age.

Step #1: Open your MS Excel workbook

Create a new workbook or open an existing one.

Step #2: Open Microsoft Visual Basic

Go to the Developer tab and click on the Visual Basic icon to the left of the ribbon.

If your MS Excel does not have a Developer tab, follow the instructions in this article to add it:

How to add developer Tab to MS Word.

Step #3: Insert a new module

Once the Microsoft Visual Basic for Applications dialog box is open, click on Insert

Select Module from the drop-down.

Step #4: Copy and paste the code

This code takes in the date of birth as a parameter and gives the number of completed years. Copy the below code and paste it into the Module 1 dialog box.

Function Age(varBirthDate As Variant) As Integer 
 Dim varAge As Variant 
 
 If IsNull(varBirthdate) then Age = 0: Exit Function 
 
 varAge = DateDiff("yyyy", varBirthDate, Now) 
 If Date < DateSerial(Year(Now), Month(varBirthDate), _ 
 Day(varBirthDate)) Then 
 varAge = varAge - 1 
 End If 
 Age = CInt(varAge) 
 End Function

Save this by simply closing the Visual Basic window.

Step #5: Type in the module function

Click on the cell where you want to see the age.

Type =Age(A4) into the cell. 

Replace A4 with the cell containing the date of birth on your spreadsheet.

Press Enter. You can now see the age.


Method #4: Using the Logical Formula

This is a simple method that uses mathematical logic to calculate the age completed in years. However, this method is not ideal as it doesn’t take into account some situations, for example someone’s age born on February 29

Step #1: Open your MS Excel workbook

Open a blank workbook and fill in the necessary data. If you already have an existing workbook, open that one.

Step #2: Place the cursor

Click on an appropriate cell where you want the age to be displayed. 

You can confirm the cell by checking the Name box in the top left corner.

Step #3: Enter the mathematical formula 

The formula is =INT((TODAY()-A5)/365)

This means that you will start typing the formula into the cell, but replace A5 with the cell that contains the birth date in your spreadsheet. In our example, it is cell A5 that contains the date of birth.

Step #4: Press Enter

After entering the formula, press Enter.

Your age will be displayed as per the number of years completed.


Conclusion

Calculating the age from the date of birth is easy. You can calculate the age by years, months, or even days. You can use any of the above methods to calculate a person’s age in no time.

Leave a Comment