Как в excel посчитать возраст по дате рождения или стаж по дате трудоустройства

Для вычисления возраста или стажа мы будет использовать недокументированную функцию 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»)

Как вычислить возраст в Excel в полных лет

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

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

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

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

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

Как вычислить стаж работы в Excel

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

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

 

Поделиться:
39 Комментариев
  • Таня:

    вложение img_6008

    Как сделать чтоб общий стаж считался у всех одной формулой? На скрине посчитано формулой что в примере и считает получается только первые ячейки...

    • excel:

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

      В июне 20 дней. То есть за эти два месяца он поработал 37 дней. Вопрос в том сколько это в формате мес. дн.

      37 дней это 1 месяц и 6 дней, или 1 месяц или 7 дней. А может за эталон брать февраль (28 дней) и это 1 месяц и 9 дней.

      • Таня:

        Нужно полное количество лет месяцев и дней...

        • excel:

          Таня, и что? Вы читали мой вопрос?

          Допустим Иванов работал весь 2015 год. и до 28 января 2016 года. Далее уволился.

          Принят на работу 20 февраля и уволился 14 марта.

          Какой у него стаж по вашему?

          1 год+28дней (в январе)+8 дней в феврале и 14 дней в марте.

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

          • Таня:

            вложение img_6023

            А если стаж записан так как на скрине, вообще есть формула чтоб общий у первого человека посчитать?

          • Таня:

            Ну и будет 1 год 1 мес. и 29 дней. Но на 300 чел сложно выслугу лет на каждого отдельно высчитывать. С этим я разобралась спасибо)

  • Сергей:

    Спасибо!!!

  • Павел:

    Интересно, а как посчитать кол-во дней по месяцам за период? К примеру, есть отпуск с 29.07.16 по 02.09.16, а в табличке из трех колонок Июль — 3 дня, Август -31 день и Сентябрь — 2 дня. Всего -35 дней.

  • Нино:

    Спасибо! В Excel 2013-все сработало

  • елена:

    Подскажите, пожалуйста, как сделать обратное действие: из текущей даты вычесть стаж работы и получить дату начала работы?

  • Наталья:

    А у меня неправильно считает. вычисляю возраст работника на определенную дату. Например, дата рождения 03.07.1990 г., нужно посчитать сколько лет ему было 15.09.2015. Сотруднику было уже 25 лет, а формула считает, что 24. В чем может быть причина?

    • excel:

      Как считаете?

      =РАЗНДАТ(A2;B2; «y») — выдает 25 лет

      • Татьяна:

        Вот у меня все получилось но там где нет даты рождения возраст считает 116, как сделать так чтоб где нет даты рождения чтоб пустой оставалась ячейка?.

        • excel:

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

          sirexcel.ru/priemi-excel/...cel-s-primerami/

          • Татьяна:

            А как посчитать стаж работы если он прерывался?..

          • Татьяна:

            Спасибо все получилось)

  • Светлана:

    Мне очень пригодилось и в комментариях нашла дополнительную функцию, спасибо! Единственный вопрос, если очень много данных, например более 1000 сотрудников на предприятии, получается можно применить функцию только растягиванием вниз? А потом делать сводную таблицу, где укажется количество по нужным параметрам? Или это можно через функцию на весь столбец? Мне нужно подсчитать количество сотрудников, работающих со стажем более 5 лет.

    • excel:

      Можно сделать функцией и протянуть на всех сотрудников. Сделайте например столбец, который будет выводить стаж в годах и фильтруйте всех у кого больше 5 лет.

      • Таня:

        вложение img_6008

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

  • Иван:

    Супер!!!

  • Виктор:

    в EXCEL 2003 Ваш синтаксис точно не работает. В инструкции версия EXCEL не укззывается.Значит, функция должна быть универсальной. НЕ РАБОТАЕТ!!!! Оценка — неуд!!! Всем восторженным высказываниям НЕ ВЕРЮ (это искусственные позитивные отзывы) — руководствуюсь только тем, что перед глазами!!!!

    • excel:

      Функция работала 2003 версии в следующих версии ее работоспособность оставили.

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

  • Женя:

    Если стаж более 4 лет, необходимо указывать не «год», а «лет», например 5 лет, 12 лет и т.д. Как исправить что бы не писалось 5 год, 12 год.

    • excel:

      Я бы составил список окончаний, которые заканчиваются на «лет», потом через если проверял заканчивается ли на цифра на эти значения и в зависимости от этого были бы разные формулы текста. Другого решения я не вижу

      • Черный Ангел:

        Спасибо моей знакомой пригодилась

  • Гулзада:

    Добрый день!

    А можно на целый столбец прописать функцию, или нужно всё время на каждую ячейку вводить расчёты

    • excel:

      Добрый вечер! Конечно же ее прописывают один раз и потом протягивают, иначе смысла нет в такой формуле

  • Михаил:

    Чуть не забыл: если в разнице между возрастом 65 лет и реальным возрастом человека будут неполные месяцы, например, 27 лет 2 месяца и 1 день (или 0 дней, то есть в день рождения расчет сделан), количество месяцев должно увеличиваться на 1, то есть, округляться в большую сторону.

  • Михаил:

    Здравствуйте! Это именно то, что мне нужно! Огромное спасибо!!! Только у меня ещё условия есть в этой задачке, может быть, поможете, а то я долго буду голову ломать: мне нужно дополнительно, чтобы в ячейках ниже возраста отображалась информация согласно следующих условий:

    1. При первом условии: = разница между возрастом 65 лет и вычисленным возрастом в формате "... лет, ... мес. ", причем если эта разница более 5 лет, чтобы в ячейке отображалась информация "5 лет, 0 мес. ";

    2. При втором условии: = разница между возрастом 75 лет и вычисленным возрастом в формате "... лет, ... мес. ", причем если эта разница более 5 лет, чтобы в ячейке отображалась информация "5 лет, 0 мес. "

    Как-то так))

    А можно вообще условия отдельно прописать и выбирать их из выпадающего списка? Я такое вроде бы видел!

  • Оксана:

    Большое спасибо!

  • Оксана:

    Добрый день! Очень понравилась Ваша статья о датах, сразу применила в своей работе. Не хватает малости, хочу сравнивать возраст ребенка (который я получила по вашей формуле)с определенной датой.

    В общем, мне необходимо узнать на определенную дату (01.10.14) сколько в списке детей в возрасте до 6 лет 6 месяцев, имеется дата рождения, возраст детей и недостаток знаний в работе с excel. Если сможете мне подсказать буду Вам очень признательна.

    • excel:

      Добрый день"

      В соседнем столбце можно сделать проверку на кол-во месяцев, если больше 78 (6 лет и 6 месяцев), то в ячейке показываем 1, иначе — 0. Потом можно отфильтровать по цифре 1 или 0

      =ЕСЛИ(РАЗНДАТ(дата_рождения; 01.10.14; «m»)>78;1;0)

  • Людмила:

    Отличная функция! Подскажите, как к общему стажу прибавить стаж работы в другой организации(пример: 23г.06мес.03дн.+3г.03мес.03дн.=(какая нужна формула, чтобы все правильно посчиталось?)

    • excel:

      Можно перед сцеплением текста просуммировать отдельно года, месяца и дни (РАЗНДАТ(A2;B2; «y»)+РАЗНДАТ(A4;B4; «y»)), но в данном случае может получиться 23 г. 15мес. 53 дня. Если месяца в года преобразовать не будет проблемой, то с днями сложнее. Нужно выделить целый месяц, но тогда нужно приминать кол-во дней за месяц 30 или 30.5 или 31

      Поэтому легче просто посчитать кол-во дней в обоих организациях, просуммировать их. Допустим вы получили 1032 дн.

      Принимает, что месяц это 30.5 дней. Чтобы представить в нужно формате используем следующее:

      Если суммарное количество дней стажа в А1, то:

      A2=ЦЕЛОЕ(A1/365) — количество лет

      A3=ЦЕЛОЕ(ОСТАТ(A1;365)/30,5) — количество месяцев

      A4=A1-A2*365-A3*30,5 — количество дней

      Потом все это сцепляете.

  • Андрей:

    Очень пригодилась, спасибо большое!

  • Дмитрий:

    Большое спасибо!

  • Kwant:

    Интересная функция и действительно в справке по ней нет никакой инфы)

    • excel:

      Да, замечательная функция.

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.