Подсчет рабочих дней между датами в Excel
Обновлено: 21.11.2024
В управлении проектами полезно знать, сколько рабочих дней проходит между двумя датами. У вас могут быть даты начала и окончания проекта, и вы хотели бы знать, сколько рабочих дней приходится на эти две даты. Основная причина получения этой информации, вероятно, заключается в том, что менеджеры проектов хотят получить представление о том, сколько дней требуется для выполнения работы. Это полезно, чтобы позволить им лучше оценить будущие проекты. Так как же нам рассчитать рабочие дни между двумя датами? А как же праздники? Как мы можем объяснить и их?
Эта статья поможет вам? Если да, поддержите меня чашечкой кофе ☕️
ЧИСТРАБДНИ() Функция Excel
В Excel есть функция ЧИСТРАБДНИ(), которая может рассчитать количество рабочих дней между двумя датами. Вы также можете указать этой функции, какие праздники вы хотите исключить.
Вот синтаксис:
| Параметр | Описание |
|---|---|
| start_date | Дата начала для использования в функции. |
| end_date | Конечная дата для использования в функции. |
| праздники | Необязательно. Это либо диапазон, содержащий даты праздников, либо константа массива порядковых номеров. |
ЧИСТРАБДНИ() Пример
Допустим, у нас есть следующие данные:
Таблица Excel слева называется «ProjectDates», а таблица Excel справа — «Праздники». Мы также собираемся использовать структурированные ссылки для этих формул, чтобы сделать их проще.
Начнем с суммирования общего количества дней. Чтобы получить это, мы можем сделать простую формулу (в столбце C):
Затем отформатируйте данные как число, например так:
Довольно просто. Первый проект рассчитан на 29 дней, а второй — на 7 дней.
Кроме того, если вам интересно, почему формула дает даты 29.01.1900 и 07.01.1900 , нажмите здесь.
Теперь давайте получим рабочие дни с помощью функции ЧИСТРАБДНИ(). Мы также собираемся включить список Праздники, чтобы исключить праздники.
Вот формула, которую мы будем использовать в столбце D:
=ЧИСТРАБДНИ([@[Дата начала]],[@[Дата окончания]],Праздники[Дата])
Подождите, подождите... почему в строке 3 указано, что количество рабочих дней равно 6, а не 5?
Функция Excel ЧИСТРАБДНИ() использует дату начала и дату окончания включительно при подсчете общего количества рабочих дней. Таким образом, для строки 3 дата окончания 08.03.2017 также считается рабочим днем.
Если вы хотите это изменить, просто вычтите 1 из общей суммы:
Вау, вы прочитали всю статью! Вы знаете, люди, которые заходят так далеко, являются настоящими учениками. И очевидно, вы цените обучение. Хотите узнать больше об Excel? Пожалуйста, поддержите меня, купив мне кофе (чтобы написать эти статьи, нужно много кофе!).
Например, вам может понадобиться узнать, сколько рабочих дней в ноябре 2015 г. Этот расчет должен исключать субботу, воскресенье и праздничные дни.
Функция ЧИСТРАБДНИ вычисляет разницу между двумя датами, исключая выходные дни (субботу и воскресенье).
Как вариант, вы можете указать диапазон ячеек, содержащих даты праздников, которые также исключаются. В Excel абсолютно невозможно определить, какие дни являются выходными, поэтому вы должны указать эту информацию в диапазоне.
Расчет количества рабочих дней между двумя датами для необычных смен
При расчете разницы между двумя датами можно исключить выходные и праздничные дни. Вы можете увидеть, как рассчитать количество Рабочих дней между двумя датами для обычной стандартной рабочей недели, где выходные — суббота и воскресенье.
Как рассчитать количество рабочих дней при четырехдневной рабочей неделе
При расчете разницы между двумя датами можно исключить выходные и праздничные дни.Excel предлагает стандартную формулу для расчета количества рабочих дней при стандартной пятидневной рабочей неделе. Также Excel предлагает формулу для расчета количества рабочих дней для разных пятидневных рабочих недель или шестидневных рабочих недель.
Вычисление количества дней между двумя датами
Если вам нужно подсчитать количество дат между двумя датами в Excel, вам не нужно использовать РАЗНДАТ и другие функции Excel для работы с датами. Дата в Excel — это не отдельный тип, а число с правильным форматированием. Целая часть этого числа представляет дни с фиксированного момента для всех дат. Вычтите одну дату из другой, и вы получите количество дней между двумя датами. Кроме того, вы можете прибавить или вычесть целое число из даты и получить дату в будущем или в прошлом.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для 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.
Описание
Возвращает количество полных рабочих дней между start_date и end_date. Рабочие дни не включают выходные и любые даты, указанные в праздничных днях. Используйте ЧИСТРАБДНИ, чтобы рассчитать выплаты сотрудникам, которые начисляются на основе количества отработанных дней в течение определенного срока.
Совет. Чтобы рассчитать полные рабочие дни между двумя датами с помощью параметров, указывающих, какие и сколько дней являются выходными, используйте функцию ЧИСТРАБДНИ.МЕЖД.
Синтаксис
ЧИСТЫЕРАБДНИ(дата_начала, дата_окончания, [праздники])
Синтаксис функции ЧИСТРАБДНИ имеет следующие аргументы:
Обязательный параметр Start_date. Дата, представляющая дату начала.
End_date Обязательно. Дата, представляющая дату окончания.
Праздники Необязательно. Необязательный диапазон из одной или нескольких дат для исключения из рабочего календаря, например государственные и федеральные праздники и плавающие праздники. Список может быть либо диапазоном ячеек, содержащих даты, либо константой массива порядковых номеров, представляющих даты.
Важно! Даты следует вводить с помощью функции ДАТА или в качестве результатов других формул или функций. Например, используйте DATE(2012,5,23) для 23 мая 2012 года. Если даты вводятся в виде текста, могут возникнуть проблемы.
Примечания
Microsoft Excel хранит даты в виде последовательных порядковых номеров, чтобы их можно было использовать в вычислениях. По умолчанию 1 января 1900 года – это порядковый номер 1, а 1 января 2012 года – порядковый номер 40 909, поскольку с 1 января 1900 года прошло 40 909 дней.
Пример
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.
Используйте функцию DATEDIF, если вы хотите вычислить разницу между двумя датами. Сначала укажите дату начала в одной ячейке и дату окончания в другой. Затем введите формулу, подобную одной из следующих.
Разница в днях
В этом примере дата начала указана в ячейке D9, а дата окончания — в ячейке E9. Формула находится в F9. «d» возвращает количество полных дней между двумя датами.
Разница в неделях
В этом примере дата начала указана в ячейке D13, а дата окончания — в ячейке E13. «d» возвращает количество дней. Но обратите внимание на /7 в конце. Это делит количество дней на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также необходимо отформатировать как число. Нажмите CTRL + 1. Затем нажмите Число > Десятичные разряды: 2.
Разница в месяцах
В этом примере дата начала указана в ячейке D5, а дата окончания — в ячейке E5. В формуле «m» возвращает количество полных месяцев между двумя днями.
Разница в годах
В этом примере дата начала находится в ячейке D2, а дата окончания — в ячейке E2. «y» возвращает количество полных лет между двумя днями.
Рассчитать возраст в накопленных годах, месяцах и днях
Вы также можете рассчитать возраст или стаж работы. Результатом может быть что-то вроде «2 года, 4 месяца, 5 дней».
1. Используйте DATEDIF, чтобы найти общее количество лет.
В этом примере дата начала указана в ячейке D17, а дата окончания — в ячейке E17. В формуле «y» возвращает количество полных лет между двумя днями.
2. Используйте DATEDIF снова с «ym», чтобы найти месяцы.
В другой ячейке используйте формулу DATEDIF с параметром "ym". «Гм» возвращает количество оставшихся месяцев после последнего полного года.
3. Используйте другую формулу, чтобы найти дни.
Теперь нам нужно найти количество оставшихся дней. Мы сделаем это, написав другую формулу, показанную выше. Эта формула вычитает первый день последнего месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: Сначала функция ДАТА создает дату 01.05.2016. Он создается с использованием года в ячейке E17 и месяца в ячейке E17. Тогда 1 представляет первый день этого месяца. Результат функции ДАТА — 01.05.2016. Затем мы вычитаем это из исходной даты окончания в ячейке E17, то есть 06.05.2016. 06.05.2016 минус 01.05.2016 равно 5 дням.
Предупреждение. Мы не рекомендуем использовать аргумент DATEDIF "md", так как это может привести к неточным результатам.
4. Необязательно: объедините три формулы в одну.
Вы можете поместить все три вычисления в одну ячейку, как в этом примере. Используйте амперсанд, кавычки и текст. Это более длинная формула для ввода, но, по крайней мере, все в одном. Совет: нажмите клавиши ALT+ВВОД, чтобы вставить в формулу разрывы строк. Это облегчает чтение. Кроме того, нажмите CTRL+SHIFT+U, если вы не видите всю формулу.
Скачать наши примеры
Вы можете загрузить пример книги со всеми примерами из этой статьи. Вы можете следовать инструкциям или создавать свои собственные формулы.
Другие расчеты даты и времени
Как вы видели выше, функция РАЗНДАТ вычисляет разницу между датой начала и датой окончания. Однако вместо того, чтобы вводить конкретные даты, вы также можете использовать функцию СЕГОДНЯ() внутри формулы. Когда вы используете функцию СЕГОДНЯ(), Excel использует в качестве даты текущую дату вашего компьютера. Имейте в виду, что это изменится, когда файл снова откроется в будущем.
Обратите внимание, что на момент написания этой статьи это было 6 октября 2016 года.
Используйте функцию ЧИСТРАБДНИ.МЕЖД, если вы хотите рассчитать количество рабочих дней между двумя датами. Вы также можете исключить выходные и праздничные дни.
Прежде чем начать: решите, хотите ли вы исключить праздничные дни. Если вы это сделаете, введите список дат праздников в отдельной области или листе. Поместите каждую дату праздника в отдельную ячейку. Затем выберите эти ячейки, выберите «Формулы» > «Определить имя». Назовите диапазон MyHolidays и нажмите OK. Затем создайте формулу, выполнив указанные ниже действия.
1. Введите дату начала и дату окончания.
В этом примере дата начала указана в ячейке D53, а дата окончания — в ячейке E53.
2. В другой ячейке введите следующую формулу:
Введите формулу, как в приведенном выше примере. 1 в формуле определяет субботу и воскресенье как выходные дни и исключает их из общего числа.
Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример в Excel 2007 выглядел бы так: =ЧИСТРАБДНИ(D53,E53). Вы не указываете 1, потому что ЧИСТРАБДНИ предполагают, что выходные приходятся на субботу и воскресенье.
3. При необходимости измените 1.
Если суббота и воскресенье не являются вашими выходными днями, измените 1 на другое число из списка IntelliSense. Например, число 2 делает воскресенье и понедельник выходными днями.
Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ Excel 2007 всегда предполагает, что выходные приходятся на субботу и воскресенье.
4. Введите название диапазона праздников.
Если вы создали название диапазона праздников в разделе "Перед началом работы" выше, введите его в конце, как показано ниже. Если у вас нет праздников, вы можете не указывать запятую и MyHolidays. Если вы используете Excel 2007, приведенный выше пример будет таким: =ЧИСТРАБДНИ(D53,E53,MyHolidays).
Совет. Если вы не хотите ссылаться на название диапазона праздников, вы также можете вместо этого ввести диапазон, например D35:E:39. Или вы можете ввести каждый праздник внутри формулы. Например, если ваши праздники приходятся на 1 и 2 января 2016 года, введите их так: =NETWORKDAYS.INTL(D53,E53,1,). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53,E53,)
Вы можете рассчитать прошедшее время, вычитая одно время из другого. Сначала укажите время начала в одной ячейке и время окончания в другой. Обязательно введите полное время, включая часы, минуты, и пробел перед AM или PM. Вот как:
1. Введите время начала и время окончания.
В этом примере время начала указано в ячейке D80, а время окончания — в ячейке E80. Обязательно введите часы, минуты и с пробелом перед AM или PM.
2. Установите формат ч:мм AM/PM.
Выберите обе даты и нажмите CTRL + 1 (или + 1 на Mac). Обязательно выберите «Пользовательский» > «ч:мм AM/PM», если он еще не установлен.
3. Вычтите два раза.
В другой ячейке вычтите ячейку времени начала из ячейки времени окончания.
4. Установите формат ч:мм.
Нажмите CTRL + 1 (или + 1 на Mac). Выберите «Пользовательский» > «ч:мм», чтобы результат не включал AM и PM.
Чтобы вычислить время между двумя датами и временем, вы можете просто вычесть одно из другого. Однако вы должны применить форматирование к каждой ячейке, чтобы Excel вернул нужный результат.
1. Введите две полные даты и время.
В одной ячейке введите полную дату/время начала. А в другой ячейке введите полную дату/время окончания. В каждой ячейке должны быть указаны месяц, день, год, час, минута, и пробел перед AM или PM.
2. Установите формат 14.03.12 13:30.
Выделите обе ячейки и нажмите CTRL + 1 (или + 1 на Mac). Затем выберите Дата > 14.03.12 13:30. Это не дата, которую вы установите, это просто пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другую примерную дату, например 14 марта 2001 г., 13:30.
3. Вычтите два.
В другой ячейке вычтите дату/время начала из даты/времени окончания. Результат, вероятно, будет выглядеть как число и десятичная дробь. Вы исправите это на следующем шаге.
4. Установите формат [ч]:мм.
Нажмите CTRL + 1 (или + 1 на Mac). Выберите Пользовательский. В поле Тип введите [ч]:мм.
Читайте также: