Формула WEEKDAY в Excel для получения дня недели, выходных и рабочих дней
Если вы ищете функцию Excel для получения дня недели по дате, вы попали на нужную страницу. В этом учебном пособии вы узнаете, как использовать формулу ДЕНЬ НЕДЕЛИ в Excel для преобразования даты в название дня недели, фильтрации, выделения и подсчета выходных или рабочих дней и многого другого.
В Excel есть множество функций для работы с днями. Функция дня недели (WEEKDAY) особенно полезна для планирования и планирования, например, для определения временных рамок проекта и автоматического исключения выходных из общего числа. Итак, давайте рассмотрим примеры по одному и посмотрим, как они могут помочь вам справиться с различными задачами, связанными с датами, в Excel.
WEEKDAY – функция Excel для дня недели
Функция Excel WEEKDAY используется для возврата дня недели из заданной даты.
Результатом является целое число в диапазоне от 1 (воскресенье) до 7 (суббота) по умолчанию. Если ваша бизнес-логика требует другого перечисления, вы можете настроить формулу для начала подсчета с любого другого дня недели.
Функция ДЕНЬНЕД доступна во всех версиях Excel 365 до 2000.
Синтаксис функции ДЕНЬНЕД следующий:
ДЕНЬ НЕДЕЛИ(серийный_номер, [return_type])
Где:
Serial_number (обязательно) — дата, которую вы хотите преобразовать в номер дня недели. Его можно указать в виде порядкового номера, представляющего дату, в виде текстовой строки в формате, понятном Excel, в виде ссылки на ячейку, содержащую дату, или с помощью функции ДАТА.
Return_type (необязательно) — определяет, какой день недели использовать в качестве первого дня. Если этот параметр опущен, по умолчанию используется неделя с воскресенья по субботу.
Вот список всех поддерживаемых значений return_type:
Return_type Возвращенное число 1 или опущено От 1 (воскресенье) до 7 (суббота) 2 От 1 (понедельник) до 7 (воскресенье) 3 От 0 (понедельник) до 6 (воскресенье) 11 От 1 (понедельник) до 7 (воскресенье) 12 С 1 (вторник) до 7 (понедельник) 13 С 1 (среда) до 7 (вторник) 14 С 1 (четверг) до 7 (среда) 15 С 1 (пятница) до 7 (четверг) 16 С 1 (суббота) до 7 (пятница) 17 С 1 (воскресенье) до 7 (суббота)
Примечание. Значения return_type с 11 по 17 были введены в Excel 2010, поэтому их нельзя использовать в более ранних версиях.
Базовая формула ДЕНЬ НЕДЕЛИ в Excel
Для начала давайте посмотрим, как использовать формулу WEEKDAY в ее простейшей форме, чтобы получить номер дня из даты.
Например, чтобы получить день недели из даты в C4 с неделей по умолчанию с воскресенья по субботу, формула будет следующей:
=ДЕНЬ НЕДЕЛИ(C4)
Если у вас есть порядковый номер, представляющий дату (например, полученный функцией ДАТАЗНАЧ), вы можете ввести этот номер непосредственно в формулу:
=ДЕНЬ НЕДЕЛИ(45658)
Кроме того, вы можете ввести дату в виде текстовой строки, заключенной в кавычки, непосредственно в формуле. Просто обязательно используйте формат даты, который Excel ожидает и может интерпретировать:
=ДЕНЬНЕД(“1/1/2025”)
Или укажите исходную дату со 100% надежностью, используя функцию DATE:
=ДЕНЬНЕД(ДАТА(2025, 1,1))
Чтобы использовать сопоставление дня, отличное от значения по умолчанию Sun-Sat, введите соответствующее число во втором аргументе. Например, чтобы начать отсчет дней с понедельника, используйте следующую формулу:
=ДЕНЬНЕД(C4, 2)
На изображении ниже все формулы возвращают день недели, соответствующий 1 января 2025 года, который хранится в Excel как число 45658. В зависимости от значения, установленного во втором аргументе, формулы выводят разные результаты.
На первый взгляд может показаться, что числа, возвращаемые функцией WEEKDAY, имеют очень мало практического смысла. Но давайте посмотрим на это под другим углом и обсудим некоторые формулы, решающие реальные задачи.
Как преобразовать дату Excel в название дня недели
Функция Excel WEEKDAY по своей конструкции возвращает день недели в виде числа. Чтобы преобразовать номер дня недели в название дня, используйте функцию ТЕКСТ.
Чтобы получить названия полных дней, используйте код формата «dddd»:
ТЕКСТ(ДЕНЬ НЕДЕЛИ(дата), “дддд”)
Чтобы вернуть сокращенные названия дней, используйте код формата «ddd»:
ТЕКСТ(ДЕНЬ НЕДЕЛИ(дата), “ддд”)
Например, чтобы преобразовать дату в формате A3 в название дня недели, используйте следующую формулу:
=ТЕКСТ(ДЕНЬНЕД(A3), “дддд”)
Или же
=ТЕКСТ(ДЕНЬ НЕДЕЛИ(A3), “ддд”)
Другое возможное решение — использовать ДЕНЬ НЕД вместе с функцией ВЫБОР.
Например, чтобы получить сокращенное название дня недели из даты в A3, формула выглядит следующим образом:
=ВЫБЕРИТЕ(ДЕНЬНЕД(A3),”Вс”,”Пн”,”Вт”,”Ср”,”Чт”,”Пт”,”Сб”)
Здесь ДЕНЬНЕД возвращает порядковый номер от 1 (воскресенье) до 7 (сб), а ВЫБОР выбирает соответствующее значение из списка. Поскольку дата в A3 (среда) соответствует 4, ВЫБОР выводит «Среда», что является 4-м значением в списке.
Хотя формулу ВЫБОР немного сложнее настроить, она обеспечивает большую гибкость, позволяя выводить названия дней в любом удобном для вас формате. В приведенном выше примере мы показываем сокращенные названия дней. Вместо этого вы можете указать полные имена, пользовательские сокращения или даже названия дней на другом языке.
Кончик. Еще один простой способ преобразовать дату в название дня недели — применить собственный формат даты. Например, в формате кода «дддд, мммм д, гггг» дата будет отображаться как «пятница, 3 января 2025 г.», а «дддд» — просто «пятница».
Формула Excel WEEKDAY для поиска и фильтрации рабочих и выходных дней
Имея дело с длинным списком дат, вы можете захотеть узнать, какие из них являются рабочими, а какие выходными.
Чтобы определить выходные и будние дни в Excel, создайте оператор IF с вложенной функцией WEEKDAY. Например:
=ЕСЛИ(ДЕНЬНЕД(A3, 2)<6, "Рабочий день", "Выходной")
Эта формула помещается в ячейку A3 и копируется во столько ячеек, сколько необходимо.
В формуле WEEKDAY вы устанавливаете return_type равным 2, что соответствует неделе с понедельника по воскресенье, где понедельник — это день 1. Таким образом, если номер дня недели меньше 6 (с понедельника по пятницу), формула возвращает «Рабочий день», в противном случае — “Выходные”.
Чтобы отфильтровать выходные или рабочие дни, примените фильтр Excel к набору данных (вкладка «Данные» > «Фильтр») и выберите «Выходные» или «Рабочие дни».
На скриншоте ниже у нас отфильтрованы будние дни, поэтому видны только выходные:
Если какой-то региональный офис вашей организации работает по другому графику, где дни отдыха отличны от субботы и воскресенья, вы можете легко настроить формулу WEEKDAY под свои нужды, указав другой return_type.
Например, чтобы считать субботу и понедельник выходными, задайте для return_type значение 12, чтобы получить тип недели «Вторник (1) — понедельник (7)»:
=ЕСЛИ(ДЕНЬНЕД(A2, 12)<6, "Рабочий день", "Выходной")
Как выделить выходные рабочие дни и в Excel
Чтобы с первого взгляда определить выходные и рабочие дни на листе, вы можете автоматически затенить их разными цветами. Для этого используйте формулу дня недели/выходных, описанную в предыдущем примере с условным форматированием Excel. Поскольку условие подразумевается, нам нужна только базовая функция WEEKDAY без оболочки IF.
Чтобы выделить выходные (субботу и воскресенье):
=ДЕНЬНЕД($A2, 2)<6
Чтобы выделить рабочие дни (понедельник – пятница):
=ДЕНЬНЕД($A2, 2)>5
Где A2 — верхняя левая ячейка выбранного диапазона.
Чтобы настроить правило условного форматирования, выполните следующие действия:
- Выберите список дат (в нашем случае A2:A15).
- На вкладке Главная в группе Стили щелкните Условное форматирование > Новое правило.
- В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек».
- В поле Формат значений, где эта формула истинна, введите вышеупомянутую формулу для выходных или будних дней.
- Нажмите кнопку Формат и выберите нужный формат.
- Нажмите OK дважды, чтобы сохранить изменения и закрыть диалоговые окна.
Подробную информацию о каждом шаге см. в разделе Как настроить условное форматирование с помощью формулы.
Результат выглядит довольно красиво, не так ли?
Как считать будни и выходные в Excel
Чтобы получить количество дней недели или выходных в списке дат, вы можете использовать функцию ДЕНЬ НЕД в сочетании с СУММ. Например:
Для подсчета выходных используется формула в D3:
=СУММ(–(ДЕНЬНЕД(A3:A20, 2)>5))
Для подсчета дней недели формула в D4 принимает следующий вид:
=СУММ(–(ДЕНЬНЕД(A3:A20, 2)<6))
В Excel 365 и Excel 2021, которые изначально обрабатывают массивы, это работает как обычная формула. В Excel 2019 и более ранних версиях нажмите Ctrl + Shift + Enter, чтобы сделать формулу массива.
Как работают эти формулы:
Функция WEEKDAY со значением return_type, равным 2, возвращает номер дня от 1 (понедельник) до 7 (воскресенье) для каждой даты в диапазоне A3:A20. Логическое выражение проверяет, больше ли возвращаемые числа 5 (для выходных) или меньше 6 (для будних дней). Результатом этой операции является массив значений TRUE и FALSE.
Двойное отрицание (–) приводит логические значения к единицам и нулям. А функция СУММ их складывает. Учитывая, что 1 (ИСТИНА) представляет дни, которые нужно подсчитывать, а 0 (ЛОЖЬ) — дни, которые нужно игнорировать, вы получите желаемый результат.
Кончик. Чтобы вычислить дни недели между двумя датами, используйте функцию ЧИСТРАБДНИ или ЧИСТРАБДНИ.МЕЖД.
Рассчитать разную оплату за рабочие и выходные дни
Наконец, давайте обсудим немного более конкретный вариант использования, демонстрирующий творческое использование функции ДЕНЬНЕД.
Предположим, вы рассчитываете выплаты сотрудникам, выполнившим дополнительную работу в выходные дни, поэтому вам необходимо применить разные ставки выплат для рабочих и выходных дней. Это можно сделать с помощью следующего оператора IF:
- В аргумент logical_test вложите функцию WEEKDAY, которая проверяет, является ли данный день рабочим или выходным.
- В аргументе значение_если_истина умножьте количество рабочих часов на ставку выходного дня (G4).
- В аргументе значение_если_ложь умножьте количество рабочих часов на ставку рабочего дня (G3).
Полная формула в D3 принимает следующий вид:
=ЕСЛИ(ДЕНЬНЕД(B3, 2)>5, C3*$G$4, C3*$G$3)
Чтобы формула корректно копировалась в указанные ниже ячейки, обязательно заблокируйте адреса ячеек ставок знаком $ (например, $G$4).
Не работает функция ДЕНЬ НЕД.
Как правило, формула WEEKDAY может возвращать две распространенные ошибки:
#ЦЕННОСТЬ! ошибка возникает, если:
- Serial_number или return_type не являются числовыми.
- Serial_number выходит за пределы поддерживаемого диапазона дат (от 1900 до 9999).
#ЧИСЛО! ошибка возникает, когда return_type выходит за допустимый диапазон (1-3 или 11-17).
Вот как можно использовать функцию ДЕНЬНЕД в Excel для управления днями недели. В следующей статье мы рассмотрим функции Excel для работы с более крупными единицами времени, такими как недели, месяцы и годы. Пожалуйста, оставайтесь с нами и спасибо за чтение!
Практическая рабочая тетрадь для скачивания
Формула ДЕНЬ НЕДЕЛИ в Excel – примеры (файл .xlsx)
