Excel: Вычисление стажа работы
Работники кадровых служб постоянно сталкиваются с задачей вычисления стажа работы сотрудников организации. В этом им может помочь функция Excel — РАЗНДАТ(), вычисляющая длительность интервалов дат.
Нюанс в том, что Вы не найдете эту функцию в списке Мастера функций, нажав кнопку fx .
Найти описание этой функции, и ее аргументов можно только в полной версии англоязычной справки.
Синтаксис функции следующий:
РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)
Последний аргумент определяет, каким образом и в каких единицах будет измеряться интервал между начальной и конечной датами.
Этот параметр может принимать следующие значения:
разница в полных годах
в полных месяцах
разница в днях без учета лет
разница в днях без учета месяцев и лет
разница в полных месяцах без учета лет
На рисунке ниже представлены различные варианты применения этой функции.
В ячейке А1 — дата поступления на работу, в ячейке А2 — дата увольнения.
Можно подсчитать, например, стаж работы в виде «… г. … мес. … дн.», для этого введем в ячейку В7 следующую формулу:
Программа для расчета стажа работы в Excel скачать
Excel позволяет не только производить операции с числами, но также считать интервалы между датами и временными промежутками. Это позволяет очень удобно считать трудовой стаж. Поэтому если бухгалтер освоит эту несложную процедуру, то в дальнейшем ему будет очень легко вести подсчеты.
Рассмотрим по шагам как посчитать стаж работы в Excel. А так же узнаем, как правильно составить шаблон для подсчета стажа, чтобы можно было всегда им пользоваться.
Автоматизированная таблица расчета стажа сотрудников
Будущая таблица, в которой будут вестись расчеты трудового стажа, должна иметь определенную шапку. В ней должны быть указаны столбцы, в которых будут проводить промежуточные и окончательные подсчеты. Шапка выглядит так.
Столбцы ЛЕТ, МЕСЯЦЕВ и ДНЕЙ подсветили цветом, т.к. именно в них будут подсчитываться конечные результаты, которые потребуются бухгалтеру.
Создадим несколько позиций, заполнив часть таблицы. С ФИО все понятно, а вот при заполнении столбцов ПРИНЯТ и УВОЛЕН нужно будет изменить формат ячеек. В новых версиях Excel они меняются автоматически, если вводить дату стандартным образом: ЧЧ.ММ.ГГГГ. На всякий случай лучше заранее поменять формат. Для этого выделяем все ячейки в колонках «Принят» и «Уволен» (кроме шапки таблицы), правой кнопкой вызываем контекстное меню и выбираем ФОРМАТ ЯЧЕЕК (CTRL+1) – ДАТА.
При этом видим, что формат даты тоже можно выбрать. Одним нравится в числовом эквиваленте, другие предпочитают, чтобы месяц прописывался словом и т.п. Мы выберем именно частично словесный формат.
Вводим дату начала работы сотрудников и их увольнения. Пусть все они устроились работать в разные дни и годы, а уволились в один.
Формула в Excel для подсчета стажа РАЗНДАТ
Подсчитаем количество дней, которые отработали сотрудники. Т.к. трудовой стаж учитывается независимо от перерывов (выходных, больничных и праздничных дней), нужно просто вычесть из конечной даты начальную Excel позволяет это сделать. Записываем в ячейку D2 формулу =С2-В2.
Получилось 6580 дней. Переведем их в годы при этом не достаточно просто поделить на 365, ведь в году может быть и 366 дней. Так же когда мы будем переводить в количество полных месяцев без учета лет, то следует учитывать тот факт что один месяц может иметь 30 и 31 день. Поэтому для точного анализа следует использовать специальную функцию РАЗНДАТ:
- для ячейки E2(Лет): =РАЗНДАТ(B2;C2;»y»);
- для ячейки F2(Месяцев): =РАЗНДАТ(B2;C2;»ym»);
- для ячейки G2(Дней): =РАЗНДАТ(B2;C2;»md»);
Внимание! Данная функция не содержится в мастере функций, поэтому ее нужно вводить вручную.
Таблица параметров функции РАЗНДАТ:
| Параметр | Описание |
| «d» | Количество полных дней |
| «m» | Количество полных месяцев |
| «y» | Количество полных лет |
| «ym» | Количество полных месяцев без учета лет |
| «md» | Количество дней без учета месяцев и лет |
| «yd» | Количество дней без учета лет |
Обратите внимание! Как можно изящно посчитать итоговые показатели стажа суммируя все периоды работы на разных рабочих местах.
Вычисление возраста или стажа функцией РАЗНДАТ (DATEDIF)
Для вычислений длительностей интервалов дат в Excel есть функция РАЗНДАТ, в английской версии — DATEDIF.
Нюанс в том, что Вы не найдете эту функцию в списке Мастера функций, нажав кнопку fx — она является недокументированной возможностью Excel. Точнее говоря, найти описание этой функции и ее аргументов можно только в полной версии англоязычной справки, поскольку на самом деле она оставлена для совместимости со старыми версиями Excel и Lotus 1-2-3. Однако, несмотря на то, что эту функцию не получится вставить стандартным способом через окно Вставка — Функция (Insert — Function) , ее можно вручную вписать в ячейку с клавиатуры — и она сработает!
Синтаксис функции следующий:
=РАЗНДАТ( Начальная_дата ; Конечная_дата ; Способ_измерения )
С первыми двумя аргументами все более-менее понятно — это ячейки со стартовой и конечной датами. А самый интересный аргумент, конечно, последний — он определяет, каким именно образом и в каких единицах будет измеряться интервал между начальной и конечной датами. Этот параметр может принимать следующие значения:
| «y» | разница в полных годах |
| «m» | в полных месяцах |
| «d» | в полных днях |
| «yd» | разница в днях с начала года без учета лет |
| «md» | разница в днях без учета месяцев и лет |
| «ym» | разница в полных месяцах без учета лет |
Т.е. при желании подсчитать и вывести, например, ваш стаж в виде «3 г. 4 мес. 12 дн.», необходимо ввести в ячейку следующую формулу:
где А1 — ячейка с датой поступления на работу, А2 — с датой увольнения.
или в английской версии Excel:
Ссылки по теме
Если подсчитанное количество дней стажа в А1, то:
A2=ЦЕЛОЕ(A1/365) — количество лет
A3=ЦЕЛОЕ(ОСТАТ(A1;365)/30,5) — количество месяцев
A4=A1-A2*365-A3*30,5 — количество дней
Николай, спасибо за подсказку. В моём случае, дополнительно к приведённой формуле, пришлось применить поправочную константу » -1 «. Формула приняла вид =(МЕСЯЦ(A2)-МЕСЯЦ(A1))-1 .
Тем кто решит этим воспользоваться :
данное вычисление, для моей задачи, корректно действет в пределах календарного года. Если даты из разных календарных лет, то результат, для поставленной задачи, будет неверен. Наводка получена, попробую универсальную конструкцию из формул организовать.
Ещё раз спасибо.
Нужна помощь специалистов.
Имею время начала операции и время окончания операции в следующем формате.
Нужно рассчитать время выполнения операции в часах.
| Вр. создания | Вр. решения | Длит.выполнения |
| 10.02.2014 16:35 | 10.02.2014 16:54 | |
| 10.02.2014 16:56 | 11.02.2014 09:18 |
Как это можно осуществить?
Буду очень признателен за помощь.
| Вр. создания | Вр. решения | Длит.выполнения |
| 10.02.2014 16:35 | 10.02.2014 16:54 | =B1-A1 |
| 10.02.2014 16:56 | 11.02.2014 09:18 |
..
Подскажите как посчитать продолжительность отпуска ( с вычетом заданных праздничных дней) но не в рабочих днях, а в календарных.
Я пробовала задать в функции WORKDAY.INTL условие — что нет выходных, но это не работает
У нас подсчет отпусков ведется в календарных днях (минус праздники , выпадающие на период отпуска)
Николай, здравствуйте!
Подскажите, существует ли вариант подсчета количества календарных дней за вычетом праздничного дня (при его наличии в заданном диапазоне), в условии заданы начальная и конечная дата. Мне это необходимо для подсчета дней отпуска.
Заранее спасибо за ответ,
Трофимова Татьяна
Прибавить один?
Прошу совета
Использую формулу вычисления возраста =РАЗНДАТ(A1; СЕГОДНЯ(); «y»)
A1 в формате дата.
Если А1 не заполнено (то есть равно 0) то возраст показывается как 116
как этого избежать?
| ФИО | Принят | УВОЛЕН | ОТРАБОТАНО | ЛЕТ | МЕСЯЦЕВ | ДНЕЙ |
| Сидоров Иван Петрович | 24.02.2014 | 29.05.2018 | 1555 | 4 | 3 | 5 |
| 03.09.2018 | 14.10.2018 | 41 | 0 | 1 | 11 | |
| 03.11.2018 | 19.12.2018 | 46 | 0 | 1 | 16 | |
| 25.04.2019 | 27.11.2020 | 582 | 1 | 7 | 2 | |
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 0 | 0 | 0 | 0 | |||
| 2224 | 5 | 12 | 34 | |||
| 6,09 | =ПРАВСИМВ(ГОД(I19);2) | =МЕСЯЦ(I19) | =ДЕНЬ(I19) | |||
| I19 > | 01.02.1906 | 06 | 2 | 1 |
помогите, пожалуйста, никак не могу сообразить. надо просчитать количество лет (нужно получить не целое число) просрочки платежей с учетом того, что просрочка составляет больше одного года и часть периода просрочки может попадать на високосный год, а может не попадать.
например, срок оплаты 20.10.2015, нужен срок просрочки задолженности на 10.02.2017 в нецелых годах с учетом того, что в 2015 году 365 дней, в 2016 — 366, и в 2017 — 365. на данный момент я высчитываю сначала кол-во дней с 20.10.15 по 01.01.16 плюс с 01.01.17 по 10.02.17 и делю на 365, потом прибавляю один год. либо если надо посчитать просрочку например, на 20.10.16 то сначала считаю просрочку 2015 года и делю на 365, потом отдельно за 2016 и делю их на 366 и итоги суммирую. скорее всего это абсолютно и совершенно кустарный метод на коленке, но другого не знаю, а потом взываю к вам за помощью и советом:)
Здравствуйте! А вы можете сделать видео урок посещенной функции «РАЗНДАТ»?
По вычислению трудового стажа?
Заранее спасибо
Добрый день!
В первой из двух ячеек с датами для расчета, через Alt+Enter перечислено несколько дат. Есть ли возможность сконструировать формулу так, чтобы она могла отсортировывать и обращаться к первой из имеющихся в первой ячейке дат или такая возможность отсутствует и содержимое этой ячейки программой воспринимается как свалка? Заранее спасибо!
Здравствуйте, уважаемые Знатоки!
Я прошу помощи в следующем.
Имеются два столбика. Первый столбик — это дата начала периода. Второй столбик — дата окончания периода. Каждая строка представляет ПЕРИОД, протяженностью несколиких лет (больше одного).
Нужно вычислить количество месяцев для каждого «составляющего» года. То есть итог будет выглядеть результатом для каждого года, который вкладывается в период каждой строки.
Например для периода 02.01.2018 (столбик 1) — 28.06.22 (столбик 2) результат будет выражен следующим:
Столбик 3 (2018 год) — 12 месяцев;
Столбик 4 (2019 год) — 12 месяцев;
Столбик 5 (2020 год) — 12 месяцев;
Столбик 6 (2021 год) -12 месяцев;
Столбик 7 (2022 год) -6 месяцев.
Всего месяцев — 54
Необходимость связана с планированием и расчетами долгосрочных проектов (работ), когда необходимо автоматически (при изменении дат начала или окончания при планировании и расчетах) получать количество месяцев производимых работ в каждом годе, входящим в состав поолного ПЕРИОДА. Фактически формула кажется должна иметь универсальную архитектуру и количество лет будет определять эту формулу с интерпретацией для каждого входящего в полный период года.
В принципе, с учетом срочной необходимости, меня устроит ЦЕЛОЕ число месяцев, поскольку, видимо, сложно определить точное число месяев (не целое).
Я попробовал составить формулу, но у меня получается «дремучий лес» логических условий, до конца дойти не могу.