Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Для расчета интервала между двумя датами воспользуемся функцией РАЗНДАТ.
Данную формулу нельзя найти через мастер функций, однако если ввести ее непосредственно в ячейку, то она будет прекрасно работать.
РАЗДАТ(нач_дата; кон_дата; единица)
Вычисляет количество дней, месяцев или лет между двумя датами.
- Начальная дата(обязательный аргумент) — дата начала периода;
- Конечная дата(обязательный аргумент) — дата окончания периода;
- Единица(обязательный аргумент) — тип возвращаемого значения:
- «Y» — разница в полных годах;
- «M» — разница в полных месяцах;
- «D» — разница в полных днях;
- «MD» — разница в днях без учета месяцев и годов;
- «YM» — разница в месяцах без учета дней и годов;
- «YD» — разница в днях без учета годов.
Как посчитать стаж в Excel?
Предположим, что у нас есть таблица с датами начала и окончания работы сотрудников в компании (если сотрудник еще работает в компании, то в поле с датой окончания работы вписываем формулу СЕГОДНЯ):
Как посчитать стаж в экселе
Если мы хотим рассчитать стаж в Excel в полном формате, например «1 г. 2 мес. 3 дн.«, то запишем формулу стажа работы в следующем виде:
В результате подсчета стажа получаем:
Как посчитать стаж с перерывами в Excel?
Немного усложним задачу и представим, что нам нужно посчитать стаж работы сотрудника работающего с перерывами, т.е. за несколько периодов.
Для простоты рассмотрим, что сотрудник работал 2 периода и сначала для каждого из них рассчитаем стаж работы:
Для подсчета общего стажа за 2 периода воспользуемся простой математикой, а именно к концу первого периода (дате увольнения) прибавим длительность второго (разность между увольнением и приемом).
Таким образом у нас получится новый период, который формально состоит из суммы двух, и для него уже воспользуемся знакомой формулой:
Если нужно посчитать длительность работы за 3 и более периода, то эту формулу можно модифицировать точно по такому же принципу.
Как посчитать возраст в Excel?
Предположим, что у нас есть таблица с датами рождения сотрудников в компании:
Как посчитать стаж работы в экселе
Для подсчета возраста воспользуемся аналогичным алгоритмом, что и при расчете стажа.
Единственным отличием будет использование сегодняшней даты вместо даты окончания работы:
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
Источник: tutorexcel.ru
Как в excel посчитать возраст по дате рождения или стаж по дате трудоустройства
Но несмотря на это, если мы ее пропишем в 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.
Источник: sirexcel.ru
Формулы для расчета сроков возраста трудового стажа в Excel
Самый простой способ вычисления возраста или трудового стажа реализуется с помощью функции =РАЗНДАТ(). Эта таинственная функция долгое время не была описана в справке Excel. Более того ее нет ни в одном списке формул, поэтом чтобы использовать эту функцию необходимо вводить только вручную в строку формул или непосредственно в саму ячейку. Несмотря на это она доступна еще с 2000-го года версий Excel и прекрасно справляется со своей основной задачей.
Примеры формул с использованием функции РАЗНДАТ в Excel
Благодаря функции РАЗНДАТ легко выполнить сложные расчеты для сравнения дат. Ведь главная сложность заключается в, том что раз в 4 года наступает високосный год и тогда количество дней в году изменяется с 365 на 366.
Чтобы вычислить возраст от даты рождения с помощью функции РАЗНДАТ используйте формулу подобную на эту:
Для расчета трудового стажа примените такую формулу:
Функция РАЗНДАТ возвращает количество дней, месяцев или лет прошедших на протяжении периода времени между двумя датами. Для работы с функцией следует заполнить 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?
Существуют и другие способы создания формул для вычисления разницы между датами без использования функции РАЗНДАТ:
Теперь если нам нужно учитывать и неполные года эксплуатации автомобиля нам нужно использовать другую формулу:
Как видно на примере функция РАЗНДАТ не решает любые вычислительные задачи с датами. Иногда следует прибегать к другим узконаправленным функциям.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Карта сайта
- Скачать примеры
Источник: exceltable.com