Для вычисления возраста или стажа мы будет использовать недокументированную функцию 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.
Интересная функция и действительно в справке по ней нет никакой инфы)
Да, замечательная функция.
Большое спасибо!
Очень пригодилась, спасибо большое!
Отличная функция! Подскажите, как к общему стажу прибавить стаж работы в другой организации(пример: 23г.06мес.03дн.+3г.03мес.03дн.=(какая нужна формула, чтобы все правильно посчиталось?)
Можно перед сцеплением текста просуммировать отдельно года, месяца и дни (РАЗНДАТ(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 — количество дней
Потом все это сцепляете.
Добрый день! Очень понравилась Ваша статья о датах, сразу применила в своей работе. Не хватает малости, хочу сравнивать возраст ребенка (который я получила по вашей формуле)с определенной датой.
В общем, мне необходимо узнать на определенную дату (01.10.14) сколько в списке детей в возрасте до 6 лет 6 месяцев, имеется дата рождения, возраст детей и недостаток знаний в работе с excel. Если сможете мне подсказать буду Вам очень признательна.
Добрый день»
В соседнем столбце можно сделать проверку на кол-во месяцев, если больше 78 (6 лет и 6 месяцев), то в ячейке показываем 1, иначе — 0. Потом можно отфильтровать по цифре 1 или 0
=ЕСЛИ(РАЗНДАТ(дата_рождения; 01.10.14; «m»)>78;1;0)
Большое спасибо!
Здравствуйте! Это именно то, что мне нужно! Огромное спасибо!!! Только у меня ещё условия есть в этой задачке, может быть, поможете, а то я долго буду голову ломать: мне нужно дополнительно, чтобы в ячейках ниже возраста отображалась информация согласно следующих условий:
1. При первом условии: = разница между возрастом 65 лет и вычисленным возрастом в формате «… лет, … мес.», причем если эта разница более 5 лет, чтобы в ячейке отображалась информация «5 лет, 0 мес.»;
2. При втором условии: = разница между возрастом 75 лет и вычисленным возрастом в формате «… лет, … мес.», причем если эта разница более 5 лет, чтобы в ячейке отображалась информация «5 лет, 0 мес.»
Как-то так ))
А можно вообще условия отдельно прописать и выбирать их из выпадающего списка? Я такое вроде бы видел!
Чуть не забыл: если в разнице между возрастом 65 лет и реальным возрастом человека будут неполные месяцы, например, 27 лет 2 месяца и 1 день (или 0 дней, то есть в день рождения расчет сделан), количество месяцев должно увеличиваться на 1, то есть, округляться в большую сторону.
Добрый день!
А можно на целый столбец прописать функцию, или нужно всё время на каждую ячейку вводить расчёты
Добрый вечер! Конечно же ее прописывают один раз и потом протягивают, иначе смысла нет в такой формуле
Если стаж более 4 лет, необходимо указывать не «год», а «лет», например 5 лет, 12 лет и т.д. Как исправить что бы не писалось 5 год, 12 год.
Я бы составил список окончаний, которые заканчиваются на «лет», потом через если проверял заканчивается ли на цифра на эти значения и в зависимости от этого были бы разные формулы текста. Другого решения я не вижу
Спасибо моей знакомой пригодилась
Пожалуйста!
в EXCEL 2003 Ваш синтаксис точно не работает. В инструкции версия EXCEL не укззывается.Значит, функция должна быть универсальной. НЕ РАБОТАЕТ!!!! Оценка — неуд!!! Всем восторженным высказываниям НЕ ВЕРЮ (это искусственные позитивные отзывы) — руководствуюсь только тем, что перед глазами!!!!
Функция работала 2003 версии в следующих версии ее работоспособность оставили.
Возможно вы как-то неправильно ее вводите. Опишите подробнее что происходит при вводе данной функции
Супер!!!
Мне очень пригодилось и в комментариях нашла дополнительную функцию, спасибо! Единственный вопрос,если очень много данных,например более 1000 сотрудников на предприятии,получается можно применить функцию только растягиванием вниз? А потом делать сводную таблицу,где укажется количество по нужным параметрам? Или это можно через функцию на весь столбец? Мне нужно подсчитать количество сотрудников,работающих со стажем более 5 лет.
Можно сделать функцией и протянуть на всех сотрудников. Сделайте например столбец, который будет выводить стаж в годах и фильтруйте всех у кого больше 5 лет.
Ну выделит в годах, а если прерывался стаж, и у нескольких работников 2 периода, у некоторых три как формулу записать чтоб общий стаж посчитать? Чтоб не только первые две ячейки считало..
А у меня неправильно считает. вычисляю возраст работника на определенную дату. Например, дата рождения 03.07.1990 г., нужно посчитать сколько лет ему было 15.09.2015. Сотруднику было уже 25 лет, а формула считает, что 24. В чем может быть причина?
Как считаете?
=РАЗНДАТ(A2;B2;»y») — выдает 25 лет
Вот у меня все получилось но там где нет даты рождения возраст считает 116, как сделать так чтоб где нет даты рождения чтоб пустой оставалась ячейка?.
сделать проверку. Если в поле дата пусто, то возвращать пусто, иначе делать расчет который у вас указан сейчас. Это делается с помощью функции если
https://sirexcel.ru/priemi-excel/funkciya-esli-v-excel-s-primerami/
Спасибо все получилось)
А как посчитать стаж работы если он прерывался?…
Подскажите, пожалуйста, как сделать обратное действие: из текущей даты вычесть стаж работы и получить дату начала работы?
Спасибо! В Excel 2013-все сработало
Интересно, а как посчитать кол-во дней по месяцам за период? К примеру, есть отпуск с 29.07.16 по 02.09.16, а в табличке из трех колонок Июль — 3 дня, Август -31 день и Сентябрь — 2 дня. Всего -35 дней.
Спасибо!!!
Как сделать чтоб общий стаж считался у всех одной формулой? На скрине посчитано формулой что в примере и считает получается только первые ячейки..
Просто тут вопрос как учитывать. Если считать стаж по дням это одно дело. Если в формате как у вас, то например если сотрудник в феврале проработал 17 дней.
В июне 20 дней. То есть за эти два месяца он поработал 37 дней. Вопрос в том сколько это в формате мес. дн.
37 дней это 1 месяц и 6 дней, или 1 месяц или 7 дней. А может за эталон брать февраль (28 дней) и это 1 месяц и 9 дней.
Нужно полное количество лет месяцев и дней….
Таня, и что? Вы читали мой вопрос?
Допустим Иванов работал весь 2015 год. и до 28 января 2016 года. Далее уволился.
Принят на работу 20 февраля и уволился 14 марта.
Какой у него стаж по вашему?
1 год+28дней (в январе)+8 дней в феврале и 14 дней в марте.
Посчитайте в ручную и скажите какой у него стаж. Объясните почему и как вы считали, после этого можно будет думать, как заставить считать Excel
Ну и будет 1 год 1 мес. и 29 дней. Но на 300 чел сложно выслугу лет на каждого отдельно высчитывать. С этим я разобралась спасибо)
А если стаж записан так как на скрине, вообще есть формула чтоб общий у первого человека посчитать?
Добрый вечер, как можно подсчитать сколько раз сотрудник отметил свой день рождения со дня приема на работу?
В группе на стене есть ответ https://vk.com/sirexcel?w=wall-57633334_259
Узнайте, когда Вам исполнится (исполнилось) 1000 недель
•Узнайте день рождения студента, которому сегодня исполнилось ровно 1000 недель
Подскажите мне почему я ввёл формулу но она активируется только после того, как войдешь в формулу и нажмешь энтер, но если меняется дата, то снова надо войти в формулу и снова нажать энтер?
Посмотрите Меню — Формулы — Параметры вычислений. У вас должно стоять вычисления в книге «Автоматически».
Создайте колонку «Возраст». Данная колонка после ввода соответствующей формулы будет отображать возраст для каждой указанной даты.
добрый день!
подскажите, пожалуйста, как сделать, чтобы отображался возраст не на сегодняшний день, а на текущий год?
то есть даже если д.р. сотрудника в декабре, чтобы уже в июне эксель прописывал, сколько лет сотруднику будет в этом году.
и второе: как автоматически подкрашивать ячейки, содержащие возраст, кратный пяти?
Здравствуйте, подскажите пожалуйста, забита формула для подсчета стажа =РАЗНДАТ(G5;СЕГОДНЯ();»y»)&»г.»&РАЗНДАТ(G5;СЕГОДНЯ();»ym»)&»мес.»&РАЗНДАТ(G5;СЕГОДНЯ();»md»)&»дн.», до 31.12.2019 г. было все нормально, нос 09.01.2020 стаж стал считаться таким образом 5г.8мес.127 дн. Что можно сделать
есть калькуляторы для
расчет лет