Самый простой способ вычисления возраста или трудового стажа реализуется с помощью функции =РАЗНДАТ(). Эта таинственная функция долгое время не была описана в справке Excel. Более того ее нет ни в одном списке формул, поэтом чтобы использовать эту функцию необходимо вводить только вручную в строку формул или непосредственно в саму ячейку. Несмотря на это она доступна еще с 2000-го года версий Excel и прекрасно справляется со своей основной задачей.
Примеры формул с использованием функции РАЗНДАТ в Excel
Благодаря функции РАЗНДАТ легко выполнить сложные расчеты для сравнения дат. Ведь главная сложность заключается в, том что раз в 4 года наступает високосный год и тогда количество дней в году изменяется с 365 на 366.
Чтобы вычислить возраст от даты рождения с помощью функции РАЗНДАТ используйте формулу подобную на эту:
Для расчета трудового стажа примените такую формулу:
Читайте также: Программа для расчета стажа работы в Excel скачать
Функция РАЗНДАТ возвращает количество дней, месяцев или лет прошедших на протяжении периода времени между двумя датами. Для работы с функцией следует заполнить 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г.
Особое внимание требуют 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.
Эта функция сохранена в 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 при любом открытии файла.
Особое внимание стоит обратить на самый последний аргумент — способ_измерения , который определяет тип и единицу измерения между начальной и конечными датами. Ниже перечислены все возможные варианты данного аргумента:
- «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.