Как посчитать дни отпуска в excel

Расчет отпускных дней в Excel

Как пользоваться калькулятором отпускных дней

Пользоваться калькулятором достаточно просто. Расчет сделан в привычной для многих программе Excel.

Все что нужно для расчета отпуска — это:

1. Указать дату трудоустройства;
2. Отметить свои предыдущие дни отпуска.

Excel автоматически рассчитает количество начисленного и использованного отпуска, а также покажет сколько еще положено отпускных дней на текущую дату.

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

Как сделать калькулятор самостоятельно

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

Далее нам потребуется текущая дата. Чтобы не указывать ее каждый раз при открытии файла, можно воспользоваться формулой =СЕГОДНЯ().

Теперь посчитаем количество отпускных дней с начала трудоустройства. Так как в год нам положено 28 календарных дней, то посчитаем количество отработанных дней поделим на 365. Для этого введем следующую формулу
=ОКРУГЛ((C4-C3)/365*28;0). Я дополнительно округлил полученное число до целых.

Дальше самое сложное — это вспомнить сколько дней уже было отгулянно. Для этого я сделал "умную" таблицу Журнал отпусков, в которой будем отмечать все наши отпуска. В этой таблице необходимо заполнять начало и конец отпуска. Календарные дни почитаются автоматически по формуле =[@Конец]-[@Начало]+1.

Если в отпуск вошли праздничные дни, то их нужно исключить, так как они не учитываются при расчете отпускных дней. Отметьте их количество в столбце Праздничные дни. Количество отгулянных дней необходимо посчитать по следующей формуле =[@[Календарные дни]]-[@[Праздничные дни]].

Складываем все отгулянные дни. В ячейке C6 используем для этого такую формулу: =СУММ(Журнал[Отгулянные дни]).

Осталось в ячейке C8 вычесть из общего количества начисленного отпуска сумму отгулянного отпуска по формуле =C5-C6. Вот и все, мы посчитали количество оставшегося отпуска на текущую дату. Теперь вы всегда будете знать сколько отпуска вам осталось. При открытие файла, Excel автоматически будет обновлять все данные.

Расчет среднего заработка для отпускных в Excel

Правильный расчет отпускных волнует бухгалтеров, кадровиков (больше – даты и сроки) и самих работников. С помощью средств Excel некоторые расчетные операции можно автоматизировать. Рассмотрим варианты создания простых программок.

Расчет количества дней отпуска

Составим программку для контроля количества положенных работнику дней отпуска. Государственные праздники не входят в отпускные периоды. Поэтому сначала сделаем таблицу в Excel с государственными праздниками.

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

Формула объединяет значения ячеек В3, С3 и результат функции ГОД. Чтобы показать даты праздников в прошлом году, ставим «-1». В будущем году – «+1». Для столбца на 2018 год – «+2».

На 2-м листе сделаем табличку для расчета количества дней, положенных к отпуску.

Это первый блок таблицы. Во втором блоке покажем общий стаж, сколько дней отработано за период и сколько дней отпуска положено работнику. Шапка блока:

Чтобы автоматически устанавливалась текущая дата, используется функция СЕГОДНЯ.

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

Количество отработанных дней – разница между текущей датой и датой приема (=D$3-C4).

Чтобы найти, сколько дней отпуска наработано на текущую дату, используем следующую формулу:

Размножаем формулы на весь столбец.

Третий блок – найдем расчетный период для количества дней отпуска за первый год. Начало периода – ссылка на ячейку с датой приема на работу. Конец периода находим по формуле: =ДАТАМЕС(G4;12)-1, где G4 – начало расчетного периода.

Четвертый блок – данные по отпускам за период. Количество дней отпуска и начальную дату проставляем вручную. Делаем вспомогательный столбец для расчета конечной даты отпуска с учетом праздников. Формула: =J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14 = J4) * (гос.праздники!D3:G14

Excel прибавляет к начальной дате отпуска количество дней за минусом 1. Затем проверяет наличие праздничных дат в диапазоне. Если таковые имеются, прибавляет их число к количеству отпускных дней.

Формула для расчета конечной даты отпуска:

Общее число отгулянных дней нашли по формуле: =ЕСЛИ(ИЛИ(I4="";J4="");0;I4+I5).

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

Как рассчитать отпускные за год в Excel

Схема расчета отпускных выглядит так:

1. Найти сумму заработной платы за год, предшествующий отпуску. Сюда входят только те платежи, которые предусмотрены по контракту. Не учитываются разовые премии, оплата больничного листа, материальная помощь и т.п.
2. Рассчитать среднемесячный заработок. Для этого сумму годовой зарплаты делим на 12.
3. Рассчитать средний дневной заработок. Необходимо разделить среднемесячную зарплату на среднее число календарных дней в месяце (29,3).
4. Средний дневной заработок умножить на количество дней отпуска.

Тимохин И.И. идет в отпуск с 01.02.2016 на 14 дней. Ежемесячный оклад согласно контракту – 20 000 рублей. Предусмотрена надбавка в размере 1 500 рублей ежемесячно и премия 20%.

Расчетный период – с 01.02.2015 по 31.01.2016 гг.

Данные о зарплате работника за период, предшествующий отпуску:

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

Таблица данных о зарплате имеет следующие столбцы для расчетов:

Формулы, которые были использованы для введения данных о зарплате:

• количество рабочих дней (В3):
• надбавка (D3): =C3*$D$1
• фактически отработанное время (Е3) :=ЕСЛИ(I3>0;B3-ЧИСТРАБДНИ(G3;H3);B3)
• премия (F3):=ЕСЛИ(B3=E3;C3*20%;(C3/30,5*E3)*20%)
• число дней, когда человек был на больничном (I3)=ЕСЛИ(ИЛИ(G3="";H3="");"";(H3-G3)+1)
• итого начислено (L3) : =ЕСЛИ(J3

А так же сумма отработанных дней в неполных месяцах(C20): зарплата!E3:E14;зарплата!E3:E14;0))’ >

Обратите внимание! Формулы в ячейках C19 и C20 выполняются в массиве для этого после их ввода следует нажимать комбинацию клавиш CTRL+SHIFT+Enter.

Делаем дополнительную табличку:

Формула для расчета количества дней: =29,3*$H$14+29,3/30*$G$6.

Формулы для расчета средней заработной платы и отпускных:

Точно так же рассчитываются отпускные при увольнении в Excel и компенсации за неиспользованный отпуск.

Чтобы рассчитать отпускные по уходу за ребенком в Excel, нужно выполнить следующие операции:

1. Найти средний заработок за два предшествующих отпуску календарных года.
2. Полученную величину разделить на количество календарных дней расчетного периода.
3. Средний дневной заработок умножить на 30,4 и на 40%.

Материалы:

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

Для применения алгоритма подойдут приведенные выше таблицы и формулы.