Top-office11.ru

IT и мир ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Формула подсчета рабочих дней в excel

Считаем рабочие дни в Microsoft Excel

Функции подсчета рабочих дней, наверное, один из самых ценных подарков пользователям Excel от разработчиков. Вы представьте, сколько производственных задач можно спланировать с их помощью? Очень много! Только не все знают о существовании этих функций. Вот этот пробел мы сегодня и восполним.

Как определить дату через заданное количество рабочих дней

Стандартная задача: нам известна дата начала работ и отведенное на это количество рабочих дней. Нужно определить дату окончания работ. Как мы это сделаем? С помощью функции РАБДЕНЬ(Начальная дата;Количество дней;[Праздники]) . У этой функции три аргумента:

  1. Начальная дата – дата, от которой отсчитываем срок. Это обязательный аргумент
  2. Количество дней – сколько рабочих дней прибавить к начальной дате – обязательный аргумент
  3. Праздники – перечень праздничных дней, если они есть и их нужно учитывать. Это необязательный аргумент. Удобнее всего указывать его ссылкой на список праздников.

Вот пример использования этой функции:

Функция РАБДЕНЬ

Но что если у вас другой график выходных? Например, вы отдыхаете только в воскресенье? Используйте похожую функцию – РАБДЕНЬ.МЕЖД(Начальная дата;Количество дней; [Выходные];[Праздники]) . Здесь появился еще один необязательный параметр – Выходные. Он как раз и позволяет задать нужный график выходных. Когда вы вводите этот параметр, программа подсказывает что выбрать для правильной настройки выходных дней:

Функция РАБДЕНЬ.МЕЖД

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

Считаем сколько рабочих дней между датами

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

  • Начальная дата периода – обязательный аргумент
  • Конечная дата периода – обязательные аргумент
  • Праздники – необязательный аргумент – учитывает праздничные дни, не совпадающие с выходными. Удобнее всего использовать ссылку на праздники

Вот как эта функция применяется:

Функция ЧИСТРАБДНИ

Выходными днями по умолчанию считаются суббота и воскресенье. Если вам нужно выбрать другие выходные – используйте функцию ЧИСТРАБДНИ.МЕЖД(Начальная дата;Конечная дата;[Выходные];[Праздники]) . При вводе аргумента «Выходные», программа выведет подсказку со списком параметров. Выберите и запишите подходящий:

Функция ЧИСТРАБДНИ.МЕЖД

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

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

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

Добавить комментарий Отменить ответ

17 комментариев

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

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

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

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

Я отправил через форму обратной связи задачу с описанием примера, но файл не вложить — пишет недопустимый тип файла. Ни Exel, ни Rar, ни Zip не пропускает!!

Добрый день! Как в формуле указать конечную дату расчета? К примеру есть формула =ДАТА(ГОД(C14);МЕСЯЦ(C14)+1;ДЕНЬ(20)), но мне нужно чтобы формула применялась только до определенной даты.Спасибо.

Оксана, нужно использовать функцию условия ЕСЛИ(Условие ; Значение если истина; Значение если ложь) . Если, например, у Вас в ячейке А1 хранится та самая предельная дата. Все что меньше должно вычисляться по Вашей формуле, что больше — возвращать пустое значение, тогда формула будет выглядеть так: =ЕСЛИ(ДАТА(ГОД(C14);МЕСЯЦ(C14)+1;ДЕНЬ(20))

Читать еще:  Как вычислить рабочие дни в excel

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

Евгений, конечно можно. Используйте функцию РАБДНИ, аргументом «начальная дата» будет Ваша конечная дата, а количество дней укажите отрицательным числом!

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

Сергей, когда можно упростить — рекомендую упрощать. Например, сократить до «дн». Тогда формула будет выглядеть так: =A1&» дн.». В данном случае предполагается, что в ячейке А1 находится Ваше число.

Добрый день! Подскажите пожалуйста как можно рассчитать например третий рабочий день. К примеру задаём дату — 01.02.2018. Мне нужно третий рабочий день. Функция рабдень считает через 3 раб дня это 6 февраля. А мне необходимо чтобы получалось на третий т.е. 5 февраля. Спасибо

Наталья, здравствуйте. Функция РАБДЕНЬ считает дату, которая будет через 3 рабочих дня, а не на третий день. Поэтому, в формуле вычтите единицу из даты, или в параметре «число дней» указывайте не 3, а 2. Второй вариант, видимо, сработает лучше.

Добрый день,
Подскажите, как высчитать количество рабочих дней для списка сотрудников, если какие-то сотрудники пришли после начала месяца, а какие-то уволились до его окончания.
Заранее спасибо!

Юлия, здравствуйте. Сделайте в списке сотрудников две колонки с датами приема на работу и увольнения. Используйте функцию ЧИСТРАБДНИ, она описана в этой статье и может принимать для расчетов произвольные даты, не обязательно первый или последний день месяца. Если не получится — напишите, будем разбираться.

Хорошая статья, но я так и не понял, как считать перенесённые праздники?
Есть две даты. Надо проверить, что бы вторая дата была не меньше 5 рабочих дней.
Использовал РАБДЕНЬ() и список дат-прадников.
Но как перенести… не понятно.
Есть формулы используя массивы, но такая формула по столбцу не копируется.
Может кто- решил проблему за такое большое время?

Здравствуйте. Стандартного решения от разработчиков пока нет. Я готовлю статью о способах решения этой задачи, вскоре она появится на блоге. Дополнительно сообщу вам на e-mail

Как вычислить рабочие дни в Excel

Добрый день уважаемый читатель!

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

Для начала, вы должны знать, что Excel видит любую дату как обыкновенное числовое значение, которое начинается с цифры 1 и соответствует дате 1 января 1900 г. К примеру, 1 января 2018 г. будет равняться числу 43101 и разница между датами составит 43100 дней. А ввиду этого мы очень просто можем манипулировать датами, так же, как и числами, а это значительно делает проще нашу работу.

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

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

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

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

Синтаксис этой функции достаточно прост и понятен:

= ЧИСТРАБДНИ(_нач_дата_; _кон_дата_; _[праздники]_), где:

  • Начальная дата – является обязательным аргументом и указывается дата начала отсчёта;
  • Конечная дата – также обязательный аргумент и указывает дату окончания периода расчётов;
  • Праздники – не обязательный аргумент, являет собой список дат или ссылку на них, которые следует изъять из расчётов, например, государственные или религиозные праздники.

Простой пример:

У нас есть даты начала периода в диапазоне ячеек А2:А5 и даты конца периода – В2:В5. Используя нашу функцию, мы получаем нужный нам интервал в рабочих днях. Сложный пример:

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Расчет количества дней между датами в Эксель

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

Расчет разности дат

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

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

Читать еще:  Какой бесплатный конвертер видео лучше

  1. Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
  2. Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Формат ячеек“. Аналогичного результата можно добиться, используя комбинацию клавиш Ctrl+1.
  3. Перед нами окно настроек формата ячеек:
    • по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
    • в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
    • в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
    • нажимаем OK.
  4. Теперь все значения, вводимые в ячейки выделенной области будут восприниматься программой как данные в формате “Дата“.

Метод 1: использование формулы вычитания

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

  1. Вносим две даты, между которыми будем определять разницу в днях, в две разные ячейки отформатированного ранее диапазона.
  2. Теперь выделим ячейку, в которой планируем отобразить результат нашего вычисления.
    ВАЖНО: Количество дней обозначается простым целым числом, следовательно, ячейка для его вывода должна иметь отличный от “Дата” формат, а именно – “Общий“. В противном случае полученный результат будет отображаться в формате даты, что не позволит оценить корректность проведенных вычислений. Для определения текущего формата ячейки достаточно взглянуть на соответствующее поле, расположенное в разделе инструментов “Число” на ленте вкладки “Главная“.
  3. В нашем случае установлено значение “Дата“. Для его изменения вновь вызываем окно формата ячеек и по описанному выше алгоритму устанавливаем числовой формат “Общий“. Для сохранения изменений жмем ОК.
  4. Теперь можно переходить непосредственно к вычислениям:
    • в итоговую ячейку вводим знак “=“;
    • кликаем по ячейке с конечной датой (более поздней);
    • вводим с клавиатуры символ “” (знак минус);
    • кликаем по ячейке с начальной датой.
  5. Для получения результата жмем клавишу Enter. В итоге получаем целое число – количество дней между двумя датами.

Метод 2: функция РАЗНДАТ

Следующий способ – это определение разницы между двумя датами при помощи функции РАЗНДАТ.

Функция имеет следующий синтаксис: =РАЗНДАТ(начальная_дата;конечная_дата;единица).

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

  • d” — количество дней между датами;
  • m” — количество полных месяцев между датами;
  • y” — количество полных лет между датами;
  • YM” — разница между датами в месяцах;
  • MD” — разница между датами в днях, без учета месяцев и лет;
  • YD” — разница между датами в днях, без учета лет.

В нашем случае покажем пример использование аргумента “YD”, так как целью расчета является определение количества дней между датами в пределах года.

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

  1. Выбираем целевую ячейку, проверяем ее формат по способу, описанному выше.
  2. Описываемая функция отсутствует в Мастере функций, поэтому вводим ее в ячейку вручную, строго придерживаясь синтаксиса.
  3. Жмем Enter и проверяем полученный результат. Как мы видим, он аналогичен тому, который мы получили, используя простую формулу вычитания. Значит все сделано верно.

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

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

В данном методе используется функция ЧИСТРАБДНИ со следующим синтаксисом: =ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]).

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

Приступаем к вычислениям:

  1. Так как описываемая функция присутствует в списке Мастера функций, воспользуемся им для большей наглядности: встаем на ячейку, куда будем выводить результат и щелкаем по кнопке “Вставить функцию” (fx), расположенной слева от строки формул.
  2. В открывшемся окне Мастера функций:
    • выбираем категорию “Полный алфавитный перечень”;
    • находим и кликаем в списке операторов по строке “ЧИСТРАБДНИ”;
    • нажимаем ОК.
  3. Перед нами появится окно аргументов функции. Здесь можно ввести адреса ячеек вручную или, находясь в поле для заполнения значений аргумента, кликнуть по нужной ячейке на листе. Выбрав начальную и конечную дату, а также указав праздничный день (адрес ячейки), нажимаем ОК.
  4. Предположим, в этот раз мы заранее не задали формат итоговой ячейки как “Общий”. Тогда мы получим результат, отображающийся в виде даты.
  5. Следовательно, необходимо снова проверить формат по алгоритму, описанному выше и изменить его на “Общий”.

Заключение

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

Читать еще:  Копировать лист excel в другую книгу

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

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

К счастью, для решения подобных задач в Excel есть немало готовых инструментов, поэтому выполнять подобные расчеты вручную нет необходимости. Сейчас вы узнаете как это делается в современных версиях Excel, таких как Excel 2007, 2010, 2013 и 2016.

Подготовка ячеек для работы с датами

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

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

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

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

Подсчет количества дней с помощью простой формулы

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

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

После присвоения формата «Общий», в эту ячейку нужно ввести формулу, которая будет отнимать более раннюю дату от более поздней. Подобная формула вводится очень просто. Выделяем ячейку, в которой должен находится результат, вводим символ «=», потом адрес ячейки с более поздней датой, потом символ «-» и адрес ячейки с более ранней датой.

После ввода формулы нужно нажать на клавишу Enter, и вы получите результат.

Как видим, в данном случае количество дней между двумя датами составляет 41.

Подсчет количества дней между двумя датами

Кроме этого, в Excel есть специальная функция для подсчета количества дней между двумя датами. Данная функция называется «РАЗНДАТ» и имеет вот такой синтаксис:

При этом значение «Единица» — это обозначение возвращаемых данных.

ЕдиницаВозвращаемое значение
«Y»Количество полных лет в периоде.
«M»Количество полных месяцев в периоде.
«D»Количество дней в периоде.
«MD»Разница в днях между начальной и конечной датой. Месяцы и годы дат не учитываются.
«YM»Разница в месяцах между начальной и конечной датой. Дни и годы дат не учитываются.
«YD»Разница в днях между начальной и конечной датой. Годы дат не учитываются.

Для того чтобы воспользоваться данной функцией нужно выделить ячейку, в которой должен находиться результат, присвоить ей тип данных «Общий» и ввести формулу. В данном случае ввод формулы чуть сложнее, нужно ввести символ «=», потом название функции «РАЗНДАТ», а потом открыть круглые скобки и ввести адреса ячеек с датами и единицу возвращаемых значений.

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

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

Еще одна типичная задача – это подсчет количества рабочих дней между двумя датами. Для ее решения в Excel также есть специальная функция, которая называется «ЧИСТРАБНИ». Синтаксис данной функции следующий:

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

Для того чтобы воспользоваться данной функцией нужно выделить ячейку, в которой должен находиться результат, присвоить ей тип данных «Общий» и ввести формулу. Для ввода формулы нужно ввести символ «=», потом название функции «ЧИСТРАБДНИ», потом открыть круглые скобки и ввести адреса ячеек с датами.

После ввода формулы нужно нажать на клавишу Enter, и вы получите результат.

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

Ссылка на основную публикацию
Adblock
detector