Как быстро преобразовать дату рождения в возраст в Excel?
Как посчитать возраст или стаж в Excel?
Спасибо, что читаете TutorExcel, дорогие читатели. Блог Ru.
Для расчета интервала между двумя датами воспользуемся функцией РАЗНДАТ.
Данную формулу нельзя найти через мастер функций, однако если ввести ее непосредственно в ячейку, то она будет прекрасно работать.
РАЗДАТ(нач_дата; кон_дата; единица)
Вычисляет количество дней, месяцев или лет между двумя датами.
- Начальная дата(обязательный аргумент) — дата начала периода;
- Конечная дата(обязательный аргумент) — дата окончания периода;
- Единица(обязательный аргумент) — тип возвращаемого значения:
- «Y» — разница в полных годах;
- «M» — разница в полных месяцах;
- «D» — разница в полных днях;
- «MD» — разница в днях без учета месяцев и годов;
- «YM» — разница в месяцах без учета дней и годов;
- «YD» — разница в днях без учета годов.
Как посчитать стаж в Excel?
Допустим, у нас есть таблица с датами начала и окончания работы сотрудников в компании (если сотрудник все еще работает в компании, то в поле даты окончания нужно ввести формулу TODAY)
Если мы хотим рассчитать стаж в Excel в полном формате, например «1 г. 2 мес. 3 дн.«, то запишем формулу стажа работы в следующем виде:
В результате расчета стажа мы получаем:
Как посчитать стаж с перерывами в Excel?
Рассмотрим следующий сценарий: в стаже работника случаются перерывы, то есть несколько периодов.
Если сотрудник отработал два периода, сначала рассчитайте стаж за каждый период:
Для расчета общего стажа за 2 периода используем простую математику, т.е. к концу первого периода (дата увольнения) прибавляем стаж второго периода (разница между увольнением и получением дохода).
Итак, у нас появился новый период, который формально состоит из суммы двух, и для этого мы используем знакомую формулу
Если вы хотите рассчитать рабочее время за 3 или более периодов, эту формулу можно изменить точно так же.
Как посчитать возраст в Excel?
Допустим, у нас есть таблица дат рождения сотрудников компании:
Для подсчета возраста воспользуемся аналогичным алгоритмом, что и при расчете стажа.
Единственным отличием будет использование сегодняшней даты вместо даты окончания работы:Как в excel посчитать возраст по дате рождения или стаж по дате трудоустройства
Несмотря на это, написание его в Excel гарантирует, что он будет работать во всех версиях Excel без необходимости включать или устанавливать что-либо дополнительно.
Синтаксис функции RAINDATE(), которую мы будем использовать для расчета возраста или стажа человека, выглядит следующим образом:
= SENDAT( начальная_дата ; конечная_дата ; метод_измерений )
начальная_дата — это дата отсчета, если мы вычисляем возраст, то это дата рождения, если вычисляем стаж работы, то это дата трудоустройства.
конечная_дата — это дата на которую нам необходимо посчитать то или иное значение. Если рассматривать стаж и возраст, то как правило, требуется посчитать на сегодняшнее число. То есть какой стаж у сотрудника с начала даты трудоустройства и до сегодняшнего дня. Аналогично и для даты рождения.Текущую дату можно вставлять в ручную, но если открыть файл на следующий день, то сегодняшняя дата превратится во вчерашнюю, поэтому вы можете использовать функцию СЕГОДНЯ() , которая будет автоматически вставлять сегодняшнюю дату при любом обновлении файла. Таким образом у вас стаж работы или возраст будет считаться автоматически в Excel при любом открытии файла.
Особое внимание стоит обратить на самый последний аргумент — способ_измерения , который определяет тип и единицу измерения между начальной и конечными датами. Ниже перечислены все возможные варианты данного аргумента:
- «y» разница дат в полных годах
- «m» разница дат в полных месяцах
- «d» разница дат в полных днях
- «yd» разница дат в днях с начала года без учета лет
- «md» разница дат в днях без учета месяцев и лет
- «ym» разница дат в полных месяцах без учета лет
Таким образом, если нам необходимо посчитать возраст или стаж работы в полных лет, то мы можем использовать аргумент «y» , и формула будет выглядеть следующим образом:
В английской версии Excel =DATEDIF(A2;B2;»y»)
Либо можно удалить второй столбец и прописать формулу =РАЗНДАТ( A2 ; Сегодня() ; «y» )
Кроме того, он учитывает месяц и дату рождения, поэтому, например, если бы сегодня было 10 апреля 2014 года (в примере на картинке), то возраст был бы не 31, а 30 лет, так как 2014 года еще не было.
Если вы хотите указать стаж работы в полном варианте, например «2 г. 3 мес. 4 дня», то формула будет выглядеть следующим образом:
Тут мы сначала вычисляем количество полных лет « y» , затем количество полных месяцев без учета лет «ym» и последнее это «md» — разница дат без учета месяцев и лет. После этого все эти числа и текст мы склеиваем с помощью специального символа и получаем нужный нам результат.
Надеюсь, эта статья помогла вам решить проблемы с Excel, и спасибо, что поставили лайк и присоединились к нашим группам в Facebook и Вконтакте.
Как вычислить дату в Excel
Дата и время в Excel — это числа со специальным форматом. Дата — это целая часть числа, а время (часы и минуты) — дробная часть.
Следовательно, число 1 будет соответствовать 01 января 1900 года. То есть дата рассчитывается как количество дней, прошедших с 1 января 1900 года. На следующем уроке мы рассмотрим тему времени, а на следующем уроке подробно остановимся на датах.
Как в Excel посчитать дни между датами?
Поскольку дата является числом, с ней можно выполнять математические расчеты и вычислительные операции. Количество дней между двумя датами в Excel вычислить несложно. Давайте сложим и вычтем даты для наглядного примера. Для этого:
- Напишите текущую дату в ячейке A1 на чистом листе бумаги, нажав CTRL+;.
- В ячейку A2 введите промежуточный период в днях, например, 127.
- В ячейку A3 введите формулу: =A1+A2.
Обратите внимание, что ячейке A3 был автоматически присвоен формат «Дата». Нетрудно догадаться, что для вычисления разницы дат в Excel нужно вычесть самую раннюю дату из самой поздней. В ячейку B1 введите формулу: =A3-A1. Это даст вам количество дней между двумя датами.
Вычисление возраста по дате рождения в Excel
В следующем уроке мы научим вас вычислять возраст человека на основе его даты рождения:
- В новом листе в ячейках A1:A3 поставьте даты: 18.04.1985; 17.08.1977; 08.12.1992
- В ячейках B1:B3 поставьте текущую дату.
- Теперь используйте функцию для преобразования количества дней в годы. Для этого вручную введите следующее значение в диапазон C1:C3: =RANGE(A1;B1; «y»).
Таким образом, использование функции позволило нам точно рассчитать в Excel возраст по дате рождения.
Внимание. Для перевода дней в годы нельзя использовать формулу: =(B1-A1)/365. Более того, даже если мы знаем, что 1 день равен 0,0027397260273973 года, формула: (B1-A1)*0.0027397260273973 не даст нам точного результата.
Более точным способом преобразования дней в годы является функция:=DATEDIF(). Английская версия — =DATEDIF(). Вы не найдете ее в списке мастеров функций (SHIFT+F3). Но если вы просто введете ее в строку формул, она будет работать.
Функция RANGE (Диапазон) поддерживает несколько параметров:
Параметр Описание «d» Количество полных дней «m» Количество полных месяцев «y» Количество полных лет «ym» Количество полных месяцев без учета лет «md» Количество дней без учета месяцев и лет «yd» Количество дней без учета лет Рассмотрим пример использования нескольких параметров:
Предупреждение. Убедитесь, что начальная дата старше конечной даты, чтобы функция:=RESPENDAT() работала правильно.
Ввод даты в ячейку Excel
Цель этого урока — привести пример математических операций с датами. Кроме того, мы убедимся, что для Excel типом данных даты является число.
Заполните таблицу датами, как показано на рисунке:
Ввод дат несколькими способами. Способ ввода указывается в колонке A, а результат — в колонке B.
Обратите внимание, что в формате по умолчанию ячейки «Общие» даты и числа выравниваются по правому краю, а текст — по левому. Значение в ячейке B4 распознается программой как текст.
Excel сам приписывает ячейке B7 текущий год (сейчас 2015). Это можно увидеть в строке формул, отобразив содержимое ячеек. Обратите внимание, как первоначально было введено значение в ячейке A7.
Вычисление даты Excel
На пустом листе в ячейках A1:B1 введите 01.01.1900 и в ячейках A2:B2 введите 01.01.1901. Теперь измените формат ячеек на «числовой» в выделенных B1:B2. Это можно сделать, нажав CTRL+SHIFT+1.
Теперь B1 и B2 оба содержат число 1. В високосном году было 366 дней и 1 день.
В диалоговом окне Формат ячеек можно указать способ отображения даты. Чтобы открыть его, нажмите CTRL+1. В списке «Числовые форматы» на вкладке «Число» выберите «Дата». В разделе «Тип» показаны наиболее популярные форматы отображения дат.
Следующий урок будет посвящен использованию готовых примеров для обсуждения времени и периодов дня.