Функции даты и времени

Функции даты и времени

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

Excel поддерживает две системы дат: 1900 и 1904. По умолчанию используется система дат 1900. Чтобы воспользоваться системой дат 1904, необходимо выбрать команду Параметры в меню Сервис, а затем на вкладке Вычисления установить опцию Система дат 1904.

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

Функция ВРЕМЗНАЧ

Результат: Числовой формат для времени суток, представленного аргументом врeмя_как_текст. Время сугок в числовом формате — это десятичная дробь в интервале от 0 до 0,99999999, представляющая время суток от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера). Функция ВРЕМЗНАЧ используется для преобразования времени суток, представленного в виде текста, в числовой формат времени суток.

• время_как_текст — текстовая строка, содержащая значение времени суток в любом формате, допустимом в Excel. Информация о дате в аргументе время_как_текст игнорируется.

Функция ВРЕМЯ

Результат: Значение времени в числовом формате, соответствующее введенным аргументам. Это десятичная дробь в интервале от 0 до 0,99999999, представляющая время суток от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

• часы — число от 1 до 24 (количество часов);
• минуты — число от 1 до 59 (количество минут);
• секунды — число от 1 до 59 (количество секунд).

Функция ГОД

Результат: Год, соответствующий заданному сериальному числу. Год определяется как целое в интервале от 1900 до 9999.

• дата_в_числовом_формате — сериальное число. Данный аргумент можно задать как текст, например «19/Мар/1955», или «19-3-55». При этом текст автоматически преобразуется в дату в числовом формате.

Если в панели Вычисления диалогового окна Параметры установлена опция Система дат 1904, в качестве начала отсчета принимается 02/01/1904 вместо 01/01/1900.

Функция ДАТА

Результат: Сериальное число (от 0 до 2958456), соответствующее дате в числовом формате (в качестве начала отсчета принимается 01 января 1900 года).

• год — число (год) от 1900 до 9999 (или от 0 до 8099 соответственно);
• месяц — номер месяца в году (если значение аргумента больше 12, то это число делится на 12, частное прибавляется к указанному значению года, а остаток выступает в качестве номера месяца; так, функция ДАТА(98,14,2) возвращает числовой формат даты 2 февраля 1999 года);
• день — номер дня в месяце (если аргумент- день больше числа дней в указанном месяце, то алгоритм вычислений аналогичен описанному для аргумента месяц’, так, функция ДАТА(99,1,35) возвращает числовой формат даты 4 февраля 1999 года).

Функция ДАТАЗНАЧ

Результат: Соответствующее дате сериальное число, представленное в текстовом виде, Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат.

• дата_как_текст — текст, содержащий дату в формате даты Excel. При использовании в Excel истемы дат, принятой по умолчанию для Windows 95/98, аргумент дата_как_текст должен представлять собой дату в диапазоне от 1 января 1900 года до 31 декабря 9999 года. При использовании в Excel системы дат, принятой по умолчанию для Macintosh, аргумент дата_как_текст должен представлять собой дату в диапазоне от 1 января 1904 года до 31 декабря 9999 года. Функция ДАТАЗНАЧ возвращает значение ошибки #ЗНАЧ!, если значение аргумента дата_как_текст выходит за пределы указанных диапазонов. Если в аргументе дата_как_текст опушен год, то функция ДАТАЗНАЧ использует значение текущего года из встроенных часов компьютера. Информация о времени суток в аргументе дата_ как_текст игнорируется.

Функция ДАТАМЕС

Результат: Сериальное число для даты, отстоящей на заданное количество месяцев от начальной.

• нач_дата — сериальное число, соответствующее начальной дате;
• число_месяцев — количество месяцев (может быть положительным или отрицательным).

Функция ДЕНЬ

Результат: Число месяца, соответствующее заданной дате (целое число от 1 до 31).

• дата_в_числовом_формате — значение даты, заданное в виде сериального числа от 0 до 65380 или в виде текста в стандартных Excel-форматах, например «29/12/63» или «15-01-01». При этом текст будет автоматически преобразован в дату в числовом формате.

Функция ДЕНЬНЕД

Результат: День недели, соответствующий заданному сериальному числу. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота).

• дата_в_числовом_форматe — сериальное число; этот аргумент можно задать как текст, например, как «15/Янв/1999», или «04-. . . , 15-99.», при этом текст автоматически преобразуется в значение даты в числовом формате;
• тип — число (1, 2 или 3), определяющее тип отсчета недели (с Вс=1 до Сб=7; с Пн=1 до Вс=7 или с Пн=0 до Вс=6 соответственно).

Функция ДНЕЙ360

Результат: Количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Эта функция используется для расчета платежей, если бухгалтерские операции основываются на двенадцати 30-дневных месяцах.

• нач_дата, кон_цата — две даты, количество дней между которыми требуется определить (аргументы могут быть либо текстовыми строками, в которых используются цифры для задания месяца, дня и года (например «30/01/99» или «30-01-99»), либо датами в числовом формате; если аргумент нач_дата превосходит аргумент кон_дата, то функция ДНЕЙ360 возвращает отрицательное число);
• метод — логическое значение, которое определяет, какой метод, европейский или американский, должен использоваться при вычислениях. Этот аргумент может принимать значения ЛОЖЬ (американский метод; принимается по умолчанию) или ИСТИНА (европейский метод).

Чтобы определить количество дней между двумя датами в нормальном году, следует использовать обычное вычитание. Например, «31/12/99» — «01/01/99» = 364.

Функция ДОЛЯГОДА

Результат: Часть года, соответствующая количеству дней от начальной даты до конечной (продолжительность года принимается за единицу).

• нач_дата, кон_дата — сериальные числа для начальной и конечной дат;
• базис — число от 0 (принимается по умолчанию; означает американский стандарт) до 5, определяющее режим расчета.

Функция КОНМЕСЯЦА

Результат: Сериальное число для даты, которая определяется как последнее число месяца, отстоящего на заданное количество месяцев от начальной даты.

• нач_дата — сериальное число, представляющее начальную дату;
• число_месяцев — количество месяцев (может быть положительным или отрицательным) .

Функция МЕСЯЦ

Результат: Номер месяца, соответствующий заданному сериальному числу. Номер месяца определяется как целое число в интервале от 1 (январь) до 12 (декабрь).

• дата_в_числовом_формате — сериальное число. Данный аргумент можно задать как текст, например «15-4-1999″ или»15-Янв-1999», а не как число. При этом текст автоматически преобразуется в дату в числовом формате. См. функцию ГОД.

Функция МИНУТЫ

Результат: Количество минут (целое число от 0 до 59) в значении времени суток, соответствующем заданному сериальному числу.

• дата_в_числовом_формате — сериальное число. Данный аргумент можно задать как текст, например «16:48:00» или «4:48:00 РМ», а не как число. При этом текст автоматически преобразуется в дату в числовом формате.

Функция НОМНЕДЕЛИ

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

• дата_в_числовом_формате — дата в числовом формате;
• тип — число, которое определяет первый день недели (1 — воскресенье, 2 — понедельник).

Функция РАБДЕНЬ

Результат: Сериальное число для даты, отстоящей на заданное число рабочих дней от начальной даты.

• нач_дата — начальная дата в виде сериального числа;
• количество_дней — число дней;
• праздники — задает массив сериальных чисел, которые соответствуют праздничным дням.

Функция СЕГОДНЯ

Результат: Сериальное число текущей даты.

Функция СЕКУНДЫ

Результат: Количество секунд (целое число от 0 до 59) в значении времени, соответствующем заданному сериальному числу. Функция СЕКУНДЫ используется для того, чтобы получить значение количества секунд времени суток, заданного датой в числовом формате.

• дата_в_числовом_формате — сериальное число. Этот аргумент можно задать как текст, например «16:48:23» или «4:48:47 РМ», а не как число. При этом текст автоматически преобразуется в дату в числовом формате.

Функция ТДАТА

Результат: Сериальное число, соответствующее текущему времени суток и текущей дате (внутренних часов операционной системы).

См. также функцию ГОД; результат функции актуализируется только при новом вычислении таблицы.

Функция ЧАС

Результат: Количество часов, соответствующее заданному сериальному числу. Определяется как целое в интервале от 0 (12:00 AM) до 23 (11:00 РМ).

Создание календаря в Excel на год

Для тех, кто просто хочет скачать календарь в Excel на текущий год, на картинке ниже показан как он выглядит. Красным отмечены праздничные дни, розовым — выходные. Текущий день, также автоматически выделяется заливкой.

Добавил календарь на 2021 год с праздниками и выходными в соответствии с постановлением правительства.

Пошаговая инструкция создания календаря в Excel

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

Делаем заголовки

В ячейку A1 вбиваем следующую формулу: ="Календарь на " & ГОД(СЕГОДНЯ()) & " год". Функция СЕГОДНЯ() возвращает текущую дату (мы часто будем ей пользоваться). Соответственно связка функций ГОД(СЕГОДНЯ()) возвратит нам текущий год.

Теперь создадим заголовки месяцев. В ячейку где стоит Январь запишем следующую формулу =ДАТА(ГОД(СЕГОДНЯ());1;1). Эта функция возвращает первое января текущего года (01.01.2015 в моем случае). Применим к этой ячейке такой формат, чтобы отображалось только название месяца. Для этого откроем формат ячеек сочетанием клавиш Ctrl + 1 и выберем соответствующий формат (см. рисунок).

Для создания заголовков последующих месяцев будем использовать еще одну очень полезную функцию =ДАТАМЕС(B3;1). Эта функция возвращает дату отстоящую от указанной на заданное число месяцев (в нашем случае на один). Таким образом, у нас получается следующая картина (дополнительно я добавил немного форматирования):

Проставляем числа

Для автоматического проставления чисел в календаре мы будем использовать всего одну формулу — формулу массива. На вид звучит страшновато, но если разобраться — все легко и просто. Выделяем ячейки B5:H10 и вводим следующую формулу (не спешим жать Enter):
=ДАТА(ГОД(B3);МЕСЯЦ(B3);1-1)-(ДЕНЬНЕД(ДАТА(ГОД(B3);МЕСЯЦ(B3);1-1))-1)+<0:1:2:3:4:5:6>*7+

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

Аналогичную операцию проводим с другими месяцами или просто копируем диапазон дат января в следующие месяца. Получаем следующую картину:

Форматируем

Отформатируем ячейки таким образом, чтобы они содержали только числа (а не полностью даты). Выделяем даты, открываем формат ячеек (Ctrl+1) и вводим вручную следующий формат:

Календарь принимает все более привычный вид.

Добавляем условного форматирования

Сейчас в январе календарь содержит даты 29, 30, 31 декабря, а также часть дат февраля. Давайте немного скроем эти числа. Для этого будем использовать условное форматирование. Выделяем ячейки B5:H10 и создаем правило условного форматирования. Форматироваться будут ячейки, для которых следующая формула будет являться ИСТИНОЙ:
=И(МЕСЯЦ(B5)<>1+3*(ЧАСТНОЕ(СТРОКА(B5)-5;9))+ЧАСТНОЕ(СТОЛБЕЦ(B5);9))

В качестве формата я выбрал серый текст шрифта и получил следующую картину:

Автоматически выделяем текущую дату в календаре

Для этого создаем еще одно правило условного форматирования. Ячейка будет форматироваться только в том случае, если ее значение будет равно текущей дате (=СЕГОДНЯ()):

Выделим выходные дни в календаре Excel

На этот раз обойдемся обычной заливкой ячеек. Выбирайте на свой вкус. У меня получилось так:

Добавляем праздничные дни

Создадим новый лист Excel (Праздники) и добавим на него умную таблицу со столбцом Праздничные дни. В этот столбец занесем все праздничные дни. Я это уже сделал, можете спокойно копировать и использовать.

Вернемся в наш календарь и создадим еще одно правило условного форматирования. Формула условного форматирования будет проверять является ли дата праздником. И в случае если формула вернет ИСТИНА ячейка будет форматироваться. Вот эта формула:
=НЕ(ЕОШИБКА(ПОИСКПОЗ(B5;ДВССЫЛ("Праздники[Праздничные дни]");0))). Стоит отметить, что при добавлении или изменении праздничных дней, календарь будет автоматически форматироваться.

Осталось скопировать ячейки B5:H10 в другие месяца и наш автоматический календарь готов и может служить нам не один год.

Изменение цветовой гаммы календаря Excel

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

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

ГОД Формула в Excel

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

Синтаксис:

Аргумент в формуле ГОД

• serial_number : серийный номер представляет дату.

объяснение

Если вы не уверены, как серийный номер представляет дату? тогда вы должны понимать, как дата сохраняет в Excel. С 1 января 1900 года Excel присваивает серийный номер каждой дате. На 1 января 1900 года один, на 2 января 1900 года два и так далее.

Например, если мы дадим серийный номер 1 в формуле года, он вернет 1900 согласно приведенной ниже логике.

Обратите внимание на формулу, которую мы только что ввели в серийный номер 1, и он вернул 1900, который является годом, связанным с этой датой. Мы увидим еще несколько примеров использования функции YEAR.

Как использовать формулу Excel YEAR в Excel?

Формула Excel YEAR очень проста и легка. Давайте разберемся, как использовать формулу Excel YEAR с несколькими примерами.

Вы можете скачать этот шаблон Formula Excel ГОД здесь — Шаблон Formula Excel ГОД

Пример № 1 — формула ГОДА с функцией ДАТА

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

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

Сначала запустите ФОРМУЛУ ГОДА, как показано ниже.

Введите DATE Formula вместо серийного номера, как показано ниже.

Теперь нам нужно ввести год, месяц и день в формулу DATE.

Нажмите клавишу ввода.

Поскольку мы ввели Год как 2019 в формуле даты, следовательно, он возвращает год как 2019.

Пример №2 — формула года с функцией СЕГОДНЯ

Если мы хотим получить данные текущего года с помощью функции «Сегодня», мы можем получить ее очень легко. Выполните следующие шаги.

Начните формулу года, как показано ниже.

Нам нужно получить 4-значный номер года с сегодняшнего дня, следовательно, сейчас вводится функция TODAY.

Нажмите Enter после закрытия функции СЕГОДНЯ. Он вернет номер текущего года, как показано ниже.

Пример № 3 — Сравнение двух дат

Сравнивая две даты, принадлежат ли они одному и тому же месяцу и году или нет. Рассмотрим два набора разных дат, как на скриншоте ниже.

Теперь с помощью функции year и month мы проверим, сколько дней в одной строке связано с одним и тем же месяцем и одним и тем же годом.

Начните Формулу с ГОДА сначала, как показано ниже.

Выберите первую ячейку даты из набора 1.

Теперь добавьте & Symbol и добавьте функцию MONTH для той же ячейки, как показано ниже.

До настоящего времени мы объединили год и месяц первой даты из набора 1. Аналогично, сделаем также для первой даты из набора 2.

Теперь он вернется, если совпадают месяц и год обеих дат. Если год или месяц кому-либо не соответствует критериям, он вернет значение False .

Перетащите ту же формулу в другие ячейки.

Пример № 4 — Найти год високосный год или нет

Возьмите несколько дат из разных лет, как показано на скриншоте ниже.

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

Запустите формулу с функцией МЕСЯЦ.

Вместо ввода серийного номера введите функцию DATE, как показано на скриншоте ниже.

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

Для ввода аргумента «МЕСЯЦ» 2 и ввода аргумента «ДЕНЬ» 29.

Закройте скобки DATE и MONTH, затем введите «=» 2, как показано ниже.

Нажмите клавишу ввода.

Перетащите ту же формулу в другие ячейки.

Вывод

Из всех дат, которые мы хотим узнать, имеет ли февраль 29 дней или нет для этого года, февраль является вторым месяцем, поэтому функция формулы «Месяц» должна возвращать 2. Она вернет 2 только тогда, когда функция «Дата» верна, что означает, что если в этом году февраль имеет 28 дней, то есть он переместится на 1 марта и вернет 3, поэтому результат формулы месяца не будет совпадать с числом 2.

Когда у февраля 29 дней, он вернет 2, что будет совпадать с правой частью 2, следовательно, вернет «True».

Это один из способов, иначе мы можем найти по-разному, как показано ниже.

1. Левая часть вычисляет дату после 28 февраля, если она равна правой части 29, то это високосный год.

2. Левая сторона вычисляет дату до 1 марта, если она равна правой части 29, то это високосный год.

Что нужно помнить о формуле года в Excel

• Формула года полезна, когда мы запрашиваем детали одного года из большого набора данных.
• Никогда не вводите дату непосредственно в функцию Year, поскольку она будет считаться текстом и будет возвращать ошибку #NAME, поскольку она будет рассматриваться только как текст.
• Если вы хотите использовать функцию год непосредственно, воспользуйтесь функцией Date и используйте, то вы не получите никакой ошибки.
• Другой способ использования — преобразовать дату в числовой формат и использовать это число в серийном номере, чтобы получить год из 4 цифр.
• Если вы взяли год до 1900 года, вы получите сообщение об ошибке # ЗНАЧЕНИЕ, поэтому убедитесь, что вы используете функцию ГОД в течение лет после 1900 года.
• Убедитесь, что формат даты правильный, потому что если вы используете значение больше 12 в месте месяца или больше 31 в месте дня, это вызовет ошибку.

Рекомендуемые статьи

Это руководство по формуле ГОД в Excel. Здесь мы обсуждаем, как использовать формулу YEAR в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Excel works!

Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel — это классика. Регулярно пользуюсь ими и вам советую! Если вам нужно быстро сгруппировать данные по месяцам, сделать график, какой день недели самый загруженный или в каком году были наибольшие продажи, вам необходимо знать об этих функциях. Обо всем по порядку.

Функция ГОД в Excel

Пример. Дана таблица данных по продажам (см. выше), необходимо найти величину продаж по годам.

В отдельном столбце записываем функцию ГОД, как показано на картинке выше. У этой функции только один аргумент — дата в числовом формате (именно в числовом формате — если формула не работает проверьте верно ли задана дата). Соответственно результатом работы формулы будет номер года в числовом значении.

После чего в столбце I рассчитываем сумму по годам при помощи СУММЕСЛИ

Функция МЕСЯЦ в Excel

Теперь, к примеру, нам нужно отобрать только данные за март месяц. Ищем номер месяца в столбце F. Теперь функция Месяц в эксель возвратит нам номер месяца в году. Если июль, то результатом вычисления будет число 7.

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

Делается это несложно при помощи автофильтра

Функция ДЕНЬ в Excel

Функция ДЕНЬ работает точно так же как и две предыдущих — она считает порядковый номер дня этой даты в месяце. Т.е. если сегодня 22.07, число 22 получаем именно функцией ДЕНЬ:

Как мне кажется, даже поинтереснее будет функция ДЕНЬНЕД. Она находит номер дня недели из числа и выводит число.

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

После чего мы можем построить график — в какой день недели было больше всего продаж.

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

Для функции ДЕНЬНЕД есть небольшая хитрость — это дополнительный аргумент, если оставить это поле пустым, то дни недели будут считаться по американской системе (воскресенье первый день недели), а если поставить 2, то по европейской — к которой мы привыкли.

Все 3 функции и примеры графиков в одном месте можно скачать в файле.

Статьи, которые будут полезны вместе с этой статьей. Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel:

• Как группировать данные по дням в сводной таблице
• Как посчитать количество дней между датами, в том числе количество рабочих дней
• Функция СЕГОДНЯ и все, что с ней связано

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

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

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

Исходные данные

Возьмем в качестве примера такие данные.

Значения в первом столбце нам не важны, используем их в данном примере только как ориентиры. Потому для нас интерес представляет столбец «C» содержащий даты. Подсчет будет осуществляться по датам, так что будут отражать/представлять/указывать даты не важно. Например, пусть в первом столбце ФИО посетителей, во втором столбце даты их посещения.

Количество значений/элементом меду двумя датами, будем находить при помощи двух функции Excel, «СЧЕТЕСЛИМН» и «ДАТА». Однако использование функции «ДАТА» будет зависеть от контекста подсчёта.

Так, например, для подсчета количества элементов в рамках года или полугодия, использование функции «ДАТА» необходимо. Однако при подсчёте количества элементов за квартал или месяц можно использовать только ссылки на значения в ячейках. Возможен и комбинированный вариант применения, всё зависит от представления данных в таблице.

Подсчёт данных в пределах года

Разберем как работает формула:

Отличие функции «СЧЕТЕСЛИМН» от «СЧЕТЕСЛИ» заключается в возможности указания нескольких условий фильтрации. Как следствие множественная фильтрация, обеспечивает возможность подсчета значений в диапазоне.

$C$3:$C$18 – указание на диапазон ячеек, в котором будет производится подсчет. В примере задано условие фильтрации в пределах 2016 и 2017 года, в отдельности. Вводя формулу в ячейку F3 мы подсчитываем результат для 2016 года, что указано в ячейке E3. Для расчета за 2017 год, необходимо ввести формулу в ячейку F4, самый простой способ сделать это, скопировать ее из ячейки F3. В примере используются абсолютные ссылки на ячейки, потому при копировании не произойдет сдвиг ячеек, в которых осуществляется подсчет значений.

После указания диапазона, указывается логическое условие. В формуле используется строковое определение условия с добавлением к нему результата функции «ДАТА». Начало диапазона задается как «>=»&ДАТА(E3;1;1), конец диапазона как «<=»&ДАТА(E3;12;31). Знак «&» в формуле используется для соединения двух строковых значений, условия и результата функции «ДАТА».

Для того чтобы определить две даты мы используем функцию «ДАТА», первое значение функции ссылается на ячейку с данными, в нашем случае ячейки содержат года «2016» и «2017». Второй параметр функции определяет порядковый номер месяца, третий число месяца.

Таким образом результатом вычисления функции «ДАТА» в условии получаются значения первого дня года и последнего дня года. ДАТА(E3;12;31) – E3 ссылка на ячейку содержащую значение 2016. Второй параметр функции номер месяца, а третий параметр число. Соответственно результатом вычисления функции будет 31 декабря 2016 года.

В результате выполнения функций логические условия будут читаться следующим образом, например, для 2016 года: >=01.01.2016 – больше или равно 1 января 2016

<=31.12.2016 – меньше или равно 31 декабря 2016

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

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

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

Дату начала месяца определить легко, указав в функции «ДАТА» ссылки на год и месяц, а день задать константой — цифрой 1. Однако же, последний день месяца зависит от месяца, в феврале последний день может быть 28, и 29, в остальные месяцы 30 или 31 число. Поэтому для определения последнего дня лучше всего использовать функцию «КОНМЕСЯЦА». Первым параметром функции будет результат вычисления функции «ДАТА», а значение второго параметра будет равно 0.

Таким образом, формула будет иметь вид:

Что соответствует описанию: подсчитать все значения в диапазоне $C$3:$C$18. Удовлетворяющие условию фильтра: больше или равно 1 числу месяца, номер которого указан в ячейке F7, год которого указан в ячейке E7. Притом значение должно быть меньше или равно последнему дню месяца, первое число которого вычисляется функцией и соответствует первому числу месяца, номер которого указан в ячейке F7, год указан в ячейке E7.

Написать формулу для подсчета значений между двумя датами, еще проще. Нет необходимости использовать дополнительные функции, такие как «ДАТА» и «КОНЦМЕСЯЦА». Достаточно в условиях указать ссылки на ячейки, содержащие начальную и конечную дату диапазона.

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