Для вычисления возраста или стажа мы будет использовать недокументированную функцию Excel — РАЗНДАТ() (в английской версии Excel — DATEDIF ())

Описание этой функции есть только в английской справке, ее невозмжно вызвать стандартным способом, она сохранена в Excel только для совместимости работы со старыми версиями Excel и Lotus 1-2-3

Но несмотря на это, если мы ее пропишем в Excel, то она отлично будет работать во всех версиях Excel без необходимости, что-то включать или устанавливать дополнительно.

Синтаксис функции РАЗНДАТ() с помощью которой мы будем вычислять возраст или стаж человека следующий:

=РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

начальная_дата — это дата отсчета, если мы вычисляем возраст, то это дата рождения, если вычисляем стаж работы, то это дата трудоустройства.

конечная_дата — это дата на которую нам необходимо посчитать  то или иное значение. Если рассматривать стаж и возраст, то как правило, требуется посчитать на сегодняшнее число. То есть какой стаж у сотрудника с начала даты трудоустройства и до сегодняшнего дня. Аналогично и для даты рождения.Текущую дату можно вставлять в ручную, но если открыть файл на следующий день, то сегодняшняя дата превратится во вчерашнюю, поэтому вы можете использовать функцию СЕГОДНЯ(), которая будет автоматически вставлять сегодняшнюю дату при любом обновлении файла. Таким образом у вас стаж работы или возраст будет считаться автоматически в Excel при любом открытии файла.

Особое внимание стоит обратить на самый последний аргумент — способ_измерения, который определяет тип и единицу измерения между начальной и конечными датами. Ниже перечислены все возможные варианты данного аргумента:

• «y» разница дат в полных годах
• «m» разница дат в полных месяцах
• «d» разница дат в полных днях
• «yd» разница дат в днях с начала года без учета лет
• «md» разница дат в днях без учета месяцев и лет
• «ym» разница дат в полных месяцах без учета лет

Таким образом, если нам необходимо посчитать возраст или стаж работы в полных лет, то мы можем использовать аргумент «y», и формула будет выглядеть следующим образом:

=РАЗНДАТ(A2;B2;«y»)

В английской версии Excel =DATEDIF (A2;B2; «y»)

Либо можно удалить второй столбец и прописать формулу =РАЗНДАТ(A2;Сегодня();«y»)

Самое главное, что при этом будет учитываться дата и месяц рождения, например, если сегодня было бы 10.04.2014 (в примере на картинке), то возраст был бы не 31, а 30 лет, так как день рождение в 2014 году еще не наступило.

Если вы хотите указать стаж работы в полном варианте, например «2 г. 3 мес. 4 дня», то формула будет выглядеть следующим образом:

=РАЗНДАТ(A2;B2;«y»)& » г. «&РАЗНДАТ(A2;B2;«ym»)& » мес. «&РАЗНДАТ(A2;B2;«md»)& » дн. «

в английской версии Excel =DATEDIF (A2;B2; «y»)& « г. »&DATEDIF (A2;B2; «ym»)& « мес. »&DATEDIF (A2;B2; «md»)& » дн. «

Тут мы сначала вычисляем количество полных лет «y», затем  количество полных месяцев без учета лет «ym» и последнее это  «md» — разница дат без учета месяцев и лет. После этого все эти числа и текст мы склеиваем с помощью специального символа и получаем нужный нам результат. 

Надеюсь, что статья помогла вам в решении ваших задач в Excel, спасибо, что ставите лайки, вступайте в наши группы в Вконтакте и Facebook.

1. 

   Создайте колонку с заголовком «Имя». Наличие заголовка столбца не обязательно, тем не менее, вам следует знать, что в этом столбце должны содержаться имена людей, чей возраст вы хотите рассчитать по дню рождения.

2. Создайте колонку с заголовком «Дата рождения».

   В этой колонке в индивидуальных строчках должны содержаться дни рождения людей.

   • Не обязательно использовать функцию «РАЗНДАТ» для расчета возраста. С ее помощью можно вычислять любой период времени, например, от даты покупки товара, от даты его отправки и так далее.
3. Введите даты в привычном формате.

   Убедитесь в том, что все введенные даты занесены единообразно. Если вы находитесь в России или СНГ, то формат даты будет выглядеть следующим образом: ДД.ММ.ГГГГ. Если вы находитесь в США, то формат даты будет таким: ММ/ДД/ГГГГ. Программа Excel автоматически определит, что вы ввели даты, и присвоит ячейкам соответствующий формат.

   • Если введенные даты при автоматическом форматировании ячеек не определились как даты, выделите ячейки и в контекстном меню зайдите в «Формат ячеек». В открывшемся окне на вкладке «Число» в списке числовых форматов выберите формат «Дата».
4. 

   Создайте колонку «Возраст». Данная колонка после ввода соответствующей формулы будет отображать возраст для каждой указанной даты.

5. 

   Выделите первую пустую ячейку в колонке «Возраст». Здесь вы введете формулу для расчета периода от дня рождения.

6. Введите в ячейку формулу для определения количества прошедших лет.

   Введите в ячейку нижеуказанную формулу, которая подразумевает, что первый день рождения указан в ячейке B2.

   • =РАЗНДАТ(B2;СЕГОДНЯ();»Y»)
   • Функция =РАЗНДАТ() производит вычисление разницы между двумя датами. Параметры (B2;СЕГОДНЯ();»Y») задают функции «РАЗНДАТ» данные для расчета разницы между датой из ячейки B2 (первой указанной датой в списке) и текущей датой (СЕГОДНЯ()). Параметр («Y») говорит функции о том, что разницу необходимо подсчитать в годах. Если вы предпочитаете вычислить промежуток времени в днях или месяцах, то для этого необходимо использовать уже другой параметр: «D» или «M».
7. 

   Нажмите на квадратик в нижнем правом углу ячейки и потяните его вниз. При этом та же самая формула будет скопирована в нижележащие ячейки с учетом изменения строк для расчета, чтобы в каждом случае рассчитывался правильный период для правильной даты.

8. 

   Исправьте ошибку, если формула не работает. Если в ячейке с формулой отображается #ЗНАЧ! или #ИМЯ?, то, вероятно, где-то в формуле присутствует ошибка. Убедитесь в том, что при наборе формулы вы использовали правильный синтаксис и указали в таблице на правильную ячейку с датой. Обратите внимание, что функция «РАЗНДАТ» не работает для дат до 1 января 1900 года.

9. Изменяйте формулу для расчета промежутка времени в годах, месяцах или днях.

   Если вы хотите получить более детальную информацию о возрасте, то можно рассчитать промежуток времени в годах, месяцах и днях. При этом используется та же самая формула, но с большим количеством аргументов, чтобы можно было определить точный период.

   • =РАЗНДАТ(B2;СЕГОДНЯ();»Y»)&» лет, «&РАЗНДАТ(B2;СЕГОДНЯ();»YM»)&» месяцев, «&РАЗНДАТ(B2;СЕГОДНЯ();»MD»)&» дней»

Вычисление возраста по датам в Excel. Способы решения

Не так давно столкнулся с проблемой вычисления возраста человека по дате его рождения. Как оказалось, для такой распространенной, казалось бы, технической задачи в Excel нет единого решения. Однако, обо все по порядку. В предлагающемся к статье файле электронных таблиц – тренировочный пример, который вы можете скачать и посмотреть все описанные в данной статье способы решения.

Как известно, если результат зависит от выбранного способа решения – это не математика, а бухгалтерская отчетность. В нашем с вами случае именно так и есть, вычисление возраста – это задача с которой чаще всего приходится сталкиваться кадровикам, специалистам по бухгалтерскому учету. Поэтому четко обозначим проблему: необходимо вычислить возраст человека по дате его рождения на сегодняшнее число.

Все варианты решения данной проблемы так или иначе вращаются вокруг одного простого математического действия – необходимо вычислить разницу между сегодняшней датой и датой рождения. В результате мы получаем количество дней от момента рождения до настоящего времени, которую тем или иным способом необходимо преобразовать в количество лет.

Вариант 1

Однако попробуем обойти неудобный вопрос количества дней и сразу возьмёмся определять количество лет. Для начала при помощи функции СЕГОДНЯ(), позволяющей использовать текущую дату в формулах, и ГОД(), которая позволяет извлечь из даты год в виде целого числа, определим текущий год. Затем отнимем от него год рождения, полученный аналогичным способом.  Свяжем эти функции в нехитрую формулу:

=ГОД(СЕГОДНЯ())-ГОД(])

или проще

если формула не используется в рамках форматированной таблицы. Поскольку этот инструмент – очень удобное средство в работе с большими объемами данных, то все формулы в этой статье мы будем рассматривать именно в разрезе форматированных таблиц.

вариант 1

Вроде бы все правильно, однако, при внимательном рассмотрении становятся очевидными недостатки такой системы. Достаточно взглянуть на первые три записи. На момент написания статьи функция СЕГОДНЯ() возвращает дату 04.09.2016, т.е. на данный момент первому человеку из списка действительно 13 лет от роду. Но второй человек празднует день рождения именно сегодня т.е. ему исполнится 13 лет только по окончании астрономического дня, а пока ему только 12 полных лет, а система, между тем, уже показывает, что он стал на год старше. Однако и это не беда.  Гораздо сложнее ситуация с третьим человеком, которому 13 стукнет только завтра (т.е. 15-го сентября).

Иными словами, на примере пограничных значений становится понятным, что данное решение не учитывает день рождения в текущем году.

Вариант 2

Чтобы сделать результат более точным, попробуем усложнить формулу.

Во-первых, необходимо заставить вычислять точную дату рождения для каждого сотрудника в этом году. (например, первый человек в списке будет отмечать свой день рождения 03.10.2016).

Во-вторых, сравнить ее с текущей датой и, если день рождения уже наступил, то считать полное количество лет по формуле из предыдущего варианта, иначе уменьшать результат не единицу.

Поскольку итоговая формула будет достаточно сложной для начального понимания, решим задачу по действиям. Для начала создадим дополнительный столбец, в котором при помощи формулы будет определяться день рождения в текущем (2016) году.

Сделать это можно при помощи формулы:

=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(]);ДЕНЬ(]))

Если формула сразу сложна для восприятия, разберем ее по частям.

Функция СЕГОДНЯ() возвращает текущую дату. Как уже говорилось, на момент написания данной статьи – это 04 сентября 2016 года. При помощи функции ГОД() из текущей даты мы извлекаем год в числовом формате (2016).

Далее функциями МЕСЯЦ() и ДЕНЬ() мы извлекаем число месяца и день из даты рождения.

Финальным аккордом является соединение трех чисел (текущего года, месяца и дня рождения в единую дату при помощи функции ДАТА()).

Если логика данного построения не совсем понятна, советуем записаться на курс по использованию встроенных функций в Excel.

Теперь, когда известна дата рождения в текущем году, можно при помощи условия осуществлять проверку, отмечал ли человек день рождения в этом году или еще нет.

Реализуется это при помощи функции ЕСЛИ(), где результат предыдущей формулы сравниваем с текущей датой:

=ЕСЛИ(СЕГОДНЯ() Вычисление возраста в Excel Вычислить возраст человека непросто, так как результат зависит не только от текущего года, но и дня. Кроме того, необходимо учесть осложнения, возникающие из-за високосных годов. Мы рассмотрим три метода вычисления возраста человека (рис. 1). Рис. 1. Вычисление возраста человека Скачать заметку в формате Word или pdf, примеры в формате Excel Метод 1. Следующая формула вычитает дату рождения из текущей даты и делит результат на 365,25. Функция ЦЕЛОЕ отсекает дробную часть результата: =ЦЕЛОЕ((B2-B1)/365,25). Эта формула не точна на 100%, так как делителем является среднее количество дней в году. Допустим, есть ребенок, которому ровно 1 год. Для него эта формула вернет 0 в обычный год, и 1 – в високосный (рис. 2). Рис. 2. Разность дат, деленная на 365,25 иногда дает осечку Метод 2. Более точный метод подсчета возраста связан с использованием функции ДОЛЯГОДА: =ЦЕЛОЕ(ДОЛЯГОДА(B1;B2)). Как правило, функция ДОЛЯГОДА используется в финансовых расчетах, но подходит и для вычисления возраста. Эта функция рассчитывает долю года, соответствующую целому количеству дней между двумя датами. Функция ЦЕЛОЕ удаляет дробную часть результата и возвращает целое число, соответствующее количеству полных лет. Метод 3. Данный метод вычисления возраста связан с использованием функции РАЗНДАТ: =РАЗНДАТ(В1:В2;"y"). Это недокументированная функция, которой нет в списке функций, и она не появляется в подсказке, если начать набирать формулу =раз… Правда, ее описание есть в справке Excel. Эта функция сохранена в Excel для обеспечения совместимости с Lotus 1-2-3. Функция рассчитывает разницу между двумя датами и выражает результат в месяцах, днях или годах. Синтаксис функции РАЗНДАТ(нач_дата;кон_дата;единица). Нач_дата и кон_дата — это стандартные даты (здесь также может стоять ссылка на ячейку, в которой содержится дата). Нач_дата должна быть меньше или равна кон_дата. Третий аргумент – единица – представляет собой текстовую строку, указывающую единицы времени, в которых будет выражен возвращаемый результат. Основные коды интервалов: m — количество полных месяцев, d — количество дней, у — количество полных лет. Аргумент единица может принимать и несколько других значений (подробнее см. справку). Можно выразить возраст с точностью до дня: =РАЗНДАТ(B1;B2;"y")&" лет "&РАЗНДАТ(B1;B2;"ym")&" месяцев "&РАЗНДАТ(B1;B2;"md")&" дней". Правда, в связи с особенностями русского языка, иногда это будет выглядеть коряво. По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 126, 127.