Convert birthday to age in years

To convert a date of birth to age, in years, we can apply the Excel DATEDIF and NOW functions

Example: Convert birthday to age in years

Convert birthday to age in years

METHOD 1. Convert birthday to age in years

EXCEL

=DATEDIF(B5,NOW(),"y")
The formula returns the number of years from the specified date, which is the birthdate that we selected in this example, to the current date that is specified by the Excel NOW function.

METHOD 1. Convert birthday to age in years using VBA

VBA

Sub Convert_birthday_to_age_in_years()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
Set date1 = ws.Range("B5")
'convert the birthdate to age in years
ws.Range("C5") = DateDiff("yyyy", date1, Now)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Birthday: If using the exact VBA code the birthday needs to be captured in cell ("B5").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Birthday: Select the cell that captures the birthday by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

METHOD 2. Convert birthday to age in years using VBA with a formula function

VBA

Sub Convert_birthday_to_age_in_years()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'convert the birthdate to age in years
ws.Range("C5").Formula = "=DATEDIF(B5,Now(),""y"")"

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Birthday: If using the exact VBA code the birthday needs to be captured in cell ("B5").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Birthday: Select the cell that captures the birthday by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

Explanation about the formula used to convert birthday to age in years

EXPLANATION

EXPLANATION
To convert a date of birth to age, in years, we can apply the Excel DATEDIF and NOW functions. If using VBA, we either use a DateDiff function or apply the DATEDIF formula.
FORMULAS
=DATEDIF(birthdate,NOW(),"y")

ARGUMENTS
birhtdate: The birthdate of the person who's age you want to calculate.