Как выделить выходные дни в excel

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

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

Синтаксис

Аргументы функции ЧИСТРАБДНИ.МЕЖД описаны ниже.

• Нач_дата и кон_дата Обязательный. Даты, разницу между которыми требуется вычислить. Начальная дата может предшествовать конечной дате, совпадать с ней или быть позже нее.
• Выходной Необязательный. Указывает, какие дни недели являются выходными и не включаются в число рабочих дней между начальной и конечной датой. Значение может задаваться номером выходного дня или строкой, определяющей, какие дни являются выходными. Номера обозначают следующие выходные дни:

Номер выходного дня

Выходные дни

Строковые значения дней недели включают семь знаков, каждый из которых обозначает день недели (начиная с понедельника). Значение 1 представляет нерабочие дни, а 0 — рабочие дни. В строке допустимо использовать только знаки 1 и 0. При значении 1111111 всегда возвращается 0.

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

• Праздники Необязательный. Набор из одной или нескольких дат, которые необходимо исключить из календаря рабочих дней. Значение «праздники» должно быть диапазоном ячеек, содержащих даты, или константой массива, включающей порядковые значения, которые представляют даты. Порядок дат или значений может быть любым.

Замечания

• Если начальная дата позже конечной даты, возвращаемое значение будет отрицательным, а его модуль будет равен числу полных рабочих дней.
• Если start_date выходит за пределы диапазона для текущего базового значения даты, NETWORKDAYS. INTL возвращает #NUM! (значение ошибки).
• Если end_date выходит за пределы диапазона для текущего базового значения даты, NETWORKDAYS. INTL возвращает #NUM! (значение ошибки).
• Если строка выходных дней имеет недопустимую длину или содержит недопустимые символы, NETWORKDAYS. INTL возвращает #VALUE! значение ошибки #ЗНАЧ!.

Пример

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

Получаем 22 предстоящих рабочих дня. Вычитает 9 нерабочих выходных дней (5 суббот и 4 воскресенья) из 31 общего дня между двумя датами. По умолчанию суббота и воскресенье считаются нерабочими днями.

Получаем -21, т. е. 21 прошедший рабочий день.

Из 32 дней между 1 января 2006 и 1 февраля 2006 года вычитаем 10 нерабочих дней (4 пятницы, 4 субботы и 2 праздника) и получаем 22 предстоящих рабочих дня. Используем аргумент 7 в качестве выходных дней, которыми являются субботы и воскресенья. Также на этой период времени приходится два праздника.

Получаем 22 предстоящих рабочих дня. Такой же период времени, как и в приведенном выше примере, но с выходными днями по субботам и средам.

Как выделить выходные дни в excel

Cоздаём графики работы, автоматически заполненяемые табеля учёта рабочего времени и т.п…. (продолжение)

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

Создадим в нашей книге новый лист, под названием ПРАЗДНИКИ, на нем организуем «умную таблицу», я назову её ВЫХОДНЫЕ (сменить имя таблицы можно на вкладке «Конструктор», когда выделена любая ячейка таблицы).

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

Присвоим имена нашим столбцам (без заголовков), чтобы их можно было использовать в правилах Условного форматирования.

Для этого нужно выделить ячейки, к примеру A1:A2, на вкладке Формулы выбрать команду Задать Имя, и в выпадающем окошке ввести имя для диапазона (я назвал его «Праздник», второму диапазону B1:B2 я присвоил имя «Перенос»).

Вызывать именованные диапазоны в формулы можно с помощью клавиши F3 (или написать имя вручную).

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

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

1. Выделяем диапазон ячеек, для которых нужно применять форматирование.
2. На вкладке Главная, выбираем Условное форматирование и в выпадающем списке пункт Управление правилами
3. В появившемся окне выбираем правило и нажимаем на кнопку Изменить правило.4, Меняем нашу формулу.

=ИЛИ(И(ДЕНЬНЕД(G1;2)>5;СЧЁТЕСЛИ(Перенос;G1)=0);СЧЁТЕСЛИ(Праздник;G1)>0)

Т.е. у нас должно выполняться несколько условий:

— чтобы день недели был субботой или воскресеньем и при этом даты не было в списке перенесенных дней (условия должны выполняться одновременно, за этим в формуле следит функция И (AND).

-чтобы дата была в списке праздничных дней, независимо от того на какой день недели она попадает.

Так как эти условия не могут выполняться одновременно – нужно использовать функцию ИЛИ (OR).

Нажав на «ОК» (2 раза) мы увидим, что всё работает так, как мы задумывали и теперь эти даты тоже участвуют в определении выходных и рабочих дней.

Для тех кто хочет комплексно изучить Excel, понять его суть, логику и принципы работы курс «Excel бизнес-анализ и прогнозирование»
Кроме 27 часов материала, 12 домашних заданий и курсового проекта в чате онлайн поддержки вы сможете задавать свои вопросы и получать подобные подсказки в решение Ваших трудностей по работе с Excel.
Детально ознакомиться с программой курса, наполнением пакетов и ценами можно тут.

Автор статьи тренер DATAbi Михаил Беленчук

Пример формулы выделения цветом выходных дней по дате в Excel

Во время работы с графиками и календарями всегда кстати очень полезная возможность выделения цветом дат выходных дней (субботы и воскресенья). Формула условного форматирования позволяет выделить все даты выходных дней в списке графика или на календаре в Excel.

Как выделить цветом только выходные дни по дате в Excel

Ниже на рисунке приведен пример автоматического выделения цветом дат выходных дней:

Чтобы создать такое правило для условного форматирования, выполните следующие действия шаг за шагом:



1. Выделите исходных диапазон ячеек (в данном примере это A2:A17) и выбреете инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно как показано ниже на рисунке:
2. В появившемся окне выберите опцию: «Использовать формулу для определения форматируемых ячеек». Теперь у нас есть возможность ввести формулу для определения собственных правил и условий выделения ячеек цветом в предварительно выделенном исходном диапазоне.
3. В поле ввода введите логическое выражение формулы представленное на данном этапе. Обратите внимание на то, что в формуле используются только относительные ссылки на ячейки. Для определения дня недели по значению ячейки используется функция ДЕНЬНЕД. Если функция при определенно заданных аргумента будет возвращать значение 6 или 7, это значит, что в текущей ячейке (например, A4) записана дата выходного дня. После чего итоговый результат вычисления целой формулы будет возвращать логическое значение ИСТИНА. В таком случае для этой ячейки будет применено условное форматирование, предварительно заданное пользователем в настройках данного инструмента (описано на следующем этапе). =ИЛИ(ДЕНЬНЕД(A2)=1;ДЕНЬНЕД(A2)=7)
4. Щелкните на кнопку «Формат» и появится знакомое окно для оформления стиля отображения ячейки и ее значения – «Формат ячеек», как показано ниже на рисунке. В данном окне вы имеете возможность указать цвета для заливки, шрифтов и границ ячейки. А также присвоить другие свойства декорации формата. После внесения всех настроек подтвердите их нажатием на кнопку ОК на всех открытых окнах.

В результате в столбце даты подсветились цветом только даты с выходными днями недели.

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как выделить выходные дни в excel

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

Выделяйте выходные и праздничные дни с помощью условного форматирования

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

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

2, Затем нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:

3. В выскочившем Новое правило форматирования диалоговое окно:

• Нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. из Выберите тип правила список;
• Введите эту формулу: =NETWORKDAYS($A2,$A2,$F$2:$F$6)=0 в Форматировать значения, где эта формулаправда текстовое окно;
• А затем нажмите Формат .

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

4. В открытом Формат ячеек диалоговое окно под Заполнять на вкладке укажите цвет выделения строк, см. скриншот:

5, Затем нажмите OK > OK чтобы закрыть диалоги, и теперь строки, содержащие выходные и праздничные дни, заштрихованы сразу, см. скриншот:

Больше относительных статей:

• Цветные альтернативные строки для объединенных ячеек
• Очень полезно форматировать чередующиеся строки с другим цветом в больших данных, чтобы мы могли сканировать данные, но иногда в ваших данных могут быть некоторые объединенные ячейки. Чтобы выделить строки поочередно другим цветом для объединенных ячеек, как показано на снимке экрана ниже, как вы могли бы решить эту проблему в Excel?

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

• Выделите ячейку, если значение больше, чем другая ячейка
• Чтобы сравнить значения в двух столбцах, например, если значение в столбце B больше, чем значение в столбце C в той же строке, затем выделите значения из столбца B, как показано ниже. В этой статье я собираюсь представить некоторые методы выделения ячейки, если значение больше, чем другая ячейка в Excel.

• Выделите строки на основе раскрывающегося списка
• В этой статье будет рассказано о том, как выделить строки на основе раскрывающегося списка. Например, сделайте следующий снимок экрана. Когда я выбираю «Выполняется» из раскрывающегося списка в столбце E, мне нужно выделить эту строку красным цветом, когда я выберите «Завершено» из раскрывающегося списка, мне нужно выделить эту строку синим цветом, а когда я выберу «Не начато», для выделения строки будет использоваться зеленый цвет.