Общая формула
=DATEDIF(birthdate,TODAY(),"y")
Резюме
Чтобы рассчитать возраст по дате рождения, вы можете использовать функцию РАЗНДАТ вместе с функцией СЕГОДНЯ. В показанном примере формула в ячейке E5, скопированная вниз, имеет следующий вид:
=DATEDIF(D5,TODAY(),"y")
Поскольку СЕГОДНЯ всегда возвращает текущую дату, формула продолжит вычислять правильный возраст в будущем.
Объяснение
Функция РАЗНДАТ (Дата + Разница) - небольшая аномалия в Excel. Функция совместимости, изначально пришедшая из Lotus 1-2-3, Excel не помогает предоставлять аргументы при вводе функции. Однако РАЗНДАТ работает во всех современных версиях Excel и является полезной функцией для вычисления интервала между двумя датами в годах, месяцах и днях.
В показанном примере цель - вычислить возраст в годах. Формула в E5:
=DATEDIF(D5,TODAY(),"y")
Первые два аргумента для DATEDIF - start_date и end_date. В примере дата начала берется из ячейки D5 (15 мая 2001 г.). Дата окончания генерируется функцией СЕГОДНЯ. СЕГОДНЯ всегда возвращает текущую дату в Excel. На момент написания этой статьи текущая дата - 24 ноября 2020 года. Последний аргумент в DATEDIF указывает единицу времени. Функция РАЗНДАТ поддерживает здесь несколько параметров, но в этом примере целью является возраст в целых годах, поэтому мы используем «y», чтобы указать полные годы.
На этом этапе мы можем переписать формулу, как показано ниже:
=DATEDIF("15-May-2001","24-Nov-2020", "y")
Поскольку даты в Excel на самом деле являются серийными номерами, исходными значениями являются:
=DATEDIF(37026,44159,"y")
С этими входными данными DATEDIF возвращает 19 в качестве окончательного результата.
Возраст на конкретную дату
Чтобы рассчитать возраст на определенную дату, замените функцию СЕГОДНЯ на дату задания. Простой и безопасный способ жестко указать конкретную дату в формуле - использовать функцию ДАТА. Например, чтобы рассчитать возраст на 1 января 2021 года, вы можете использовать такую формулу:
=DATEDIF(D5,DATE(2022,1,1),"y") // returns 20
Эта формула вернет возраст Майкла Чанга на 1 января 2022 года, то есть 20 лет.
Взрослый или несовершеннолетний
Чтобы проверить дату рождения и вернуть «Minor» или «Adult», вы можете заключить формулу в функцию ЕСЛИ следующим образом:
=IF(DATEDIF(D5,TODAY(),"y")<18,"Minor","Adult")
Приведенная выше формула является примером вложенности. Замените 18 на любой подходящий возраст.
Возраст в годах, месяцах и днях
Чтобы вычислить возраст в годах, месяцах и днях, используйте три экземпляра РАЗНДАТ, например:
=DATEDIF(A1,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"
Первый экземпляр DATEDIF возвращает годы, второй экземпляр возвращает месяцы, а третий экземпляр возвращает дни. Это пример конкатенации, и результатом является такая текстовая строка:
19y 6m 9d
Даты начала и окончания заметки остаются одинаковыми для всех трех РАЗНДАТ; изменен только блок.
YEARFRAC с INT
Другой вариант расчета возраста по дате рождения использует функцию YEARFRAC вместе с функцией INT в такой формуле:
=INT(YEARFRAC(D5,TODAY()))
YEARFRAC вычисляет десятичное число, представляющее долю года между двумя датами. Чтобы вычислить долю года как десятичное значение, Excel использует дни между двумя датами. Как и выше, дата рождения предоставляется как start_date из ячейки D5, а сегодняшняя дата предоставляется как end_date, благодаря функции TODAY.
Текущая дата - 24 ноября 2020 года. Результат от YEARFRAC для Майкла Чанга:
19.5290896646133
Затем функция INT округляет это число до целого числа, равного 19.
=INT(19.5290896646133) // returns 19
Эта формула кажется совершенно логичной и в большинстве случаев работает нормально. Однако YEARFRAC может возвращать неправильное число в дни годовщины (дни рождения). Я не совсем уверен, почему это происходит, но это связано с тем, как YEARFRAC использует дни для определения дробных лет, которые контролируются аргументом основы. Например:
=YEARFRAC(DATE(1960,6,30),DATE(1962,6,30),1) // 1.998, should be 2 =YEARFRAC(DATE(1960,3,3),DATE(1964,3,3),1) // 3.998, should be 4
Суть в том, что формула DATEDIF для даты является более безопасным и простым вариантом, когда цель - указать возраст в целых годах.
