В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет, в годах, месяцах и днях на текущий момент или на какой-то определённый момент времени. Мы объясним преимущества и недостатки каждого способа, покажем, как создать идеальную формулу расчета возраста в Экселе и настроить ее для решения некоторых конкретных задач.
- Основная формула для расчета возраста
- Как определить возраст с функцией ДОЛЯГОДА.
- Как узнать возраст при помощи функции РАЗНДАТ
- Возраст в днях, месяцах и годах
- Сколько будет лет на определенную дату?
- Сколько вам будет в определенный год?
- Узнаём дату, когда человек достигает N лет
- Калькулятор возраста для Excel.
В повседневной жизни вопрос «Сколько вам лет?» обычно подразумевает ответ, указывающий, сколько лет вы прожили. В Microsoft Excel вы можете создать формулу для исчисления точной продолжительности жизни в месяцах, днях, часах и даже минутах. Но давайте не будем нарушать традицию и научимся сначала считать по полным годам.
Основная формула Excel расчета для возраста в годах.
Как вы обычно определяете, сколько вам лет? Просто вычитая дату рождения из текущей. Этот традиционный подход, знакомый нам еще с детства, может также использоваться в Excel.
Если предположить, что день рождения (ДР) записан в ячейке B2, то вычислить возраст в годах можно следующим образом:
=(СЕГОДНЯ()-B2)/365
=(TODAY()-B2)/365
Первая часть выражения (СЕГОДНЯ()-B2) возвращает разницу между текущей и датой рождения в днях. А затем вы делите это на 365, чтобы получить количество лет.
Формула очевидна и легко запоминаема, однако есть небольшая проблема. В большинстве случаев мы получаем число, как показано на скриншоте ниже. Что, согласитесь, несколько непривычно и не слишком удобно.
Чтобы отобразить количество прожитых лет, используйте один из предлагаемых методов округления десятичного числа до ближайшего целого:
=ЦЕЛОЕ((СЕГОДНЯ()-B2)/365)
Функция ЦЕЛОЕ округляет в меньшую сторону до ближайшего целого.
=ОКРУГЛ((СЕГОДНЯ()-B3)/365;0)
Округляет количество прожитых лет по стандартным правилам в зависимости от значения дробной части (более 0,5 — в большую сторону).
=ОКРУГЛВНИЗ((СЕГОДНЯ()-B4)/365;0)
Округляет в меньшую сторону.
=ОТБР((СЕГОДНЯ()-B5)/365;0)
Отбрасывает дробную часть, оставляя только целое.
Во всех случаях ноль в формуле означает, что в возвращаемой цифре не должно быть ни одного десятичного разряда.
Недостатки: использование этого выражения в Экселе дает довольно точные результаты, но не безупречные. Деление на среднее число дней в году в большинстве случаев работает нормально, но иногда все же показывает неверный возраст. Например, если кто-то родился 29 февраля, а сейчас 28 февраля, программа сделает человека на один день старше.
В качестве альтернативы вы можете использовать в знаменателе 365,25 вместо 365, поскольку каждый четвертый год состоит из 366 дней. Однако и этот подход не идеален. Например, если вы рассчитываете возраст ребенка, который еще не прожил високосный год, деление на 365,25 дает неправильный результат.
В целом, вычитание ДР из текущей даты прекрасно работает в обычной жизни, но не является идеальным подходом в Excel. Ведь мы здесь ожидаем получить точный ответ.
Далее мы поговорим о нескольких специальных функциях, которые безошибочно вычисляют возраст независимо от года.
Работа на калькуляторе лет с помощью клавиатуры компьютера.
Работать с калькулятором можно не только с помощью мышки, но и с помощью клавиатуры вашего компьютера. Далее приведена таблица соответствия клавиш компьютера и кнопок калькулятора.
| Клавиши компьютера | Действия на калькуляторе |
| Цифры 1 до 0 | Числа дат от 1 до 0 |
| b | до н.э. |
| alt+t | Сегодняшняя дата |
| alt+y | Вчерашняя дата |
| alt+m | Дата первого числа текущего месяца |
| alt+s | Дата первого дня текущего года |
| + | Действие сложения |
| — | Действие вычитания |
| i | Действие промежутка |
| shift+1, shift+… и т.д. | Числа величин (кнопки правой части калькулятора) |
| shift+d | Величина «Дней» |
| shift+m | Величина «Месяцев» |
| shift+y | Величина «Лет» |
| shift+c | Величина «Веков» |
| c или del | Сброс (Cancel) |
| Backspace | Удалить один символ с конца |
| = или Enter | Кнопка «Равно» |
Как узнать возраст в Excel при помощи РАЗНДАТ.
Еще один способ — использование функции РАЗНДАТ (DATEDIF):
= РАЗНДАТ(дата_нач; дата_кон; аргумент)
где
дата_нач и дата_кон — две даты, разницу между которыми необходимо рассчитать,
Она может возвращать разницу в различных единицах времени, таких как годы, месяцы и дни, в зависимости от параметра, который вы задаете в аргументе:
- Y — возвращает количество лет.
- M — количество месяцев.
- D — количество дней.
- YM — возвращает месяцы, игнорируя дни и годы.
- MD — разница в днях, игнорируя месяцы и годы.
- YD — разница в днях, игнорируя годы.
Поскольку мы хотим подсчитать возраст в годах, мы используем параметр «Y»:
=РАЗНДАТ( дата рождения, СЕГОДНЯ(), «Y»)
В этом примере день рождения записан в ячейке B2, и вы ссылаетесь на него в своей формуле возраста:
=РАЗНДАТ(B2;СЕГОДНЯ();»Y»)
В этом случае дополнительная операция округления не требуется, так как РАЗНДАТ с аргументом «Y» сразу выводит количество лет.
Возрастной период
| Название этапа | Социальная ситуация развития | Возраст |
| Новорожденность | Полная зависимость от матери | 1-2 месяца |
| Младенческий возраст | Неразрывная связь ребенка со взрослым. Ситуация “МЫ” | от 2-х месяцев до 1 года |
| Раннее детство | Сотрудничество со взрослым Ситуация “РЕБЕНОК — ПРЕДМЕТ — ВЗРОСЛЫЙ”. Познание мира окруж. Вещей. | 1-3 года |
| Дошкольный возраст | Отделение ребенка от взрослого. Две системы социальных отношений: “ребенок — взрослый” и “ребенок — дети”. Имитация мира человеческих отношений | 3 – 7 лет |
| Младший школьный возраст | Переход от свободного от постоянных обязанностей существования к обязательной, общественно-значимой и общественно-оцениваемой деятельности. Социальный статус школьника. | 7 – 11 лет |
| Подростковый возраст | Стремление приобщиться к миру взрослых, ориентация поведения на нормы и ценности этого мира. Эмансипация от взрослых и группирование | 11 – 15 лет |
| Старший школьный возраст или ранняя юность | Первоначальный выбор жизненного пути | 15 – 17 лет |
| Юнность | Выбор жизненного пути. Уход из родительского дома | от 17 до 20-23 лет |
| Молодость | Выбор спутника жизни и создание семьи. Овладение выбранной профессией | от 20-23 до 30 лет |
| Зрелость | Реализации себя, полного раскрытия своего потенциала в профессиональной деятельности и семейных отношений. Осознание ответственности за содержание своей жизни перед самим собой и перед другими людьми. | от 30 до 60-70 лет |
| Поздняя зрелость или старость | Смена общественной позиции, с выход на пенсию и удаление от активного участия в производительном труде. Отношения с родственниками. Стремление воспитывать, подготовить себе «достойную замену» на работе | после 60-70 лет |
Рассчитываем в днях, месяцах и годах.
Высчитывать можно не только количество лет, как это обычно принято, но и более мелкие единицы времени.
Давайте попробуем рассчитать полный возраст, который включает в себя дни, месяцы и годы.
Фактически, определение чьего-либо возраста на основе даты рождения является частным случаем расчета разницы в датах в Excel, где конечной датой является текущий момент времени. Итак, вы используете обычную функцию РАЗНДАТ:
=РАЗНДАТ(B2;СЕГОДНЯ();»Y»)&» лет, «&РАЗНДАТ(B2;СЕГОДНЯ();»YM»)&» мес., «&РАЗНДАТ(B2;СЕГОДНЯ();»MD»)&» д.»
Где В2 — дата рождения.
Ну а если нужно сделать совсем красиво, используйте более сложное выражение:
=ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();»y»);РАЗНДАТ(B2;СЕГОДНЯ();»y»)&» «&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();»y»)-11;100);9);10);»[<1]\го\д;[<4]\го\да;лет»)&» «;)& ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();»ym»);РАЗНДАТ(B2;СЕГОДНЯ();»ym»)&» меся»&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();»ym»)-1; 11);»[<1]ц;[<4]ца;цев»)&» «;)& ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();»md»);РАЗНДАТ(B2;СЕГОДНЯ();»md»)&» д»&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();»md»)-11;100);9); 10);»[<1]ень;[<4]ня;ней»);)
И вот что получится:
Более подробно об использовании функции РАЗНДАТ читайте в материале о расчете разности между датами в Excel.
Инструкция по использованию калькулятора лет.
Пользоваться калькулятором очень просто. Интерфейс разделен на три условных области. В левой части калькулятора находятся кнопки для ввода дат в формате ДД.ММ.ГГГГ (например, 01.01.2016). В центре находятся действия, которые можно производить с датами: сложение (прибавить к дате временную величину), вычитание (вычесть из даты временную величину) и промежуток (узнать разницу между двумя датами). А в правой части вводятся временные величины, то есть количество дней, месяцев, лет и веков, которые необходимо прибавить или вычесть из той или иной даты.
Калькулятор принимает и обрабатывает только простые арифметические выражения, состоящие из двух чисел (дат или временных величин) и одного действия. Операции сложения и вычитания могут производиться только между датой и величиной. Операция нахождения промежутка может производиться только между двумя датами.
В случае, если производится заведомо ошибочный ввод данных, то в верхней части калькулятора (над экраном) будет выведена соответствующая подсказка, а в области набора будут подсвечены те кнопки, которые допустимы для ввода в данном месте выражения. Это очень просто и удобно. Допустим, вы ввели дату и сразу же пробуете ввести величину из правой части. В данной ситуации калькулятор напишет наверху «Пожалуйста, выберите действие» и подсветит средний столбец кнопок. Ровно по той же самой логике выводятся все остальные подсказки. Поначалу это может показаться непривычным, но подобные подсказки для усвоения и соблюдения принципа работы калькулятора лет. Как показывает практика, пользователи осваивают его очень быстро, а сообщения об ошибках находят весьма полезными. Далее будут рассмотрены примеры действий на калькуляторе лет.
Сколько вам будет на определённую дату?
Вы можете воспользоваться формулой РАЗНДАТ из предыдущего раздела, но вместо СЕГОДНЯ() используйте ссылку на ячейку, в которой и будет указано, на какой именно момент времени вы хотите определить возраст человека.
Кстати, аналогичным образом можно определять и величину трудового стажа.
Итак, вот как это может выглядеть:
Вот расчет:
=РАЗНДАТ(B2;C3;»Y»)&» лет, «&РАЗНДАТ(B2;C3;»YM»)&» мес., «&РАЗНДАТ(B2;C3;»MD»)&» д.»
или же —
=ЕСЛИ(РАЗНДАТ(B2;C3;»y»);РАЗНДАТ(B2;C3;»y»)&» «&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;C3;»y»)-11;100);9);10);»[<1]\го\д;[<4]\го\да;лет»)&» «;)& ЕСЛИ(РАЗНДАТ(B2;C3;»ym»);РАЗНДАТ(B2;C3;»ym»)&» меся»&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;C3;»ym»)-1; 11);»[<1]ц;[<4]ца;цев»)&» «;)& ЕСЛИ(РАЗНДАТ(B2;C3;»md»);РАЗНДАТ(B2;C3;»md»)&» д»&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;C3;»md»)-11;100);9); 10);»[<1]ень;[<4]ня;ней»);)
Сколько вам будет лет в определенный год?
Эта формула полезна в ситуациях, когда полная дата для расчета не определена, и вы знаете только год.
Допустим, вы работаете с медицинской базой данных, и ваша цель – выяснить, сколько лет пациенту на момент последнего полного медицинского обследования.
Если предположить, что ДР указан в столбце B, начиная со строки 2, а год последнего медицинского осмотра находится в столбце C, то расчет выглядит следующим образом:
=РАЗНДАТ(B2;ДАТА(C2;1;1);»Y»)
Поскольку точный срок медицинского осмотра не определен, вы используете функцию ДАТА с произвольным аргументом дня и месяца, например, DATА(C3; 1; 1).
Функция ДАТА извлекает номер года из ячейки С2, создает полную дату, используя введенные вами номера месяца и дня (в этом примере 1 января), и передает ее в РАЗНДАТ. В результате вы получаете сумму прожитых пациентом лет по состоянию на 1 января определенного года.
Калькулятор лет для математических действий с датами онлайн.
Калькулятор лет поможет вам произвести математические расчеты, связанные с датами и временными величинами. В частности, калькулятор позволяет выполнять следующие вычисления:
- Определить количество дней между датами. А в случае с большими промежутками определяется также количество месяцев, лет и веков. Функция часто может пригодится в рамках исторических вычислений, а также в деловом документообороте.
- Прибавить дни к определенной дате. Например, можно определить дату окончания отпуска и т.п. Прибавлять можно не только дни, но и месяцы, годы и даже века.
- Вычесть из даты определенное количество дней. Данная функция будет очень полезна для различных ретроспективных вычислений.
Узнайте дату, когда человек достигает N лет
Предположим, ваш друг родился 21 марта 1974 года. Как вам определить, когда ему исполняется 50 лет? Обычно вы просто добавляете 50 лет к ДР человека. В Excel вы делаете то же самое, используя ДАТА:
=ДАТА(ГОД(B2) + 50; МЕСЯЦ(B2); ДЕНЬ(B2))
Где B2 — день рождения.
Вместо того, чтобы жестко указывать количество лет в формуле, вы можете сослаться на определенную ячейку, в которую ваши пользователи могут вводить сколько угодно лет.
