График сменности в экселе

Хочу поделиться способом создания автоматически заполняемого графика сменности в экселе. За основу взял формулу универсального календаря, которая описана тут

Чтобы сделать автозаполняемый календарь, на месяц который задается  в ячейке В2, нужно сделать заготовку из дней недели, затем выделить диапазон B4:H9 и в строку формулы вставить скопированную отсюда формулу:

=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)) <>МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1);" "; ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1)

Затем нажмите сочетание Ctrl+Shift+Enter. Формат этих ячеек нужно выбрать (все форматы) и ввести туда Д, чтобы отображался только день.

Теперь можно сделать так чтобы по нажатию на кнопку вверх или вниз, у нас менялся месяц в ячейке В2. В меню "Разработчик"-> "Вставить" нужно добавить элемент управления формой счетчик

После добавления нужно связать его с ячейкой, для этого жмем правой кнопкой - формат объекта - вкладка элемент управления и там связь с ячейкой. Вбиваем шаг изменения 31

Теперь в ячейку В2 нужно вбить формулу:

=КОНМЕСЯЦА(43800+I2;-1)+1

тут 43800 - это дата 1 декабря 2019 года в числовом формате. Теперь нажимая на стрелку вверх можно листать календарь, который будет сам заполняться так как надо

Добавим выбор бригады для заполнения календаря. Снова идем на вкладку разработчик и выбираем переключатель, добавляем 4 переключателя и связываем их с ячейкой I4

Теперь сделаем график сменности.1 ноября 2019 у нас первая бригада выходит в день это 43770 в числовом формате, но берем на день меньше, т.к. прибавляется 1 при выборе 1 бригады. В итоге получаем формулу для условного форматирования в диапазоне ячеек календаря:

=ОСТАТ(B4+43669+$I$4;4)=0

То есть если эта дата будет делиться без остатка на 4, то ее нужно окрашивать в оранжевый цвет. И такая дата будет повторяться каждые 4 дня, естественно

А на следующий день идет ночная смена, которая тоже повторяется каждые 4 дня:

=ОСТАТ(B4+43672+$I$4;4)=0

Ночные для удобства можно рисовать через день, тогда нужно поиграться с числом 43672, а именно поставить 43671

Еще есть вариант календаря в одну  сроку, с автоматическим заполнением выходных

Делается это так: первое число в первый столбец это ссылка наячейку с датой, вторая это
=ЕСЛИ(ЕОШ(B12+1);" ";ЕСЛИ(B12+1<=КОНМЕСЯЦА(date;0);B12+1;""))
Протягиваем эту формулу на следующие 31 ячейку. Потом добавляем условное форматирование формулу =ДЕНЬНЕД(B$12;2)=7 и еще =ДЕНЬНЕД(B$12;2)=6
Скачать файл со всеми формулами и форматированием тут

Онлайн график смен с аналогичным функционалом есть на моем гитхабе

Также на гитхабе можно скачать этот график в виде apk приложения для Android