Расчет стажа в Excel

О правилах расчета

О правилах подсчета страхового стажа см. ]]>Приказ Министерства здравоохранения и социального развития РФ от 06.02.07 № 91]]> «Об утверждении правил подсчета и подтверждения страхового стажа для определения размеров пособий по временной нетрудоспособности, по беременности и родам», а также ]]>Постановление Правительства РФ от 02.10.2014 N 1015 (ред. от 10.05.2017) «Об утверждении Правил подсчета и подтверждения страхового стажа для установления страховых пенсий»]]>.

Чтобы не перечитывать эти документы, можно воспользоваться письмом-разъяснением от Фонда социального страхования РФ от 30 октября 2012 г. N 15-03-09/12-3065П:

Согласно п. 21 Правил подсчета и подтверждения страхового стажа для определения размеров пособий по временной нетрудоспособности, по беременности и родам, утвержденных Приказом Минздравсоцразвития России от 06.02.2007 N 91, исчисление периодов работы (службы, деятельности) производится в календарном порядке из расчета полных месяцев (30 дней) и полного года (12 месяцев). При этом каждые 30 дней указанных периодов переводятся в полные месяцы, а каждые 12 месяцев этих периодов переводятся в полные годы.

При расчете страхового стажа перевод каждых 30 дней указанных периодов в полные месяцы, а каждых 12 месяцев этих периодов в полные годы предусматривается только для неполных календарных месяцев и неполных календарных лет.

В случае если календарный месяц или календарный год отработан работником полностью, то делить количество отработанных дней на 30 дней и соответственно 12 месяцев не нужно.

Ниже приведен пример расчета на основе этих документов.

Как в excel посчитать возраст по дате рождения или стаж по дате трудоустройства

до 31 мая 2017 г.

  • 5 полных месяцев в 2017 году: июнь+июль+август+сентябрь+октябрь
  • 6 дней в ноябре 2017 года: с 01 ноября 2017 года по 06 ноября 2017 года (6-е число включительно)
  • Итого стаж: 6 лет 5 месяцев 16 дней, т.к. 10+6=16 дней.

    Примечание. В этой статье понятие Полный месяц отличается от аналогичного понятия в статье MS EXCEL: Сколько полных месяцев прошло?, т.к., например, период с 01.01.2010 по 31.01.2010 в данной статье считается полным месяцем, а в упомянутой статье полным месяцем является период с 01.01.2010 по 01.02.2010 (так считает функция РАЗНДАТ()).

    Таким образом, весь стаж разбивается на 4 периода:

    • Cначала подсчитывается количество дней в первом неполном месяце.
    • Затем, определяется количество полных лет (с начала первого полного месяца до конца предыдущего месяца, но отстоящего на полное количество лет).
    • Далее подсчитываются полные месяцы.
    • И, наконец, дни в последнем, часто неполном, месяце. Расчеты приведены в файле примера на листе Задача (1 период).

    Для удобства настройки дат использованы элементы управления формы Полоса прокрутки и Счетчик.

    Автоматизированная таблица расчета стажа сотрудников

    Будущая таблица, в которой будут вестись расчеты трудового стажа, должна иметь определенную шапку. В ней должны быть указаны столбцы, в которых будут проводить промежуточные и окончательные подсчеты. Шапка выглядит так.

    Столбцы ЛЕТ, МЕСЯЦЕВ и ДНЕЙ подсветили цветом, т.к. именно в них будут подсчитываться конечные результаты, которые потребуются бухгалтеру.

    Создадим несколько позиций, заполнив часть таблицы. С ФИО все понятно, а вот при заполнении столбцов ПРИНЯТ и УВОЛЕН нужно будет изменить формат ячеек. В новых версиях 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» Количество дней без учета лет

    Скачать программу для расчета стажа.

    Обратите внимание! Как можно изящно посчитать итоговые показатели стажа суммируя все периоды работы на разных рабочих местах.

    ≡ 30 Июнь 2015 · Рубрика: Могучий MS Excel

    Как посчитать стаж или возраст в excel

    Теперь попробуем решить другую задачу и посчитать от даты 01.01.1980 до даты 01.01.2016 сколько прошло полных лет, полных месяцев или дней. Хочу сказать, что решать подобную задачу очень часто приходится бухгалтерам. Представителям данной профессии приходится регулярно считать стаж проработанный человеком и его полный возраст. Но как показывает практика, бухгалтера не умеют считать стаж в Excel только из-за того, что не знаю функцию РАЗНДАТ.

    РАЗНДАТ(первая_дата;вторая_дата;тип_расчета) — функция предназначенная для расчета разницы между датами.

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

    Скрин таблицы очень хорошо демонстрирует как работает данная формула с первыми тремя типами расчета.

    Подсчет количества полніх лет, месяцев и дней с помощью функции =РАЗНДАТ

    Оставшиеся три типа расчета очень интересны для получения более полезной и красивой информации. Итак имеем таблицу в которой казана дата приема на работу сотрудника на работу и сегодняшняя дата. И будем вести расчет стажа.

    Расчет стажа и возраста с помощью функции =РАЗНДАТ

    На рисунке видно как в колонке «Стаж» рассчитывается полный и правильный стаж человека. Туда записана достаточно длинная формула.

    =РАЗНДАТ(C19;D19;»y»)&» г. «& РАЗНДАТ(C19;D19;»ym») &» м. «& РАЗНДАТ(C19;D19;»md»)&» дн.»

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

    Думаю теперь Вы сможете рассчитать разницу в днях между датами или посчитать полный стаж сотрудника. На крайний случай сделайте файлик и посчитайте сколько осталось до ожидаемого собития.

    Как автоматизировать расчет стажа работника в Excel&nbsp&nbsp

    Стоит заметить, что все показатели — это РАЗНИЦА между датами.

    Исходя из возможностей РАЗНДАТ, прибегая к помощи функции СЦЕПИТЬ, можно вычислить стаж сотрудника. Общий вид функции будет:
    =РАЗНДАТ(B2;B3;»y») & » лет » & РАЗНДАТ(B2;B3;»ym») & » месяцев » & РАЗНДАТ(B2;B3;»md») & » дня(ей)»

    Если сейчас поменять данные в ячейках, где мы указывали начало и окончание работы, вы автоматически заставите функцию пересчитать стаж.

    Конечно же вы можете использовать эту функцию в своих целях, так как не обязательно, что периодом будет стаж работы.

    Не забудьте поделиться ссылкой на статью ⇒

    В прошлом уроке я уже рассказывал как удалить дубликаты с помощью специальной функции, которая появилась с 2007 версии. Сегодня поговорим про возможность, которая подойдет и для более ранних версий. Будем использовать расширенный фильтр.

    • 03.11.2015
    • Просмотров: 2366
    • Excel
    • Видеоурок

    Читать полностью

    В этом уроке расскажу что такое транспонирование в Excel. Благодаря этой функции можно поменять строки местами. Это может понадобиться тогда, когда вы создаете таблицу и по мере ее заполнения добавляете параметры по горизонтали и вертикали. Со временем вы понимаете, что для большей наглядности было бы лучше, если бы мы поменяли строки и столбцы.

    • 27.11.2015
    • Просмотров: 2275
    • Excel

    Читать полностью

    В этом уроке расскажу как сделать выпадающий список в Excel. В основном он создается с одной целью — ограничить возможность ввода данных определенным списком. Например, когда в таблице есть специальная ячейка, в которой вы отмечаете к какому отделу относиться тот или иной сотрудник. Этот список всегда один и тот же. Намного удобнее выбирать отдел из списка, чем вводить его каждый раз вручную или копировать из других ячеек.

    • 21.11.2015
    • Просмотров: 3412
    • Excel
    • Видеоурок

    Читать полностью

    В сегодняшнем уроке я расскажу как вставить формулу в Word. На самом деле существует несколько способов это сделать и по возможности я постараюсь рассмотреть их все.

    • 04.02.2016
    • Просмотров: 11276
    • Word

    Читать полностью

    В этом уроке расскажу как сделать разбивку текста по столбцам в Excel. Данный урок подойдет вам в том случае, если вы хотите произвести разбивку текста из одного столбца на несколько. Сейчас приведу пример. Допустим, у вас есть ячейка «A», в которой находится имя, фамилия и отчество. Вам необходимо сделать так, чтобы в первой ячейке «A» была только фамилия, в ячейке «B» — имя, ну и в ячейке «C» отчество.

    • 15.12.2015
    • Просмотров: 3472
    • Excel
    • Видеоурок

    Читать полностью

    >Формулы для расчета сроков возраста трудового стажа в 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г.

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

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

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

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

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

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

    =ОКРУГЛВВЕРХ(ДОЛЯГОДА(A2;A4);0)

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

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

    EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

    ВСЕ УРОКИ

    Поиск по сайту:

    Пользовательский поиск

    Назад

    Как автоматизировать расчет стажа работника в Excel

    ТАБЛИЦА РАСЧЕТА СТАЖА СОТРУДНИКОВ В EXCEL

    Для расчета стажа нам понадобятся следующие умения:

    1. Расчет количества дней путем вычитания одной даты из другой
    2. Абсолютная ссылка
    3. Округление в меньшую сторону

    Подготовим таблицу, где при изменении текущей даты стаж сотрудников будет автоматически пересчитываться:

    Начнем с того, что установим текущую дату, которая будет автоматически меняться каждый день.

    Теперь посчитаем общее количество дней путем вычитания из текущей даты даты поступления на работу.

    Не забываем установить числовой формат для ячейки, где будет считаться количество дней.

    Установим абсолютную ссылку на ячейку с указанием текущей даты. В случае, если список сотрудников большой, мы, таким образом, избежим того, чтобы добавлять формулу в каждую строку.

    Для расчета количества лет делим полученное общее количество дней на 365:

    Округлим полученное значение в сторону меньшего целого числа

    Тут отметим один момент. Если мы используем обычное округление, то столкнемся с тем, что числа, имеющие в десятых 5 и больше, будут округлены до большего значения. В данном случае нас это не устраивает.

    Для того, чтобы округлить значение в сторону меньшего используем функцию «ОКРУГЛВНИЗ».

    Для этого выбираем «функции» — > «Другие функции…»:

    В «Математических» находим нашу функцию «ОКРУГЛВНИЗ» и нажимаем ОК:

    Нажимаем на кнопочку (обведена красным),

    выбираем цифру (выделив нужную ячейку), которую собираемся округлить, и возвращаемся в окошко функции нажатием на кнопочку (обведена красным):

    Ставим «0» в окошке «Число разрядов» и нажимаем ОК:

    Идем дальше. Для того, чтобы рассчитать количество месяцев, берем в скобки разницу между неокругленным и округленным значениями, умножаем на количество месяцев в году (переводим годы в месяцы):

    Округляем месяцы по той же схеме что и годы с помощью «ОКРУГЛВНИЗ». Для ускорения процесса можно просто скопировать ячейку с формулой:

    и вставить в ячейку, где будет округляться значение месяцев:

    Осталось посчитать количество дней. Автоматизировать этот процесс до получения безошибочного значения не получится, т.к.

    количество дней в месяце различно. Дни будут считаться с погрешностью 1-2 дня.

    Так же как и в случае с расчетом месяцев, берем в скобки разницу между неокругленным и округленным значениями, умножаем на среднее количество (365/12=30,42) дней в месяце (переводим месяцы в дни):

    Осталась самая малость. Копируем все полученные формулы:

    Выделяем область до конца списка сотрудников и вставляем формулы:

    Таблица готова. При изменении текущей даты, стаж будет пересчитан автоматически.

    Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой «эксель» по адресу: ngt@inbox.ru

    Назад&nbsp&nbsp&nbsp Вверх страницы

    Рассчитаем страховой (трудовой) стаж в годах, месяцах и днях.

    Для вычисления страхового стажа необходимо учесть время работы сотрудника в данной компании и ранее в других организациях.

    Рассчитав стаж для каждого из периодов, сложим продолжительность всех периодов вместе: годы с годами, месяцы с месяцами, а дни с днями. При этом каждые 30 дней переведем в полный месяц, а каждые 12 месяцев переведем в полный год. Расчеты приведены в файле примера на листе Задача (несколько периодов).

    Для удобства расчетов каждый период можно включать или исключать из вычисления общего стажа с помощью элемента управления Флажок.

    Рассмотрим возможности расчета стажа или возраста человека в Excel в полных годах, месяцах или днях.

    Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

    РАЗДАТ(нач_дата; кон_дата; единица)
    Вычисляет количество дней, месяцев или лет между двумя датами.

    • Начальная дата (обязательный аргумент) — дата начала периода;
    • Конечная дата (обязательный аргумент) — дата окончания периода;
    • Единица (обязательный аргумент) — тип возвращаемого значения:
      • «Y» — разница в полных годах;
      • «M» — разница в полных месяцах;
      • «D» — разница в полных днях;
      • «MD» — разница в днях без учета месяцев и годов;
      • «YM» — разница в месяцах без учета дней и годов;
      • «YD» — разница в днях без учета годов.
    admin