How to Calculate Age in Google Sheets from a Date of Birth

The following formulas are essential tools for age calculation in Google Sheets or Excel:

Formula 1: Calculate Age in Whole Years (e.g. 23 years)

=DATEDIF(A2,NOW(),"y")

Formula 2: Calculate Age in Decimal Years (e.g. 23.567 years)

=YEARFRAC(A2,NOW())

Formula 3: Calculate Age in Years, Months, Days (e.g. 23 years, 6 months, 25 days)

=DATEDIF(A2,NOW(),"y")&" years, "&DATEDIF(A2,NOW(),"ym")&" months, "&DATEDIF(A2,NOW(),"md")&" days"

Each formula assumes that the date of birth is located in cell A2.

The application of these formulas is demonstrated clearly using a sample dataset. The following examples illustrate how to implement each formula practically, leveraging a list of dates of birth input into Column A of the Google Sheet. This visual aid clarifies the distinction between the outputs produced by the DATEDIF function and the YEARFRAC function.

Example 1: Calculating Age in Whole Years

The most frequent requirement for age calculation is determining the number of complete years an individual has lived. This approach provides a simple, integer output that is easy to read and use in reporting, rounding down to the last full year achieved. The DATEDIF function is perfectly suited for this task when utilizing the appropriate unit code.

To perform this calculation, we reference the individual’s date of birth in cell A2 as the starting point. The current date is dynamically supplied by the NOW function, ensuring real-time accuracy. By specifying the unit code "y" (for years), the DATEDIF function computes the difference and returns the whole number of years elapsed between the two dates.

For the date of birth found in cell A2, we input the formula into the corresponding output cell, B2. This single formula calculates the age in whole years.

The specific formula used to convert the date of birth in cell A2 to age in cell B2 is:

=DATEDIF(A2,NOW(),"y")

Once the formula is correctly entered in B2, the power of spreadsheet software allows for rapid replication. By simply clicking and dragging the formula down to the remaining cells in column B, the calculation is automatically applied to every corresponding date of birth in column A, efficiently generating the age for the entire dataset.

As observed in the resulting spreadsheet view, Column B displays the age for each birth date listed in column A, presented accurately in terms of complete years. This method is standard for general data reporting where fractional age is not required.

Example 2: Calculating Age in Decimal Years

In many analytical fields, especially statistics, finance, or actuarial science, precise age calculation is necessary, often requiring the age to be expressed as a decimal value rather than a rounded integer. This provides insight into the exact fraction of the year that has elapsed since the last birthday. For this purpose, the YEARFRAC function is significantly more appropriate than DATEDIF.

The YEARFRAC function calculates the fraction of the year represented by the number of whole days between two dates. Its basic syntax is =YEARFRAC(start_date, end_date, [day_count_convention]). The optional day_count_convention argument allows users to specify how the fraction should be calculated (e.g., based on a 360-day year, a 365-day year, or actual days). For standard age calculation in Google Sheets, omitting this convention often defaults to a suitable basis, or using 1 for Actual/Actual is common for precise calendar-based calculations.

Applying this to our data, with the date of birth in A2 and the current time supplied by NOW function, we input the formula into cell B2.

=YEARFRAC(A2,NOW())

As with the previous example, to populate the rest of the column, we simply click and drag this formula down to apply it to all remaining cells in column B:

The resulting Column B now displays the age for each birth date in column A in terms of decimal years. For instance, an age displayed as 23.567 years indicates that the person is 23 years old and has completed roughly 56.7% of their 24th year.

Example 3: Detailed Age Breakdown in Years, Months, and Days

For applications such as legal documentation or highly precise biographical records, it is often necessary to calculate age not just in years, but also specifying the remaining months and days since the last birthday. This requires a composite formula that utilizes the DATEDIF function multiple times, each time using a different unit code, and then concatenating the results into a readable string.

This complex calculation relies on specific unit codes within DATEDIF:

• "Y": Calculates the difference in whole years.
• "YM": Calculates the remaining months after subtracting the whole years.
• "MD": Calculates the remaining days after subtracting both the whole years and the whole months.

By combining these three independent DATEDIF function calls with the concatenation operator (&), we construct a single output string that provides the full age description (e.g., “23 years, 6 months, 25 days”).

To convert the date of birth in A2 to this detailed age format, we type the following comprehensive formula into cell B2:

=DATEDIF(A2,NOW(),"y")&" years, "&DATEDIF(A2,NOW(),"ym")&" months, "&DATEDIF(A2,NOW(),"md")&" days"

After entering the formula into the first cell (B2), we efficiently apply it to the entire column by clicking and dragging the formula down to each remaining cell in column B:

The final result shows that Column B clearly displays the age for each date of birth in column A, expressed in the highly detailed format of years, months, and days. This provides the most granular view of the time elapsed since the start date.

Advanced Considerations: Why DATEDIF is Preferred

A common mistake for users new to spreadsheet date functions is attempting to calculate age by simply subtracting the date of birth from the current date and dividing by 365.25. While simple subtraction =(NOW() - A2) / 365.25 might seem intuitive, this method is highly prone to errors, particularly around leap years and the variation in day counts between months.

The DATEDIF function, conversely, handles these complexities internally, relying on established calendar rules to ensure the calculation is precise. When calculating whole years, for example, DATEDIF("Y") only increments the year count when the anniversary date has genuinely passed. Simple division of days does not account for the exact timing of the anniversary relative to the current date.

Furthermore, for calculating months and days since the last anniversary (using "YM" and "MD" units), there is no simple arithmetic equivalent that reliably manages the variable length of months (28, 29, 30, or 31 days). The built-in logic of the DATEDIF function ensures calendar accuracy, making it the definitive, reliable tool for professional age calculation in Google Sheets.

Summary of Age Calculation Methods

Calculating age accurately in Google Sheets is a straightforward process when utilizing the appropriate date functions. Whether you require a basic whole number, a statistical decimal value, or a comprehensive breakdown, DATEDIF and YEARFRAC provide the necessary tools for precision and reliability.

Here is a quick recap of the calculation methods covered:

1. Whole Years: Use =DATEDIF(A2, NOW(), "Y"). Ideal for simple reporting and demographic summaries.
2. Decimal Years: Use =YEARFRAC(A2, NOW()). Essential for actuarial and statistical analysis requiring high temporal granularity.
3. Years, Months, Days: Use the concatenated DATEDIF formula utilizing the “Y”, “YM”, and “MD” units. Provides the most detailed, human-readable output.

By mastering these three formulas, users can efficiently manage and analyze date-related data within their spreadsheets. This eliminates the need for manual updating or complex external calculations, making data management both dynamic and accurate.

If you are interested in exploring further applications of date and time manipulation within Google Sheets, the following tutorials explain how to perform other common tasks: