Универсальный календарь формулой

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

Чтобы ее использовать создайте на листе заготовку вот такого вида:

universal-calendar1.png

Дата в ячейке B2 может быть любой, тут важен только месяц и год. В ячейках диапазона B3:H3 могут быть названия дней недели в любом подходящем формате. 

Теперь выделите диапазон 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)

В английском варианте это будет:

=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1)) <>MONTH(DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””, DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

Затем нажмите сочетание Ctrl+Shift+Enter, чтобы ввести эту формулу как формулу массива. Все выделенные ячейки должны заполнится датами заданного в B2 месяца:

universal-calendar2.png

Останется только навести внешний блеск, добавив форматирование и скрыв день в заголовке B2 и месяц и год в остальных ячейках с помощью окна Формат ячеек (Ctrl+1):

universal-calendar3.png

Теперь, меняя дату в ячейке B2, мы будем получать по нашей формуле корректный календарь на любой выбранный месяц любого года. Практически, вечный календарь ;)

Ссылки по теме

 



MCH
30.12.2013 07:18:08
зачем МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)) ? достаточно ведь просто МЕСЯЦ(B2)

чуть короче, но по тому же принципу:
=ЕСЛИ(МЕСЯЦ(B2-ДЕНЬ(B2)-ОСТАТ(B2-ДЕНЬ(B2)-1;7)+{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7})<>МЕСЯЦ(B2);"";B2-ДЕНЬ(B2)-ОСТАТ(B2-ДЕНЬ(B2)-1;7)+{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7})

или так
=ЕСЛИ(МЕСЯЦ(ОТБР((B2-ДЕНЬ(B2)-1)/7+{0:1:2:3:4:5})*7+{1;2;3;4;5;6;7}+1)<>МЕСЯЦ(B2);"";ОТБР((B2-ДЕНЬ(B2)-1)/7+{0:1:2:3:4:5})*7+{1;2;3;4;5;6;7}+1)
30.12.2013 10:22:20
Красивый вариант!
02.01.2014 13:27:51
Как всегда, пришел МСН и все улучшил! :)

"Он придет и молча исправит все..." (с) БГ
13.01.2014 20:05:54
Потому что у Уокенбаха ТАК :)
16.03.2014 14:55:53
Кстати, да - только что проверил :)
16.02.2014 04:13:24
Ребята, а как сделать календарь на несколько лет, например, с 2005 по 2010 гг, чтобы я выбрала февраль 2007 года (месяц и год из выпадающего списка)  и  мне выдало календарь именно за этот месяц этого года. Года менять у меня получилось, а вот при выборе месяца, календарь не меняется.
16.03.2014 04:38:53
А можно вот так без формулы массива:
=ЕСЛИ(И(ЕСЛИОШИБКА(ЕСЛИ(ДЕНЬНЕД(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИОШИБКА(ЗНАЧЕН(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))));0)+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0));2)=СТОЛБЕЦ()-СТОЛБЕЦ($B$7)+1;ДЕНЬ(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИ(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7)))="";0;СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))))+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0)));"";);"";)<8-ДЕНЬНЕД(ДЕНЬНЕД(ДАТАЗНАЧ("01."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0));2);2);СТРОКА()-СТРОКА($B$7)>0);"";ЕСЛИОШИБКА(ЕСЛИ(ДЕНЬНЕД(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИОШИБКА(ЗНАЧЕН(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))));0)+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0));2)=СТОЛБЕЦ()-1;ДЕНЬ(ДАТАЗНАЧ(ТЕКСТ(ЕСЛИ(СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7)))="";0;СМЕЩ($A$7;ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;СТРОКА()-СТРОКА($B$7)-1;СТРОКА()-СТРОКА($B$7));ЕСЛИ(СТОЛБЕЦ()-СТОЛБЕЦ($B$7)=0;7;СТОЛБЕЦ()-СТОЛБЕЦ($B$7))))+1;0)&"."&ТЕКСТ(МЕСЯЦ(ЛЕВСИМВ(ТЕКСТ($B$5;0);3)&"."&ТЕКСТ($B$1;0));0)&"."&ТЕКСТ($B$1;0)));"";);"";))
15.05.2014 09:58:02
29.01.2015 19:18:36
08.02.2016 07:06:24
календарь в одну формулу, это круто. у меня так не получилось бы. тут как-то можно прикрепить файл excel для вопроса?
08.02.2016 07:16:53
на прошлой неделе просто загорелся excel'ем, когда посмотрел ролики в интернете (и даже подписался на planetaexcel на youtube), был честно поражен тем сколько всего можно сделать (в универе как-то не так было интересно и понятно). на этой волне решил помочь кадровику и создал "табель рабочего времени". выставляешь дату начала месяца и он разлиновывает/выделяет выходные дни, также создал колонку праздники и он тоже учитывает их. но вот столкнулся с проблемой, а как быть когда праздник выпадает на выходной, он же куда-то переносится. но это еще можно отследить. а вот когда в выходной работаем, его выделять не нужно. короче проблема производственного календаря. возможно на все года это делать нет смысла, но создать доп колонку дней исключений определенного года или т.п. можете подсказать в моём варианте это как-то можно реализовать? сложно объяснить алгоритм, хотелось бы показать сам файл, но не знаю как прикрепить. (сразу оговорюсь, файл .xslm содержит макрос, который прячет/показывает не нужные столбцы из таблицы, которые возникают из-за не одинакового количества дней в месяцах).
17.03.2014 09:47:45
Доброго времени суток Николай! Для начало хотелось бы выразить свою благодарность за ответ на мой вопрос и сказать большое спасибо за сайт и возможность черпать необходимую информацию с Вашего сайта!
И вопрос: можно ли создать календарь где в каждой ячейке с датой находилась бы информация т.е таблица excel с проделанной работой или шаблоны с этой таблицей? Для удобства дальнейшего их архивирования и для востоновления сценариев проделанной работы по мере необходимости. Думаю было бы очень удобно в разворачиваемом окне календаря иметь необходимую для работы таблицу.Зарание спасибо!
19.03.2014 20:18:23
Здравствуйте! У меня есть вопрос, как нужно написать формулу  имея дату начала месяца, чтобы в верхних ячейках прописались все даты месяца(1янв, 2янв и т.д.), а в ячейках под датами соответствующие дни недели? Благодарю за ответ :oops:
29.03.2014 09:02:41
Добрый день, Николай. Огромное спасибо за сайт. Прошу помочь с очень банальной вещью. У меня смещенный график работы, поэтому чтоб ыто-то планировать нужно считать дни. Хотелось бы автоматизировать этот процесс в экселе. Мой график работы: 4 дня подряд работаю утром (выделить каким-то цветом), потом 2 дня отдыхаю. Далее 4 дня работаю днем (выделить другим цветом), потом 2 дня отдыхаю. и снова 4 - утро, 2 выходных, 4 - днем, 2 выходных, такой цикл. Помогите наложить этот цикл на календарь в цветах. Заранее благодарю.
02.09.2014 19:16:27
Здравствуйте, я тоже ищу возможность автоматической окраски выходных дней календаря... строю график из месяца в месяц, хотелось бы его автоматизировать... Заранее благодарен за наводку
03.09.2014 16:41:46
Сергей, можно использовать условное форматирование. Выделить ячейки с датами, Главная - Условное форматирование - Создать правило - Использовать формулу и ввести что-то типа:

=ДЕНЬНЕД(А1;2)>5

где А1 - адрес первой выделенной ячейки (без $)
Потом нажать на кнопку Формат и выбрать цвет заливки.
02.09.2014 19:28:55
Точнее выразиться, как окрасить ячейку со значением суббота и воскресение, я разобрался, а как окрасить ячейки в этот же цвет под этими ячейками выходных?
11.12.2014 12:46:25
Большое спасибо, понравилось.
Как сделать подсветку сегодняшней даты? Условное форматирование по =день(сегодня()) и =сегодня() не работает(((

Разобрался! Условное форматирование - правила выделения ячеек - дата
13.01.2015 15:25:08
Нашла подходящую тему для своего вопроса....но нужна помощь
Как сделать календарный график, но так чтобы один день занимал 2 ячейки -до 14.00, после 14.00 (обеденный)..и соответственно не в ручную числа проставлять на месяц. числа на месяц должны идти в строку.В дальнейшем это будет диаграмма ганта с помощью условного форматирования (хотя может я хочу невозможного..) спасибо если кто-нибудь откликнется
16.02.2015 12:44:18
В украинском офисе будет формула такова: =IF(MONTH(DATE(YEAR(B2);MONTH(B2);1)) <>MONTH(DATE(YEAR(B2);MONTH(B2);1)- (WEEKDAY(DATE(YEAR(B2);MONTH(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1\2\3\4\5\6\7}-1);" "; DATE(YEAR(B2);MONTH(B2);1)- (WEEKDAY(DATE(YEAR(B2);MONTH(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1\2\3\4\5\6\7}-1)
20.02.2015 10:39:10
можно добавить в ячейках условие: если равно ячейке в шапке (дате), то можно выделить и день
13.08.2015 11:51:59
Или прописать в УФ, что значение ячейки =СЕГОДНЯ().
31.03.2015 10:35:21
Добрый день. Скачал себе "вечный календарь" (http://www.excel-office.ru/blankishablonivexcel/excel12), при изменения года меняется календарь и выходные (суббота и воскресение) принадлежащие месяцу автоматически окрашиваются. Начал менять внешний вид, вместо вертикальных недель в месяце делать горизонтальные, но не как не получается с выходными :cry:. Подскажите плиз.
20.04.2015 11:18:54
Добрый день! Поскажите, пожалуйста, как сделать так,чтоб в живой календарь подтягивались задание со списка на определенную дату.Спасибо!
05.11.2015 20:26:23
Здравствуйте Николай,
Спасибо за ваш труд и помощь в изучении этой программы.
У меня вопрос по условному форматированию этого календаря.
Столбец таблицы содержит набор дат.
02.12.15
03.06.15
и т.д. в любой последовательности.
Каким должно быть условие/формула, чтобы при совпадении даты из столбца с датой в календаре, выполнялось условное форматирование?
С уважением Виктор.
01.12.2015 15:04:53
Уверен, многие будут благодарны, если кто-то сможет сделать "разбор" формулы. А именно в части
...{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1...
Что делает этот набор чисел? Как это работает?
02.12.2015 15:08:45
Сам отвечу, разобрался.
Данная формула предназначена только для диапазона, где 6 строк и 7 колонок. Для диапазона с любым другим количеством строк и колонок формулу необходимо переделать. Например если вы хотите, чтобы дни шли не по горизонтали, а по вертикали.
Фрагмент
{0:1:2:3:4:5}
в каждой строке диапазона размещает соответствующее число от 0 до 5:
0000000
1111111
2222222
3333333
4444444
5555555
Далее полученный результат умножаем на 7:
{0:1:2:3:4:5}*7
и получаем результат:
0000000
7777777
14141414141414
21212121212121
28282828282828
35353535353535
Далее к полученному результату добавляем еще один массив. Отличие второго массива в том, что разделителем является ";", а не ":".
Разделитель ":", как вы поняли, размещает соответствующее число в соответствующую строку независимо от столбца, а разделитель ";" размещает соответствующее число в соответствующие колонки независимо от строки.
Результат массива
{1;2;3;4;5;6;7}
будет такой:
1234567
1234567
1234567
1234567
1234567
1234567
Результат работы двух массивов:
{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7} 
будет такой:
1234567
891011121314
15161718192021
22232425262728
29303132333435
36373839404142
Ну и все. Дальше арифметика.
В конце из каждой ячейки полученного массива вычитается 1. Этой единичной можно оперировать, если требуется например начать неделю не с Понедельника, а с Воскресенья.
Таким образом, если вам требуется формула для вертикального календаря с 7-мью строками и 6-тью колонками, то фрагмент формулы с загадочными числами будет следующий:
{0:1:2:3:4:5:6}+{0;1;2;3;4;5;6}*7
Надеюсь, помог кому-нибудь.
09.06.2017 11:10:21
Помог, спасибо
03.02.2016 13:04:36
комбинация клавиш Ctrl+Shift+Enter ни к чему не приводит, подскащите последовательность действий.
Копирую формулу, выделяю диапазон, нажимаю комбинацию клавиш  "Ctrl+Shift+Enter, чтобы ввести эту формулу как формулу массива" - ячейки не заполняются, выделение остаётся. Что не так сделано?
При попытке просто вставить в диапазон формулу, говорит, что данные в буфере не совпадают с выделенной областью
26.07.2016 15:03:52
о такая же фигня,что это может быть?
03.10.2016 10:53:37
Как сделать сплошной календарь на несколько месяцев? без разделения на отдельные месяцы.

Или может, есть решение этой задачи?
Есть список мероприятий различной длительности (до 5 дней). когда человек выбирает дату начала мероприятия, закрашиваются все 5 дней в календаре - первый (выбранный) и 4 после. Проблема возникает, когда дата начала мероприятия - в конце месяца (правая картинка): https://pp.vk.me/c636926/v636926682/2a04e/zkUXU1r7t5w.jpg
1 случай - всё ок, то есть форматирование с учетом переноса на след месяц отображается нормально
2 случай - не ок, то есть когда в первом месяце есть пустая строка.

Спасибо!!!
13.10.2016 11:14:30
добрый день, попробовал вашу формулу для создания календаря, сделал все в точности как у вас описано.
но при нажатии Ctrl+Shift+Enter выдает ошибку
использовал формулу =if(month(trunc(B2-Day(B2)-1)/7+{0:1:2:3:4:5})*7+1)<>Month(B2);"";trunc((B2-day(B2)-1)/7+{0:1:2:3:4:5})*7+{1:2:3:4:5:6:7}+1)
[img][IMG]http://images.vfl.ru/ii/1476346414/5d21451a/14500212_s.jpg[/img][/IMG]
30.10.2016 15:50:24
Автор, браво :)
А возможно ли создание на листе Excel некоего аналога из правого нижнего угла экрана Windows (из системного трея)? К примеру, нажать на стрелочку, выбрать год, нажать на стрелочку выбрать месяц, кликнуть на число и в определенной ячейке вставится 12.04.2010 г.?
24.12.2016 20:29:26
Добрый вечер.
Помогите реализовать такой календарик, чтобы можно было после каждого числа вписывать данные
19.06.2017 07:47:31
Доброе время суток! Подскажите пожалуйста как развернуть календарь, чтобы дни недели были вертикальными. Формулу использовал =ЕСЛИ(МЕСЯЦ(ОТБР((B2-ДЕНЬ(B2)-1)/7+{0:1:2:3:4:5})*7+{1;2;3;4;5;6;7}+1)<>МЕСЯЦ(B2);"";ОТБР((B2-ДЕНЬ(B2)-1)/7+{0:1:2:3:4:5})*7+{1;2;3;4;5;6;7}+1).Спасибо заранее
30.12.2018 15:19:54
попробовал построить календарь сводной таблицей

исходная табл
колонка_1 прогрессия по дням
колонка_2 день()
колонка_3 деннед()
колонка_4 номнед()
колонка_5 год()

и построил св табл:
в столбцы поместил поле "ден нед" - получил 7 колонок
в строки 3 поля "год <- номер_недели <- мес"
в поле значений 2-ую колнку "День()" из исходной таблицы

колонку потом "B" можно скрыть

но такой календарь кажется бесполезен

29.03.2019 18:18:39
Спасибо автору всё получилось  
Наверх