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.