Как рассчитать выплаты для сотрудников в период отпусков

Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах

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

Ее синтаксис элементарный.

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

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

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

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

Пример №1. Подсчет количества лет между двумя датами

На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.

Разница дат в полных месяцах

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

То есть, формула следующая.

=РАЗНДАТ(A2;B2;”m”)

Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно.

Разница в днях без учета лет

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

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

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Сдвиг даты на заданное количество дней

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

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ(NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Расчет отпускных при досрочном отпуске

По истечении 6-ти месяцев работы на предприятии работник имеет право на оплачиваемый отпуск, однако этой возможностью он может воспользоваться и до истечения шестимесячного срока (статья 122 ТК). Продолжительность такого отпуска должна быть согласована с работодателем.

При этом законом (письмо Роструда от 24.12.2007 г. № 5277-6-1) не запрещено использовать все дни положенного отпуска. Как правило, работодатели не препятствуют такой ситуации, поскольку имеют законное право удержать из заработной платы работника сумму задолженности за неотработанные дни (статья 137 ТК).

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

Пример Б

Сотрудник оформляет отпуск с 19.05.2014 г. сроком на 14 дней, отработав на предприятии неполный месяц (с 01.05. по 18.05.2014 г.). Начисленная заработная плата за данный срок составила 17000 руб.

Расчет отпускных будет следующим:

Среднедневной заработок составит: 17000 руб. / 17,01 д. = 999,41 руб.

Общая сумма отпускных: 999,41 руб. х 14 д. = 13991,74 руб.

На оплату налога на доходы физлица необходимо удержать: 13991,74 руб. х 0,13 = 1818,93 руб.

К выплате сотруднику подлежит сумма: 13991,74 руб. – 1818,93 руб. = 12172,81 руб.

Перенос выходных дней

На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос (рис. 7.13).

Даты дней, которые вследствие переноса стали выходными, добавим в список праздников, находящийся в диапазоне A15:G16 листа Праздники (рис. 7.5).

Теперь в табеле нам нужна формула, которая будет определять, не перенесен ли данный выходной. Для ее получения доработаем формулу из ячейки С27 табеля.

Рис. 7.13.

Скопируйте ее из строки формул, вставьте в ячейку С31, замените имя Да-таПраздн именем ДатаПеренос и удалите функцию НЕ.

Результирующая формула в ячейке С31 должна иметь следующий вид:

ЕНД(ВПР(С9;ДатаПеренос;1;ЛОЖЬ))

Формула возвращает значение ЛОЖЬ, если находит дату среди перенесенных, или значение ИСТИНА, если не находит. Таким образом, эта ячейка будет содержать значение ЛОЖЬ, если день вследствие переноса стал рабочим. Если же ячейка содержит значение ИСТИНА, значит, статус дня не изменился.

Теперь мы должны проанализировать значения ячеек С3О и С31. Первая содержит логическое значение, которое указывает на то, является ли день выходным либо праздничным. Логическое значение во второй ячейке отражает факт изменения статуса дня (то есть не стал ли он вследствие переноса выходных рабочим днем).

Формула для анализа должна работать так. День является выходным, если его статус не изменился (С31 содержит значение ИСТИНА), и он является праздничным, субботним или воскресным (СЗО содержит значение ИСТИНА). Во всех остальных случаях день будет рабочим.

Составить такую формулу можно с помощью функции И. Она возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Данная функция принадлежит к категории Логические и имеет следующий синтаксис:

И(логическое_значение1;логическое_значение2; …)

Здесь логическое_значение1, логическое_значение2, … — это от I до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.

Введите в ячейку С32 такую функцию:

И(С30;С31)

В данную функцию надлежит вложить формулы из ячеек СЗО и С31. Ниже показано, как выглядит результирующая формула в ячейке С32:

И(ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7; ИСТИНА; ЕСЛИ(ДБНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ));С27); ЕНД(ВПР(А9;ДатаПеренос;1;ЛОЖЬ)))

Скопируйте формулы из ячеек С27 и С32 в ячейки С10 и С11.

Возможно, будет полезно почитать:

Коррекция наращенных ресниц: типы коррекции, описание процедуры, уход ;
История создания легендарной куклы Барби (Barbie) Друзья Барби: кто они ;
Годовалый ребенок закатывает истерики ;
Крики, слезы, топанье ногами, разбрасывание вещей и вспышки агрессии… ;
Годовалый ребенок закатывает истерики ;
Эффективные советы психолога ;
Как воспитывают детей в италии ;
Ребенок не хочет учиться: практические советы психолога ;

Аутсорсинг расчета зарплаты и кадров — зачем он нужен

Расчет зарплаты и КДП — самые чувствительные участки бухгалтерии, которые отражают отношение компании к своим сотрудникам. Ошибки влияют на лояльность, мотивацию и продуктивность персонала компании, и вместе с тем увеличиваются риски трудовых споров.

Сторонняя бухгалтерская компания рассчитывает зарплаты и ведет кадровое делопроизводство своих клиентов. Отдать бухгалтерию и кадры на аутсорсинг – это автоматизировать рутинные задачи и делегировать сложные процессы.

В чём выгода передачи дел на аутсорсинг:

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

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

Добавить дни

Добавить рабочие дни до даты

Если вам нужно добавить или вычесть рабочие дни (рабочие дни) для даты, чтобы можно было рассчитать дату в будущем или прошлом, которая пропускает выходные дни (и праздники), вы можете использовать функцию РАБДЕНЬ. Праздники не являются обязательными.

В этом примере формула в D5:

Это добавляет 7 дней во вторник, 22 декабря и возвращает вторник, 5 января 2016 года. По умолчанию функция РАБДЕНЬ исключает выходные дни (суббота и воскресенье). В этом случае мы также предоставили список из трех праздничных дней, все из которых относятся к расчетному диапазону дат, что означает, что выходные и праздничные дни будут исключены.

Чтобы вычесть выходные из даты (вместо добавления рабочих дней), просто используйте отрицательное значение для дней. Например, чтобы получить дату 3 рабочих дня до даты в A1, вы можете использовать:

Добавить рабочие дни без выходных

Чтобы добавить или вычесть дни рабочих дней к дате, соблюдая праздники, но при условии 7-дневной рабочей недели, вы можете использовать функцию РАБДЕНЬ.МЕЖД. В этом примере формула в D6:

Эта формула добавляет 7 дней работы во вторник, 22 декабря. Три отпуска предоставляются с использованием названного диапазона «праздники» (B9: B11), а выходные устанавливаются с использованием специального синтаксиса «0000000», что означает, что все дни недели являются рабочими днями. Результат: Вт, 31 декабря 2015 года.

РАБДЕНЬ.МЕЖД может вычислять дату в будущем или прошлом, что касается праздников и выходных. Чтобы указать, какие дни считаются выходными, вы можете использовать специальный код (полный список кодов здесь) или использовать «маску» для обозначения выходных дней с единицами и нулями. Метод маски является более гибким, поскольку он позволяет назначать любой день недели в выходные (т. е. Нерабочий день). Например:

Аргумент выходных дней предоставляется в виде 7 символов, представляющих понедельник-воскресенье. Используйте один (1), чтобы указать выходные, и ноль (0), чтобы указать рабочий день.

Поскольку мы хотим, чтобы все дни недели считались рабочими днями, мы используем «0000000».

Добавить дни и исключить определенные дни недели

Чтобы добавить дни в дату, исключая определенные дни (исключая вторники и четверги, только среду и т. д.), вы можете использовать функцию РАБДЕНЬ.МЕЖД со специальным кодом шаблона.

Эта формула добавляет 7 дней к дате в B7, за исключением суббот и воскресений.

Функция РАБДЕНЬ.МЕЖД основана на функции РАБДЕНЬ, которая предназначена для добавления рабочих дней к дате. РАБДЕНЬ автоматически исключает субботу и воскресенье, а также может исключать список пользовательских праздников. РАБДЕНЬ.МЕЖД делает то же самое, но позволяет исключить любые дни недели, в дополнение к праздникам.

Чтобы исключить определенные дни недели, вы можете использовать предварительно сконфигурированный код или предоставить свой собственный код шаблона. Код шаблона должен состоять из 7 цифр, либо иметь ноль для каждого дня недели, начинающийся в понедельник и заканчивающийся в воскресенье. Значения, равные 1, исключаются, а дни с нулевыми значениями обрабатываются обычным образом.

Итак, если вы хотите добавить 7 дней к дате в ячейке A1, вы можете написать такие формулы:

Добавить дни до даты

Чтобы добавить заданное количество лет к дате, вы можете использовать функцию ДАТАМЕС.

Даты в Excel — это только серийные номера. Число 1 представляет 1 января 1900 года, число 1000 — 26 сентября 1902 года и так далее.

Когда у вас есть действительная дата в Excel, вы просто добавляете дни непосредственно. Значения дня могут быть как положительными, так и отрицательными.

Например, с датой в A1 вы можете добавить 100 дней следующим образом:

В показанном примере формула решается следующим образом:

Когда отформатировано как дата, 36868 — 8 декабря 2000 года.

Добавить рабочие дни на персонализированные выходные дни

Чтобы добавить или вычесть дни рабочих дней для даты, чтобы можно было рассчитать дату в будущем или прошлом, которая пропускает выходные дни (настраиваемые) и праздники, можно использовать функцию РАБДЕНЬ.МЕЖД. Праздники и выходные дни являются необязательными.

В этом примере формула в D5:

Эта формула добавляет 7 дней работы во вторник, 22 декабря. Три отпуска предоставляются, а выходные устанавливаются с использованием 11 аргументов в выходные, что означает «только воскресенье».

По умолчанию функция РАБДЕНЬ.МЕЖД исключает выходные дни (суббота и воскресенье). В этом случае, однако, мы поставили 11 для этого аргумента, что означает «только воскресенье».

Мы также предоставили список из 3 праздников, которые все выпадают в диапазоне дат, который рассчитывается, что означает, что праздники и воскресенья будут исключены.

Особенности использования функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД в Excel

Функция ЧИСТРАБДНИ имеет следующий синтаксис:

=ЧИСТРАБДНИ( нач_дата;кон_дата; )

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

Функция ЧИСТРАБДНИ.МЕЖД имеет следующий синтаксис:

=ЧИСТРАБДНИ.МЕЖД( нач_дата;кон_дата;

нач_дата и кон_дата – обязательные аргументы, смысл которых тождественен одноименным аргументам функции ЧИСТРАБДНИ;
– необязательный аргумент, который может быть указан в виде текстовой строки или числового значения из диапазона от 1 до 7 и от 11 до 17 соответственно, характеризующих число выходных и их позиции в неделе. По умолчанию принимается значение 1, соответствующее выходным в субботу и воскресенье. Если указано число 2 – выходными считаются воскресенье и понедельник. Далее по аналогии вплоть до числа 7, соответствующего выходным дням в пятницу и субботу. Число 11 характеризует единственный выходной день в воскресенье, 12 – в понедельник и далее по аналогии вплоть до 17, характеризующего субботу в качестве единственного выходного дня. Текстовый вариант представления аргумента имеет вид «0000011», где 1 – выходной день, 0 – рабочий день. Если строка состоит из 7 единиц («1111111»), функция ЧИСТАРАБНИ.МЕЖД вернет значение 0;
– необязательный аргумент в виде ссылки на ячейку или диапазон ячеек, содержащих данные в формате даты, указывающие даты праздничных дней, либо массив числовых значений, являющихся представлениями дат в коде времени Excel.

Аргументы нач_дата и кон_дата обеих рассматриваемых функций должны быть указаны в виде результатов выполнения функции ДАТА или прочих функций, возвращающих данные в формате даты. Представление дат в виде текстовых строк может привести к непредсказуемым результатам.
Если в качестве аргумента нач_дата была передана более поздняя дата чем та, которая передана в качестве аргумента кон_дата, результатом выполнения функций ЧИСТРАБДНИ и ЧИСТАРБДНИ.МЕЖДУ будет отрицательное число, модуль которого равен количеству рабочих дней между указанными датами.
Любые аргументы рассматриваемых функций, принимающие даты вне предела допустимых значений в Excel, будут приводить к возникновению ошибок #ЧИСЛО!
Если в качестве аргумента функции ЧИСТРАБДНИ.МЕЖД передано число, которое не принадлежит диапазонам допустимых значений, или строка, содержащая недопустимые символы (любые символы, кроме «0» и «1»), большее или меньшее количество символов, чем число дней в неделе, результатом выполнения будут коды ошибок #ЧИСЛО! или #ЗНАЧ!.

Источник статьи: http://exceltable.com/funkcii-excel/chistye-rabochie-dni

Как автоматически заполнять даты в Excel

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

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

Но возможности автозаполнения на этом не заканчиваются. Вы также можете делать это в будние дни, месяцы или годы. Есть два способа сделать это.

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

Вы также можете автоматически вставлять каждые N дней. Для этого вам нужно добавить значение в ячейку, щелкнуть правой кнопкой мыши маркер автозаполнения, удерживать его и перетащить туда, где должна заканчиваться числовая последовательность. Затем выберите параметр заполнения «Progression» и выберите значение шага.

Excel NETWORKDAYS Функция

Microsoft Excel ЧИСТРАБДНИ (функция ЧИСТРАБДНИ) вычисляет количество рабочих дней между двумя датами (датой начала и датой окончания) с автоматическим исключением выходных по умолчанию. Кроме того, указывать праздники, которые нужно исключить из рабочих дней, необязательно.

Внимание: Функция ЧИСТРАБДНИ включает дату начала и дату окончания при расчете рабочих дней

аргументы

Start_date (обязательно): Это начальная дата диапазона дат.

End_date (обязательно): Это дата окончания диапазона дат.

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

Диапазон ячеек, содержащий даты.
Массив серийных номеров, представляющих даты.

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

=NETWORKDAYS( B3, C3,F3:F4 )

Или введите даты напрямую в виде текстовой строки с заключенными в кавычки:

=NETWORKDAYS( «12/20/2018», «1/10/2019»,{«12/25/2018″,»1/1/2019»})

Численная величина

Функция ЧИСТРАБДНИ вернет число, представляющее количество рабочих дней между двумя датами.

Примечания к функциям

1. Предположим, что у ячейки нет определенного формата, при вводе в нее даты дата будет сохранена как последовательный порядковый номер. Ссылки на ячейки с такими серийными номерами также можно использовать при вычислении функции ЧИСТРАБДНИ.(По умолчанию серийный номер 1 представляет 1 января 1900 г., а серийный номер 40909 представляет 1 января 2012 г., потому что это 40,909 1 дней после 1900 января XNUMX г.)

2. #VALUE! Значение ошибки будет возвращено, если любой из аргументов содержит недопустимую дату.

3. Функция ЧИСТРАБДНИ игнорирует любые значения времени при подсчете рабочих дней.

Примеры

Этот раздел покажет вам примеры того, как рассчитать рабочие дни между двумя заданными днями в Excel.

Пример 1. Расчет рабочих дней между двумя датами, исключая выходные по умолчанию.

Как показано на скриншоте ниже, даты начала находятся в диапазоне B3: B5, а даты окончания — в диапазоне C3: C5. Для расчета только рабочих дней между датами начала и окончания и автоматического исключения выходных, сделайте следующее.

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

=NETWORKDAYS( B3, C3 )

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

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

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

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

=NETWORKDAYS( B3, C3,F3:F4 )

Здесь F3: F4 — это список праздников, которые вы исключите из рабочих дней.

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

В ситуациях, когда вам нужно получить число рабочих дней в каком-то временном промежутке, используйте формулу ЧИСТРАБДНИ() (в английском варианте – NETWORKDAYS) :

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

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

При желании вы можете ввести свой список праздников в отдельный диапазон и дополнить формулу, чтобы исключить эти дни:

В результате учитываются только будние дни.

А если у нас свой особенный график труда и отдыха?

Если вам нужно обрабатывать какие-то особенные нерабочие дни (например, отдыхаем только в воскресенье, в понедельник и воскресенье и т.д), используйте функцию ЧИСТРАБДНИ.МЕЖД, которая позволяет указать, какие дни недели предназначены для отдыха.

ЧИСТРАБДНИ.МЕЖД (WORKDAY.INTL в английском варианте) — более мощная модификация функции ЧИСТРАБДНИ, которая работает с пользовательскими параметрами выходных . Как и его предшественница, она возвращает число рабочих дней в будущем или в прошлом, но позволяет вам определить, какие именно дни недели в вашем графике нужно считать нерабочими.

Формула ЧИСТРАБДНИ.МЕЖД появилась впервые в Excel 2010, и поэтому недоступна в более ранних версиях программы.

Синтаксис ее выглядит следующим образом:

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

Дата_начала — начальная точка.

Дни — будние дней до (отрицательное значение) или после (положительное значение) точки начала. Если этот аргумент в результате каких-то расчетов предоставляется в виде десятичного числа, то он усекается до целого.

Последние два аргумента являются необязательными:

Выходные — указывает, в какие дни недели вы не работаете. Это может быть либо число, либо текст, как показано ниже.

Число	Выходные дни
1 или отсутствует	Суббота Воскресенье
2	Воскресенье понедельник
3	Понедельник вторник
4	Вторник Среда
5	Среда Четверг
6	Четверг Пятница
7	Пятница Суббота
11	Только воскресенье
12	Только понедельник
13	Только вторник
14	Только среда
15	Только четверг
16	Только в пятницу
17	Только суббота

Текст для указания выходного дня — серия цифр из семи нулей и единиц, представляющих семь дней недели, начиная с понедельника. 1 означает день отдыха, а 0 — будний день. Например:

«0000011» — суббота и воскресенье – не работаем.
«1000001» — понедельник и воскресенье — отдыхаем.

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

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

А теперь несколько примеров.

=ЧИСТРАБДНИ.МЕЖД(A10;B10;1) – это знакомый нам вариант, когда нерабочими считаются суббота и воскресенье.

А вот =ЧИСТРАБДНИ(A10;B10;11) – отдых только в воскресенье,

=ЧИСТРАБДНИ.МЕЖД(A10;B10;2) – в воскресенье и понедельник. Такой расчет полезен, если в вашей организации свой график работы, не такой, как у всех.

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

Вы можете объединить полученное число со своим собственным сообщением, склеив их при помощи оператора &.

Например, давайте посмотрим, сколько нужно еще отработать до конца 2020 года. Для этого введем 31 декабря 2020 года в формате даты, и используем следующее выражение:

Ух, осталось всего 203 дня! Не так много, как я думал :)Примечание. Чтобы учесть свой индивидуальный график работы, используйте ЧИСТРАБДНИ.МЕЖД().

Ведение табеля учета рабочего времени в excel

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

Рабочий день отмечается цифрой от 1 до 24, которая обозначает количество часов, отработанных сотрудником за день. Отмеченный день автоматически подкрашивается светло-синим цветом с помощью правил условного форматирования.
День отпуска отмечается сокращением оп. Автоматически подкрашивается светло-розовым цветом.
Праздничный день отмечается сокращением пр . Автоматически подкрашивается салатовым цветом.
Прогул отмечается обозначением пг. Автоматически подкрашивается серым цветом.
Отгул обозначается сокращением ог. Автоматически подкрашивается бледно-оранжевым цветом.
Отпуск без содержания обозначается сокращением обс. Автоматически подкрашивается сиреневым цветом.
Учебный отпуск обозначается буквой у. Обозначение подкрашивается желтым цветом.
Командировка обозначается буквой к. Подкрашивается зеленым цветом.
Больничный отмечается маленькой буквой б. Автоматически подкрашивается светло-желтым цветом.

Пример заполненного табеля учета рабочего времени в excel можно увидеть на Рисунке 3 ниже:

Формульный блок расчетных показателей (столбцы «AG:AJ») добавлен для удобства подведения месячных итогов. Данный блок состоит из четырех столбцов:

Рабочих дней – количество дней, отработанных сотрудником за отчетный месяц (количество синих ячеек).
Рабочих часов – количество часов, отработанных сотрудником в отчетном месяце (сумма значений синих ячеек).
Дней отпуска – количество дней отпуска, использованных сотрудником за отчетный месяц (количество ячеек с обозначением оп).
Дней больнич.– количество дней, которые сотрудник провел на больничном (количество ячеек с обозначением б).
Перераб. часов — кол-о часов переработки. Считает часы, которые превышают норму дневной выработки, указанной в столбце «раб. день (ч.)»
Недораб. часов — кол-о часов, которых не достает до нормы дневной выработки. Пример расчета: норма 8ч, стоит 6ч, недоработка — 2ч; норма 8ч, стоит 12ч, переработка — 4ч; норма 8ч, стоит 1й день 6ч, 2й день 10ч, переработка — 0ч, недоработка — 0ч.
Отгулов — количество дней,в которые сотрудник брал отгулы (количество ячеек ог ).
Празд. дней — кол-о праздничных дней в месяце. Праздничные дни отмечаются сокращением пр.
Прогулов дней — кол-о прогулов, отмечается сокращением пг.
Отп. без содержан. — кол-о дней, которые сотрудник провел в отпуске без содержания. Обозначение — обс.
Учебного отпуска — кол-о дней учебного отпуска, обозначение у.
Дней командир. — кол-о дней, которые сотрудник провел в командировке. Обозначение к.

Складывать и вычитать календарные дни

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

Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».

Как выглядит формат ячейки в Excel

Таким же образом можно посчитать и количество дней между двумя датами. Просто вычтите из более поздней даты более раннюю. Результат Excel покажет в виде числа, поэтому ячейку с итогом переведите в общий формат: вместо «Дата» выберите «Общий».

К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.