Как создать динамический календарь в Google Таблицах

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

РАЗНДАТ(

)

, английский вариант DATEDIF().

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

Расчет страхового (трудового) стажа в MS EXCEL.

Функции

РАЗНДАТ(

)

нет в справке EXCEL2007 и в

Мастере функций

(

SHIFT

+

F

3

), но она работает, хотя и не без огрех.

Некоторые записи в Google Таблицах обязательно сопровождаются указанием месяца, числа и дня недели. Конечно, все это можно расставлять в ячейках вручную, однако с каждым месяцем данные придется менять или создавать новую таблицу. Это неудобно, особенно если учитывать тот факт, что сами Таблицы были созданы для упрощения взаимодействия со значениями.

Поэтому в рамках данной статьи я разберу метод создания динамического календаря, числа в котором будут автоматически меняться в соответствии с текущим месяцем. К тому же мы настроим подсветку выходных дней.

Найдем дату, отстоящую от начальной даты на заданное количество месяцев.

Пусть в ячейке

А6

задана дата

05. 2012. Дата, отстоящая от нее на 2 месяца, очевидно,

05. 2012. Этот результат можно получить формулой

=ДАТАМЕС(A6;2))

Альтернативная формула

=ДАТА(ГОД(A6);МЕСЯЦ(A6)+2;ДЕНЬ(A6))

может дать в некоторых (редких) случаях другой результат. Например, если к дате

29. 2008

(високосный год) прибавить 12 месяцев, то функция

ДАТАМЕС()

вернет

28. 2009

, а альтернативная формула

01. 2009. Первый вариант представляется правильным. В статье о функции

ДАТАМЕС()

приведен еще один пример расхождения результатов расчетов (см. Функция ДАТАМЕС() в MS EXCEL

).

Создадим последовательности дат и времен различных видов:

01. 09, 01. 09, 01. 09,. , янв, апр, июл,. , пн, вт, ср,. , 1 кв. , 2 кв. , 09:00, 10:00, 11:00,. и пр.

каждому значению даты соответствует определенное число (см. статью

Как EXCEL хранит дату и время

), то подходы для формирования последовательностей, изложенные в статье

Числовые последовательности

, применимы и для дат. Однако, имеются и свои особенности.

Последовательность

01. 09, 01. 09, 01. 09

(первые дни месяцев) можно сформировать формулой

=ДАТАМЕС(B2;СТРОКА(A1))

, в ячейке

B2

должна находиться дата — первый элемент последовательности (

01. 09

).

Эту же последовательность можно сформировать используя правую клавишу мыши. Пусть в ячейку

A2

введено значение

01. Выделим ячейку

A2. Удерживая правую клавишу мыши, скопируем

Маркером заполнения

, значение из

A2

в ячейки ниже. После того, как отпустим правую клавишу мыши, появится контекстное меню, в котором нужно выбрать пункт

Заполнить по месяцам.

Изменив формат ячеек, содержащих последовательность

01. 09, 01. 09, 01. 09,

на

МММ

(см. статью

Пользовательский формат дат

) получим последовательность

янв, фев, мар,.

Эту же последовательность можно ввести используя список автозаполения

(введите

янв

, затем

Маркером заполнения

скопируйте вниз).

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

Аналогичным образом можно сформировать последовательность дней недели

пн, вт, ср,.

Последовательность кварталов

1 кв. , 2 кв. можно сформировать используя идеи из статьи

Текстовые последовательности.

Используя инструмент

Прогрессия

, можно создать последовательности только рабочих дней. А также с шагом месяц и год.

Последовательность первых месяцев кварталов

янв, апр, июл, окт, янв,. можно создать введя в две ячейки первые два элемента последовательности (

янв, апр

), затем (предварительно выделив их) скопировать вниз

маркером заполнения. Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу

=ДАТАМЕС($G$16;(СТРОКА(A2)-СТРОКА($A$1))*3)

Предполагается, что последовательность начинается с ячейки

G16

, формулу нужно ввести в ячейку

G17

(см. файл примера

).

Временную последовательность

09:00, 10:00, 11:00,. можно сформировать используя

Маркер заполнения. Пусть в ячейку

A2

введено значение

09

:

00. Выделим ячейку

A2. Скопируем

Маркером заполнения

, значение из

A2

в ячейки ниже. Последовательность будет сформирована.

Если требуется сформировать временную последовательность с шагом 15 минут (

09:00, 09:15, 09:30,. ), то можно использовать формулу

=B15+1/24/60*15

(Предполагается, что последовательность начинается с ячейки

B15

, формулу нужно ввести в

B16

). Формула вернет результат в формате даты.

Другая формула

=ТЕКСТ(B15+1/24/60*15;»чч:мм»)

вернет результат в текстовом формате.

СОВЕТ:

О текстовых последовательностях вида

первый, второй,. , 1), 2), 3),. можно прочитать в статье

Текстовые последовательности. О числовых последовательностях вида

1, 2, 3,. 1, 3, 5, 7,. I, II, III, IV,. можно прочитать в статье

Числовые последовательности.

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами — и понимает их все:

«Классическая» форма
  3. 2006

   Сокращенная форма
3. 06

   С использованием дефисов
3-10-6

   С использованием дроби
   3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т. ) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Как создать динамический календарь в Google Таблицах

Время вводится в ячейки с использованием двоеточия. Например

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

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

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать — как именно копировать выделенную дату:

Как создать динамический календарь в Google Таблицах

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

Как создать динамический календарь в Google Таблицах

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY):

Как создать динамический календарь в Google Таблицах

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку:

Как создать динамический календарь в Google Таблицах

То есть, с точки зрения Excel, 27. 2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

  • Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!  😉
  • Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Как создать динамический календарь в Google Таблицах

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т

Как создать динамический календарь в Google Таблицах

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см. выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Как создать динамический календарь в Google Таблицах

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Как создать динамический календарь в Google Таблицах

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

  • Как вычислять возраст (стаж) в полных годах-месяцах-днях
  • Как сделать выпадающий календарь для быстрого ввода любой даты в любую ячейку.
  • Автоматическое добавление текущей даты в ячейку при вводе данных.
  • Как вычислить дату второго воскресенья февраля 2007 года и т.п.

Разница в полных месяцах («m»)

Формула

=РАЗНДАТ(A2;B2;»m»)

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

Пример1:

начальная_дата

01. 2007,

конечная_дата

01. 2007

Результат:

1 (месяц)

Пример2:

начальная_дата

01. 2007,

конечная_дата

31. 2007

Результат:

0

При расчете стажа, считается, что сотрудник отработавший все дни месяца — отработал 1 полный месяц. Функция

РАЗНДАТ()

так не считает!

Пример3:

начальная_дата

01. 2007,

конечная_дата

01. 2009

Результат:

25 месяцев

Формула может быть заменена альтернативным выражением:

=12*(ГОД(B2)-ГОД(A2))-(МЕСЯЦ(A2)-МЕСЯЦ(B2))-(ДЕНЬ(B2)<ДЕНЬ(A2))

Внимание

: В справке MS EXCEL (см. раздел Вычисление возраста) имеется кривая формула для вычисления количества месяце между 2-мя датами:

Если вместо функции ТДАТА() — текущая дата использовать дату 31. 1961, а в А3 ввести 01. 1962, то формула вернет 13, хотя фактически прошло 12 месяцев и 1 день (ноябрь и декабрь в 1961г. + 10 месяцев в 1962г.

Синтаксис функции

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Аргумент

начальная_дата

должна быть раньше аргумента

конечная_дата.

Аргумент

способ_измерения

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

Значение

Описание

«d»

разница в днях

«m»

разница в полных месяцах

«y»

разница в полных годах

«ym»

разница в полных месяцах без учета лет

«md»

разница в днях без учета месяцев и лет ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение, если день начальной даты больше дня конечной даты (например, в EXCEL 2007 при сравнении дат 28. 2009 и 01. 2009 результат будет 4 дня, а не 1 день). Избегайте использования функции с этим аргументом. Альтернативная формула приведена ниже. «yd»

разница в днях без учета лет ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение. Избегайте использования функции с этим аргументом.

Ниже приведено подробное описание всех 6 значений аргумента

способ_измерения

, а также альтернативных формул (функцию

РАЗНДАТ()

можно заменить другими формулами (правда достаточно громоздкими). Это сделано в

файле примера

).

В файле примера значение аргумента

начальная_дата

помещена в ячейке

А2

, а значение аргумента

конечная_дата

– в ячейке

В2.

Заполнение таблицы

Сейчас разберемся с заполнением таблицы, в которой и будет присутствовать динамический календарь. Настроим список месяцев и сделаем так, чтобы при выборе одного из них числа и дни недели в ячейках автоматически менялись на актуальные. Красивое оформление пропустим, поскольку с этим этапом вы сможете разобраться и самостоятельно.

  • Выберите первую ячейку, на листе, которая будет служить списком с месяцами. Щелкните по ней правой кнопкой мыши и в контекстном меню нажмите кнопку «Настроить проверку данных».
  • В поле «Укажите диапазон или» нажмите по значку с ячейками.
  • Перейдите на вспомогательный лист и выделите все клетки с месяцами, формируя тем самым список.
  • Сохраните изменения, кликнув по соответствующей кнопке.
  • Теперь, разворачивая список в клетке, вы видите перечень всех месяцев, расположенных на вспомогательном листе.
  • Активируйте первую ячейку строки, с которой должен начинаться отсчет дней. Введите в ней формулу =FILTER(‘НазваниеВспомогательногоЛиста’!B:B; ‘НазваниеВспомогательногоЛиста’!’A:A=A1′). Таким образом, вы создаете фильтр, чтобы при выборе месяца автоматически подставлялся его первый день в данную клетку. Если список и названия на вспомогательном листе у вас находятся в столбцах под другими буквами, поменяйте значения в формулах под себя.
  • Измените из выпадающего списка месяц, чтобы убедиться в корректной динамической дате.
  • В клетке следующего дня введите =B1+1, где B1 – та самая клетка с функцией фильтра. Так вы добавите один день к текущему, получив следующий.
  • Растяните формулу до конца таблицы (на 31 день), достигнув тем самым отображения абсолютно всех чисел месяца. Соответственно, при изменении самого месяца даты тоже будут меняться на соответствующие, как это было показано ранее.

Последняя задача данного этапа – сделать так, чтобы формат ячеек соответствовал вашим требованиям. Разберем это на простом примере, чтобы вместо 01. 2022 отображался формат Суббота, 1 (и так далее по всем дням).

  • Выделите ячейку с первым днем месяца, откройте меню «Формат» и выберите пункт «Пользовательские дата и время».
  • Удалите текущие значения, кликнув по каждому из них левой кнопкой мыши.
  • При помощи вариантов снизу отыщите подходящие значения, которые бы включали день недели и число. Их можно поменять местами или редактировать, чтобы полные названия поменялись на сокращенные.
  • Примените изменения и вернитесь к таблице. Как видно, настройки вступили в силу и дни недели отображаются в более понятном формате.

Еще раз о кривизне РАЗНДАТ()

Найдем разницу дат 16. 2015 и 30. Функция

РАЗНДАТ()

с параметрами md и ym подсчитает, что разница составляет 1 месяц и 14 дней. Так ли это на самом деле?

Имея формулу, эквивалентную

РАЗНДАТ()

, можно понять ход вычисления. Очевидно, что в нашем случае количество полных месяцев между датами = 1, т. весь февраль. Для вычисления дней, функция находит количество дней в предыдущем месяце относительно конечной даты, т. 28 (конечная дата принадлежит марту, предыдущий месяц — февраль, а в 2015г. в феврале было 28 дней). После этого отнимает день начала и прибавляет день конечной даты =

ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B6;-1);0))-ДЕНЬ(A6)+ДЕНЬ(B6)

, т. 28-30+16=14. На наш взгляд, между датами все же 1 полный месяц и все дни марта, т. е 16 дней, а не 14! Эта ошибка проявляется, когда в предыдущем месяце относительно конечной даты, дней меньше, чем дней начальной даты. Как выйти из этой ситуации?

Модифицируем формулу для расчета дней разницы без учета месяцев и лет:

=

ЕСЛИ(ДЕНЬ(A18)>ДЕНЬ(B18);ЕСЛИ((ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))

При применении новой функции необходимо учитывать, что разница в днях будет одинаковой для нескольких начальных дат (см. рисунок выше, даты 28-31. 2015). В остальных случаях формулы эквивалентны. Какую формулу применять? Это решать пользователю в зависимости от условия задачи.

Разница в днях без учета месяцев и лет («md»)

Формула

=РАЗНДАТ(A2;B2;»md»)

вернет количество дней между двумя датами без учета месяцев и лет. Использовать функцию

РАЗНДАТ()

с этим аргументом не рекомендуется (см. примеры ниже).

Пример1:

начальная_дата

01. 2007,

конечная_дата

06. 2009

Результат1:

5 (дней), т. сравниваются конечная дата 06. 2009 и модифицированная начальная дата 01. 2009

(год и месяц начальной даты заменяется годом и месяцем конечной даты, т. 01

меньше

чем 06)

Пример2:

начальная_дата

28. 2007,

конечная_дата

28. 2009

Результат2:

0, т. сравниваются конечная дата 28. 2009 и модифицированная начальная дата 28. 2009

(год и месяц начальной даты заменяется годом и месяцем конечной даты)

Пример3:

начальная_дата

28. 2009,

конечная_дата

01. 2009

Результат3:

4 (дня) — совершенно непонятный и НЕПРАВИЛЬНЫЙ результат. Ответ должен быть =1. Более того, результат вычисления зависит от версии EXCEL.

Версия EXCEL 2007 с SP3:

Результат – 143 дня! Больше чем дней в месяце!

Версия EXCEL 2007:

Разница между 28. 2009 и 01. 2009 – 4 дня!

Причем в EXCEL 2003 с SP3 формула возвращает верный результат 1 день. Для значений 31. 2009 и 01. 2010 результат вообще отрицательный (-2 дня)!

Не советую использовать формулу с вышеуказанным значением аргумента. Формула может быть заменена альтернативным выражением:

=ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2); ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2); ДЕНЬ(B2)-ДЕНЬ(A2))

Данная формула лишь эквивалетное (в большинстве случаев) выражение для

РАЗНДАТ()

с параметром md. О корректности этой формуле читайте в разделе «Еще раз о кривизне РАЗНДАТ()» ниже.

Разница в полных годах («y»)

Формула

=РАЗНДАТ(A2;B2;»y»)

вернет количество полных лет между двумя датами.

Пример1:

начальная_дата

01. 2007,

конечная_дата

01. 2009

Результат:

2 (года)

Пример2:

начальная_дата

01. 2007,

конечная_дата

01. 2009

Результат:

1 (год)

Подробнее читайте в статье

Полный возраст или стаж.

Формула может быть заменена альтернативным выражением:

=ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))<=B2; ГОД(B2)-ГОД(A2);ГОД(B2)-ГОД(A2)-1)

Таблица с датами отстоящими на месяц (формулы)

Для составления таблицы, состоящей из последовательных дат отстоящих на 1 месяц можно воспользоваться формулой

=ДАТАМЕС($B$18;СТРОКА()-СТРОКА($A$20))

и

таблицей в формате Excel2007

(не обязательно), см. файл примера.

Также в файле примера приведены формулы для

последовательностей дат

— последних дней месяцев, а также последних дней кварталов.

Разница в днях («d»)

Формула

=РАЗНДАТ(A2;B2;»d»)

вернет простую разницу в днях между двумя датами.

Пример1:

начальная_дата

25. 2007,

конечная_дата

26. 2007

Результат:

1 (день).

Этот пример показыват, что при подсчете стажа необходимо использовать функцию

РАЗНДАТ()

с осторожностью. Очевидно, что если сотрудник работал 25 и 26 февраля, то отработал он 2 дня, а не 1. То же относится и к расчету полных месяцев (см. ниже).

Пример2:

начальная_дата

01. 2007,

конечная_дата

01. 2007

Результат:

28 (дней)

Пример3:

начальная_дата

28. 2008,

конечная_дата

01. 2008

Результат:

2 (дня), т. 2008 год — високосный

Эта формула может быть заменена простым выражением

=ЦЕЛОЕ(B2)-ЦЕЛОЕ(A2). Функция

ЦЕЛОЕ()

округляет значение до меньшего целого и использована для того случая, если исходные даты введены вместе с временем суток (

РАЗНДАТ()

игнорирует время, т. дробную часть числа, см. статью

Как Excel хранит дату и время

).

Примечание

: Если интересуют только рабочие дни, то к

оличество рабочих дней

между двумя датами можно посчитать по формуле

=ЧИСТРАБДНИ(B2;A2)

Создание листа с данными

При использовании динамического календаря в Гугл Таблицах понадобится постоянно обращаться к списку месяцев и чисел. Для этого придется создать вспомогательный лист, на заполнение которого уйдет не так много времени. Выполните по очереди каждое действие, описанное ниже, после чего переходите к следующему шагу.

  • Откройте пустую таблицу, где хотите разработать динамический календарь. Нажмите кнопку с плюсом, чтобы добавить новый лист.
  • Задайте для него любое название, чтобы в будущем не запутаться с предназначением листа.
  • По порядку в первом столбце самостоятельно напишите список всех двенадцати месяцев.
  • После этого выберите первую ячейку напротив «Январь», щелкните по ней правой кнопкой мыши и выберите пункт «Настроить проверку данных».
  • Из списка «Правила» выберите пункт «Дата».
  • После этого можно сохранить изменения.
  • Дважды кликните по данной ячейке левой кнопкой мыши, после появления календаря нажмите по первому числу месяца.
  • Теперь в клетке отображается первое число первого месяца текущего года.
  • Активируйте ячейку напротив «Февраль» и введите формулу =КОНМЕСЯЦА(B1;0)+1, после чего нажмите Enter. Эта формула вычисляет последний день месяца и прибавляет к нему единицу, что позволяет в самой клетке отображать первое число нового месяца. Выполнение данного процесса может показаться непонятным, однако это самый простой метод настроить ячейку для дальнейшего растягивания, чтобы не использовать проверку данных каждый раз.
  • Теперь, начиная с первого дня февраля, растяните формулу до последнего месяца.
  • Вы видите, что числа остаются первыми, меняются только месяцы. Если дважды кликнуть по любой ячейке, откроется календарь и дополнительно покажется и текущий день недели.

Это был весь подготовительный этап со вспомогательным листом. Теперь можете переключиться на основной и приступать к следующему шагу, в котором описан главный процесс создания динамического календаря.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Таблица с датами отстоящими на месяц (Прогрессия)

Заполнить таблицу датами, отстоящими на 1, 2 или 3,. месяцев можно также с помощью инструмента

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

Прогрессия.

Разница в днях без учета лет («yd»)

Формула

=РАЗНДАТ(A2;B2;»yd»)

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

Результат, возвращаемый формулой

=РАЗНДАТ(A2;B2;»yd»)

зависит от версии EXCEL.

Формула может быть заменена альтернативным выражением:

=ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))>B2; B2-ДАТА(ГОД(B2)-1;МЕСЯЦ(A2);ДЕНЬ(A2)); B2-ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2)))

Разница в полных месяцах без учета лет («ym»)

Формула

=РАЗНДАТ(A2;B2;»ym»)

вернет количество полных месяцев между двумя датами без учета лет (см. примеры ниже).

Пример1:

начальная_дата

01. 2007,

конечная_дата

01. 2009

Результат:

1 (месяц), т. сравниваются конечная дата 01. 2009 и модифицированная начальная дата 01. 2009

(год начальной даты заменяется годом конечной даты, т. 02

меньше

чем 01. 03)

Пример2:

начальная_дата

01. 2007,

конечная_дата

01. 2009

Результат:

11 (месяцев), т. сравниваются конечная дата 01. 2009 и модифицированная начальная дата 01. 2008

(год начальной даты заменяется годом конечной даты

за вычетом 1 года

, т. 04

больше

чем 01. 03)

Вся эта вакханалия нужна, например, для подсчета сколько полных дней, месяцев и лет прошло с определенной даты до сегодняшнего дня. Подробнее читайте в одноименной статье

Сколько лет, месяцев, дней прошло с конкретной даты.

Формула может быть заменена альтернативным выражением:

=ОСТАТ(C7;12)

В ячейке

С7

должна содержаться разница в полных месяцах (см.

Настройка подсветки выходных

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

  • Выберите строки ниже, где будете вводить свои данные. У меня это отчеты по дням для каждого сотрудника. Обязательно убедитесь в том, что выделение коснулось всех дней месяца. После этого сделайте клик ПКМ и выберите «Условное форматирование».
  • Из списка укажите условие форматирования «Ваша формула».
  • Введите =or(weekday(B$1)=7;). B$1 замените на то название клетки, в которой содержится первый день месяца.
  • После точки с запятой идет вторая часть формулы weekday(B$1)=1, а в целом строка должна выглядеть как =or(weekday(B$1)=7;weekday(B$1)=1). Таким образом, условное форматирование будет активно только в том случае, если день недели суббота или воскресенье.
  • Цвет подсветки ячейки можете выбрать любой, после чего сохраните изменения.
  • Теперь обратите внимание на то, что клетки с выходными днями начали подсвечиваться выбранным цветом. Измените месяц и проверьте динамическое форматирование, которое тоже должно работать корректно.

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