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.