Формула в экселе для расчета возраста

Самый простой способ вычисления возраста или трудового стажа реализуется с помощью функции =РАЗНДАТ(). Эта таинственная функция долгое время не была описана в справке Excel. Более того ее нет ни в одном списке формул, поэтом чтобы использовать эту функцию необходимо вводить только вручную в строку формул или непосредственно в саму ячейку. Несмотря на это она доступна еще с 2000-го года версий Excel и прекрасно справляется со своей основной задачей.

Примеры формул с использованием функции РАЗНДАТ в Excel

Благодаря функции РАЗНДАТ легко выполнить сложные расчеты для сравнения дат. Ведь главная сложность заключается в, том что раз в 4 года наступает високосный год и тогда количество дней в году изменяется с 365 на 366.

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

Для расчета трудового стажа примените такую формулу:

Читайте также: Программа для расчета стажа работы в Excel скачать

Функция РАЗНДАТ возвращает количество дней, месяцев или лет прошедших на протяжении периода времени между двумя датами. Для работы с функцией следует заполнить 3 обязательных аргумента:

  1. Начальная дата.
  2. Конечная дата.
  3. Формат даты – в какой единице измерения времени посчитать и отобразить разницу между начальной и конечной датой ("y" – год, "m" – месяц, "d" – день и др.).

Список всех форматов отображения разницы между датами приведен в следующей таблице:

№ п/п Код формата Возвращаемая единица измерения времени
1 "y" Количество полных лет в заданном периоде.
2 "m" Количество полных месяцев между двумя датами.
3 "d" Количество дней.
4 "md" Разница в остатке дней между начальной и конечной датой без учета лет и месяцев.
5 "ym" Количество оставшихся месяцев между датами без учета лет и дней.
6 "yd" Разница в остатке дней между начальной и конечной датой без учета только лет.



Сразу стоит отметить что 3-тий вариант формата особо не понадобиться так как при обычном математическом вычитании от младшей даты старшей мы получаем тот же результат в днях. Например, 2 способа чтобы узнать сколько прошло дней между двумя датами:

Ведь в Excel, как и во всех других программах, входящих в пакет MS Office, используется система 1900 для нумерации дат. И каждая дата — это число дней, прошедших от 01.01.1900г.

Читайте также:  Ndy 02 ad инструкция по применению

Особое внимание требуют 4-й, 5-й, 6-й коды. Данные коды позволяют получить остатки месяцев неполных лет и остатки дней с неполных месяцев. Их принцип действия легче показать на примере чем описать. Обратите внимание как ведет себя функция с этими кодами в ее третьем аргументе.

Для расчета точного срока беременности от даты зачатия до даты рождения ребенка лучше применить такую формулу:

А чтобы рассчитать дату зачатия по родам нам не нужно прибегать к функции РАЗНДАТ:

Пример более сложной формулы для РАЗНДАТ в комбинации с другими функциями для преобразования дробной части числа лет в месяцы и дни:

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

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

Теперь если нам нужно учитывать и неполные года эксплуатации автомобиля нам нужно использовать другую формулу:

Как видно на примере функция РАЗНДАТ не решает любые вычислительные задачи с датами. Иногда следует прибегать к другим узконаправленным функциям.

Вычислить возраст человека непросто, так как результат зависит не только от текущего года, но и дня. Кроме того, необходимо учесть осложнения, возникающие из-за високосных годов. Мы рассмотрим три метода вычисления возраста человека (рис. 1). [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.

Читайте также:  Lords of xulima каменный часовой

Эта функция сохранена в Excel для обеспечения совместимости с Lotus 1-2-3. Функция рассчитывает разницу между двумя датами и выражает результат в месяцах, днях или годах. Синтаксис функции РАЗНДАТ(нач_дата;кон_дата;единица). Нач_дата и кон_дата — это стандартные даты (здесь также может стоять ссылка на ячейку, в которой содержится дата). Нач_дата должна быть меньше или равна кон_дата. Третий аргумент – единица – представляет собой текстовую строку, указывающую единицы времени, в которых будет выражен возвращаемый результат. Основные коды интервалов: m — количество полных месяцев, d — количество дней, у — количество полных лет. Аргумент единица может принимать и несколько других значений (подробнее см. справку).

Можно выразить возраст с точностью до дня: =РАЗНДАТ(B1;B2; " y " )& " лет " &РАЗНДАТ(B1;B2; " ym " )& " месяцев " &РАЗНДАТ(B1;B2; " md " )& " дней " . Правда, в связи с особенностями русского языка, иногда это будет выглядеть коряво.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 126, 127.

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

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

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

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

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

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

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

Читайте также:  Как подготовить iphone к продаже подробная инструкция

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

  • «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 дня», то формула будет выглядеть следующим образом:

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

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

Оставьте ответ

Ваш адрес email не будет опубликован. Обязательные поля помечены *