Подсчет рабочих дней между датами в excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ЧИСТРАБДНИ в Microsoft Excel.
Описание
Возвращает количество рабочих дней между датами "нач_дата" и "кон_дата". Праздники и выходные в это число не включаются. Функцию ЧИСТРАБДНИ можно использовать для вычисления заработной платы работника на основе количества дней, отработанных в указанный период.
Совет: Чтобы вычислить количество полных рабочих дней между двумя датами с использованием параметров, определяющих количество выходных в неделе и то, какие это дни, используйте функцию ЧИСТРАБДНИ.МЕЖД.
Синтаксис
Аргументы функции ЧИСТРАБДНИ описаны ниже.
нач_дата — обязательный аргумент. Начальная дата.
кон_дата Обязательный. Конечная дата.
Праздники Необязательный. Список из одной или нескольких дат, которые требуется исключить из рабочего календаря, например государственные праздники. Список может представлять собой диапазон ячеек, содержащих даты, или константу массива, содержащую числа, которые представляют даты.
Важно: Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2012 г. воспользуйтесь выражением ДАТА(2012,5,23). Если ввести даты как текст, это может привести к возникновению проблем.
Замечания
В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. имеет порядковый номер 1, а 1 января 2012 г. — 40909, так как от 1 января 1900 г. он отс0 до 40 909 дней.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция ЧИСТРАБДНИ в Excel используется для расчета числа дней между датами начала и окончания какого-либо события за исключением выходных и праздничных, и возвращает соответственное числовое значение.
Функция ЧИСТРАБДНИ.МЕЖД в Excel выполняет расчет количества дней между указанными датами начала и окончания некоторого события за вычетом выходных и праздничных и возвращает соответствующее числовое значение. В отличие от функции ЧИСТРАБДНИ, имеет синтаксис, позволяющий указать число рабочих дней в неделе и их позиции.
Примеры использования функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД в Excel
Пример 1. Определить количество выходных между датами 20.09.2018 и 30.10.2018 включая праздничные дни, которые приходятся на следующие даты: 28.09.2018 и 05.10.2018.
Вид исходной таблицы данных:
Для расчета используем следующую формулу:
- (B3-B2) – общее число дней между указанными датами;
- B2 – дата начала некоторого события;
- B3 – дата окончания некоторого события;
- B4:B5 – диапазон ячеек, содержащих праздничные дни.
- Данные в ячейках B2:B5 указаны в виде результатов выполнения функций ДАТА.
- Ячейка B6 должна иметь числовой формат данных для отображения корректных результатов.
В результате расчетов получим:
Между указанными в условии датами всего 13 выходных с учетом 2-х праздников.
Расчет отпускных для увольняющегося сотрудника в Excel
Пример 2. Рассчитать отпускные для увольняющегося работника, который поступил на работу 10.05.2015 и был уволен 15.06.2018, за время работы ни разу не брал отпуск. Всего за весь период работы было выплачено 457240 рублей зарплаты. Праздничными днями являются следующие даты: 1-8 января, 23 февраля, 8 марта, 1 мая, 9 мая, 12 июня, 4 ноября.
Вид исходной таблицы:
Для расчета общего количества отпускных дней за период работы используем формулу:
Выражения «(ДАТА(2016;1;1)-B6)*24/365» и (B3-ДАТА(2018;1;1))*24/365 возвращают число дней отпуска с учетом, что из 365 дней в году работнику полагается 24 дня отпуска. Второй аргумент функции СУММ – «24*2» - число дней отпуска за 2016 и 2017 года.
В результате получим:
Определим сумму выпускных по формуле:
Выражение B21/ЧИСТРАБДНИ(B2;B3;B6:C6:C6:D19:E18) определяет среднюю з/п за рабочий день. Умножив это значение на количество дней отпуска за весь период получим общую сумму отпускных:
Начисление зарплаты сотрудникам по отработанным дням в Excel
Пример 3. Определить зарплату работника за июль 2018 года, если расчет оплаты производится по отработанным дням, при этом по уставу фирмы до 15 числа каждого месяца число выходных в неделе - 2 (суббота и воскресенье), а после 15 – один (воскресенье). Если 15-е число любого месяца является субботой, данный день выходным не является.
Вид исходной таблицы данных:
Для расчета используем следующую формулу:
Функция ЕСЛИ выполняет проверку, является ли 15-е число июля субботой. Число рабочих дней вычисляется как сумма числа рабочих дней в неделях, для которых количество выходных равно двум, и неделях с одним выходным днем. Если 15-е является субботой, то полное количество дней с двумя выходными рассчитывается как разница дат ДАТА(2018;7;15)-6 и начало месяца.
Количество рабочих дней до 15-го числа составило 10 дней (15.07.2018 – воскресенье), после 15-го – 14. Произведение 24 рабочих дней и оклада по дням (1320 рублей) равно 31680 рублей зарплаты за месяц.
Функция РАБДЕНЬ предназначена для определения даты начала или окончания какого-либо мероприятия относительно заданной начальной или конечной даты с учетом числа выходных и праздничных дней, и возвращает соответствующее значение в виде данных формата Дата.
Функция РАБДЕНЬ.МЕЖД предназначена для определения даты, равной сумме текущей даты и длительности мероприятия, и возвращает соответствующее числовое значение в коде времени Excel.
Примеры использования функций РАБДЕНЬ и РАБДЕНЬ.МЕЖД в Excel
Пример 1. Согласно расчетам, для выполнения проекта сотрудником фирмы потребуется 236 часов его беспрерывной работы. Дата начала проекта – 18.08.2018. Определить дату окончания работы над проектом. В ближайшие 30 дней праздников нет, выходные – суббота и воскресенье.
Вид исходной таблицы данных:
Для решения используем формулу:
- B5 – дата начал события (работы над проектом);
- B6/B8 – число рабочих дней, требуемых для реализации проекта, при условии, что рабочий день длится 8 ч.
Автоматически определилось число для сдачи реализованного проекта по ключ.
Как посчитать количество рабочих дней между датами
Чтобы посчитать количество рабочих или выходных дней между двумя датами в Excel следует использовать функцию ЧИСТРАБДНИ:
Как видите всего в невисокосном 2019-му 262 рабочих дней в году и 103 дня выходных (262+103=365).
Пример 2. Курс предмета в учебном заведении рассчитан на 46 занятий, которые проводятся в понедельник, среду и пятницу. Начало изучения предмета – 3.09.2018. Ближайшие праздники: 17.09.2018, 28.09.2018, 7.10.2018. Определить дату последнего занятия.
Вид исходной таблицы данных:
Формула для расчета:
Последнее занятие будет 24 декабря 2018 года.
Формула оптимального расчета количества рабочих дней между датами
Пример 3. Для выполнения работы в срок было предложено два варианта: увеличить часы в рабочих днях до 10 ч в день или ввести шестидневную рабочую неделю. Расчетная длительность выполнения работы – 127 ч. Выбрать оптимальный вариант, если дата начала проекта – 20.08.2018.
Вид таблицы данных:
Для решения используем следующую формулу:
РАБДЕНЬ.МЕЖД(B3;B2/C5;C6);"Вариант 2";"Вариант 1")' >
Функция ЕСЛИ выполняет проверку значений, возвращаемых функциями РАБДЕНЬ.МЕЖДУ и возвращает соответствующий результат. Чем больше дата, тем больше времени потребуется на реализацию проекта.
То есть, чтобы быстрее выполнить работу, лучше увеличить длительность рабочего дня до 10 часов.
Особенности использования функций РАБДЕНЬ и РАБДЕНЬ.МЕЖД в Excel
Функция РАБДЕНЬ имеет следующий синтаксис:
=РАБДЕНЬ( нач_дата;количество_дней; [праздники])
- нач_дата – обязательный аргумент, характеризующий значения формата Дата, которое является точкой отсчета (началом какого-либо события).
- количество_дней – обязательный аргумент, принимающий данные числового типа, которые характеризуют число рабочих дней, прошедших после начала какого-либо события или предшествующих ему. Считается, что данное количество не содержит дни, которые являются праздниками или выходными днями. Для обратного отсчета относительно даты, указанной в качестве аргумента нач_дата, данный аргумент следует оказать в виде отрицательного числа.
- [праздники] – необязательный аргумент, принимающий данные формата Дата, указывающие одну или несколько дат, соответствующих праздничным дням. В качестве данного аргумента может быть передан массив ячеек.
Функция РАБДЕНЬ.МЕЖД имеет следующий синтаксис:
=РАБДЕНЬ.МЕЖД( нач_дата;количество_дней; [выходной];[праздники])
- нач_дата – обязательный аргумент, характеризующий дату начала какого-либо события в виде данных формата Дата;
- количество_дней – обязательный аргумент в виде данных числового типа, характеризующих число дней, которые предшествуют началу какого-либо события (отрицательное число) либо обозначают какую-либо будущую дату (положительное число);
- [праздники] – необязательный аргумент, принимающий одно или несколько значений формата Дата, указывающих даты праздничных дней;
- [выходной] – необязательный аргумент в виде строки из 7 последовательно записанных значений из диапазона от 0 до 1, характеризующей количество выходных дней и их позицию по дням недели, или числового значения из диапазона от 1 до 17. Примеры некоторых вариантов записи данного аргумента:
- 1 (или явно не указан) – выходными днями являются суббота и воскресенье.
- 2 – воскресенье и понедельник.
- 3 – понедельник и вторник (и т. д).
- 11 – только понедельник.
- 12 – только вторник (и т. д.).
- «0000011» - суббота и воскресенье».
- «1000001» - воскресенье и понедельник (и т. д.).
Примечание: в отличие от функции РАБДЕНЬ, предусматривающей возможность указывать лишь даты праздничных дней, функция РАБДЕНЬ.МЕЖДУ позволяет определять число выходных в неделю и указывать, какими днями в неделе являются выходные (например, вместо привычных субботы и воскресенья можно указать вторник и среда).
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Возвращает количество рабочих дней между двумя датами с использованием параметров, определяющих, сколько в неделе выходных и какие дни являются выходными. Выходные и любые праздники не считаются рабочими днями.
Синтаксис
Аргументы функции ЧИСТРАБДНИ.МЕЖД описаны ниже.
Нач_дата и кон_дата Обязательный. Даты, разницу между которыми требуется вычислить. Начальная дата может предшествовать конечной дате, совпадать с ней или быть позже нее.
Выходной Необязательный. Указывает, какие дни недели являются выходными и не включаются в число рабочих дней между начальной и конечной датой. Значение может задаваться номером выходного дня или строкой, определяющей, какие дни являются выходными.
Номера обозначают следующие выходные дни:
Номер выходного дня
Выходные дни
Строковые значения дней недели включают семь знаков, каждый из которых обозначает день недели (начиная с понедельника). Значение 1 представляет нерабочие дни, а 0 — рабочие дни. В строке допустимо использовать только знаки 1 и 0. При значении 1111111 всегда возвращается 0.
Например, 0000011 означает, что выходными днями являются суббота и воскресенье.
Праздники Необязательный. Набор из одной или нескольких дат, которые необходимо исключить из календаря рабочих дней. Значение "праздники" должно быть диапазоном ячеек, содержащих даты, или константой массива, включающей порядковые значения, которые представляют даты. Порядок дат или значений может быть любым.
Замечания
Если начальная дата позже конечной даты, возвращаемое значение будет отрицательным, а его модуль будет равен числу полных рабочих дней.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Получаем 22 предстоящих рабочих дня. Вычитает 9 нерабочитающих выходных дней (5 суббот и 4 воскресенья) из общего числа дней между двумя датами. По умолчанию суббота и воскресенье считаются нерабочитами.
Получаем -21, т. е. 21 прошедший рабочий день.
Из 32 дней между 1 января 2006 и 1 февраля 2006 года вычитаем 10 нерабочих дней (4 пятницы, 4 субботы и 2 праздника) и получаем 22 предстоящих рабочих дня. Используем аргумент 7 в качестве выходных дней, которыми являются субботы и воскресенья. Также на этой период времени приходится два праздника.
Получаем 22 предстоящих рабочих дня. Такой же период времени, как и в приведенном выше примере, но с выходными днями по субботам и средам.
Используйте функцию РАЗДАТ, если требуется вычислить разницу между двумя датами. Сначала поместите дату начала в ячейку, а дату окончания в другую. Введите формулу, как по одной из следующих формул:
Разница в днях
В этом примере начальная дата находится в ячейке D9, а даты окончания — в ячейке E9. Формула находится в F9. "d" возвращает количество полных дней между двумя датами.
Разница в неделях
В этом примере начальная дата находится в ячейке D13, а даты окончания — в ячейке E13. "d" возвращает количество дней. Однако обратите внимание на /7 в конце. При этом количество дней делится на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также должен быть отформатирован как число. Нажмите CTRL+1. Затем щелкните Число>десятичных числах: 2.
Разница в месяцах
В этом примере начальная дата находится в ячейке D5, а даты окончания — в ячейке E5. В формуле "м" возвращает количество полных месяцев между двумя днями.
Разница в годах
В этом примере начальная дата находится в ячейке D2, а даты окончания — в ячейке E2. "y" возвращает количество полных лет между двумя днями.
Расчет возраста в накопленных годах, месяцах и днях
Вы также можете вычислить возраст или время службы другого человека. Результат может выглядеть так: "2 года, 4 месяца, 5 дней".
1. Чтобы найти общее количество лет, используйте dateDIF.
В этом примере дата начала находится в ячейке D17, а даты окончания — в ячейке E17. В формуле "y" возвращает количество полных лет между двумя днями.
2. Чтобы найти месяцы, снова используйте DATEDIF с "м".
В другой ячейке используйте формулу РАЗНОМЕР с параметром "м". "м" возвращает количество оставшихся месяцев за последний полный год.
3. Используйте другую формулу для поиска дней.
Теперь нужно найти количество оставшихся дней. Для этого мы напишем формулу другого типа, как показано выше. Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.
Предупреждение: Не рекомендуется использовать аргумент "мд" РАЗДАТ.
4. Необязательно: объединение трех формул в одну.
Все три вычисления можно поместить в одну ячейку, как в этом примере. Используйте амперанд, кавычка и текст. Это длинная формула, но по крайней мере она все в одной формуле. Совет. Нажмите ALT+ВВОД, чтобы ввести разрывы строк в формулу. Это упрощает чтение. Кроме того, если вы не видите всю формулу, нажмите CTRL+SHIFT+U.
Скачивание примеров
Вы можете скачать образец книги со всеми примерами из этой статьи. Вы можете следовать за ним или создать собственные формулы.
Другие вычисления даты и времени
Как было посчитано выше, функция РАЗДАТ.В.ДВ вычисляет разницу между датой начала и датой окончания. Однако вместо ввода определенных дат в формуле можно использовать функцию СЕГОДНЯ(). При использовании функции СЕГОДНЯ() Excel для даты используется текущая дата вашего компьютера. Имейте в виду, что эта переменная будет меняться при повторном открыть файле в будущем.
Обратите внимание, что на момент написания статьи день был 6 октября 2016 г.
Используйте NETWORKDAYS. Функция INTL, если вы хотите вычислить количество рабочих дней между двумя датами. Вы также можете исключить выходные дни и праздники.
Прежде чем начать: Решите, нужно ли исключить даты праздников. В этом случае введите список дат праздников в отдельной области или на отдельном листе. Поместите каждую праздниковую дату в свою ячейку. Затем выберем эти ячейки и выберем формулы > определить имя. Привяжем диапазон MyHolidaysи нажмите кнопку ОК. Затем создайте формулу с помощью действий ниже.
1. Введите даты начала и окончания.
В этом примере дата начала находится в ячейке D53, а даты окончания — в ячейке E53.
2. В другой ячейке введите формулу такого вида:
Введите формулу, как в примере выше. Число 1 в формуле устанавливает субботы и воскресенье в качестве выходных и исключает их из общего числа.
Примечание. Excel 2007 не имеет networkDAYS. Функция INTL. Однако в нем есть NETWORKDAYS. В этом примере в Excel 2007 г. будет выглядеть так: =NETWORKDAYS(D53;E53). Вы не указываете 1, так как в networkDAYS предполагается, что выходные — в субботу и воскресенье.
3. При необходимости измените 1.
Если суббота и воскресенье не являются выходными, измените число 1 в списке IntelliSense. Например, 2 дня по понедельникам и понедельникам являются выходными.
Если вы используете Excel 2007, пропустите этот шаг. Excel 2007 г. функция NETWORKDAYS всегда предполагает, что выходные дни — в субботу и воскресенье.
4. Введите название диапазона праздников.
Если вы создали имя диапазона праздников в разделе "Начало работы" выше, введите его в конце так: Если у вас нет праздников, оставьте запятую и запятую MyHolidays. Если вы используете Excel 2007, пример будет такой: =ДНЕЙМН(D53;E53;MyHolidays).
Совет: Если вы не хотите ссылаться на название диапазона праздников, можно также ввести диапазон, например D35:E:39. Можно также ввести в формулу каждый праздник. Например, если ваши праздники были 1 и 2 января 2016 г., введите их так: =СЕТЕВЫЕДНИ. INTL(D53;E53;1;). В Excel 2007 г. она будет выглядеть так: =ДНЕЙМН(D53;E53;)
Вы можете вычесть затращенное время, вычитая одно время из другого. Сначала поместите время начала в ячейку, а время окончания в другую. Обязательно введите полные часы, минуты и пробелы до am или PM. Ниже рассказывается, как это сделать.
1. Введите время начала и окончания.
В этом примере время начала находится в ячейке D80, а время окончания — в ячейке E80. Введите часы, минуты и пробелы до am или PM.
2. Задать формат ч:мм.AM/PM.
Выберите обе даты и нажмите CTRL+1 (или + 1 на Компьютере Mac). Выберите настраиваемый >ч:мм/PM, если он еще не установлен.
3. Вычитать два раза.
В другой ячейке вычитайте ячейку времени начала из ячейки времени окончания.
4. За установите формат ч:мм.
Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите Настраиваемые > ч:мм, чтобы исключить из результатов am и PM.
Чтобы вычислить время между двумя датами и временем, можно просто вычесть одну из другой. Однако необходимо применить форматирование к каждой ячейке, чтобы Excel возвращал нужный результат.
1. Введите две полные даты и время.
В одной ячейке введите полную дату и время начала. В другой ячейке введите дату и время окончания. В каждой ячейке до am или PM должен быть месяц, день, год, час, минута и пробел.
2. Задать формат 14.03.2012, 13:30.
Выберем обе ячейки и нажмите CTRL+1 (или + 1 на компьютере Mac). Затем выберите Дата >14.03.2012, 13:30. Это не та дата, которая будет заданной вами, а только пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другой пример даты, например14.03.2011 13:30.
3. Вычитать эти два.
4. За установите формат [ч]:мм.
Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите пункт (все форматы). В поле Тип введите [ч]:мм.
Читайте также: