Column A has birth dates. Return current age in complete years

公式

=DATEDIF(A2, TODAY(), "Y")

说明

DATEDIF with "Y" counts whole years between the birthday and today. It is undocumented in some Excel UIs but works in Excel, Sheets, and WPS.

步骤

  1. A2 must be a real date, not text.
  2. TODAY() is the end date.
  3. "Y" returns completed years.

变体

Years and months

"YM" is leftover months after whole years.

=DATEDIF(A2, TODAY(), "Y")&" years, "&DATEDIF(A2, TODAY(), "YM")&" months"

Without DATEDIF

Approximate. Leap years make this less accurate than DATEDIF.

=INT((TODAY()-A2)/365.25)

Age at a report date in B1

Lock B1 so every row uses the same as-of date.

=DATEDIF(A2, $B$1, "Y")