Excel кол во дней месяце. Функции времени год, месяц, день в excel

(Часть I ), а также авторским к нему дополнением (Часть II ). Не следует относиться к материалу серьёзно, а скорее как к разминке для ума, требующей не более чем школьных знаний арифметики и не имеющей практического применения. Всем приятного чтения!

Часть I

Вступление

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

Формализуя Другими словами, необходимо найти функцию f , такую, что значение f(x) для каждого месяца x , представленного числом от 1 до 12, равняется количеству дней в этом месяце. Таблица значений аргумента и функции :

x 1 2 3 4 5 6 7 8 9 10 11 12
f(x) 31 28 31 30 31 30 31 31 30 31 30 31

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

Ответ


Ниже следуют мои шаги по нахождению решения.

Математический аппарат

Сначала бегло освежим в памяти два жизненно необходимых в решении этой задачи оператора: целочисленное деление и остаток от деления.

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

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

Замечу, что остаток от деления имеет равный с делением приоритет.

Основы

Итак, применим наш математический аппарат для получения базовой формулы. В обычном месяце 30 или 31 день, так что мы можем использовать для получения поочерёдно 1 или 0, а затем просто прибавить к этому числу константу:

Получаем таблицу, полужирным выделены корректные значения:
x 1 2 3 4 5 6 7 8 9 10 11 12
f(x) 31 30 31 30 31 30 31 30 31 30 31 30

Неплохое начало! Уже есть правильные значения для января и для месяцев с марта по июль включительно. Февраль - особый случай, и с ним мы разберёмся чуть позже. После июля для оставшихся месяцев порядок получения 0 и 1 должен быть изменён на обратный.
Для этого мы может прибавить к делимому 1:

x 1 2 3 4 5 6 7 8 9 10 11 12
f(x) 30 31 30 31 30 31 30 31 30 31 30 31

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

Наложение маски

Для этого необходима кусочно-заданная функция, но - так как мне это показалось скучным - я задумался о другом пути решения, использующем одну часть функции на одном интервале, другую - на другом.
Полагаю, что проще всего будет найти выражение, равное 1 в одной области применения и 0 - в остальной. Метод, в котором умножая аргумент на выражение мы исключаем его из формулы вне области его применения, я назвал «наложением маски», потому такое поведение подобно некой битовой маске.
Для применения этого метода в последней части нашей функции необходимо найти выражение, равное 1 при , и - так как значения аргумента всегда меньше 16 - для этого прекрасно подходит целочисленное деление на 8.
x 1 2 3 4 5 6 7 8 9 10 11 12
⌊ x ⁄ 8 ⌋ 0 0 0 0 0 0 0 1 1 1 1 1

Теперь с помощью этой маски, используя в делимом выражение вместо 1, мы можем заменить порядок получения 0 и 1 формуле на обратный:

x 1 2 3 4 5 6 7 8 9 10 11 12
f(x) 31 30 31 30 31 30 31 31 30 31 30 31

Эврика! Всё правильно, кроме февраля. Сюрприз-сюрприз.

Февраль

В любом месяце 30 или 31 день, кроме февраля с его 28 (високосный год выходит за рамки этой задачи). На текущий момент по нашей формуле в нём 30 дней, поэтому неплохо бы вычесть выражение, равное 2 при .
Лучшее что мне удалось придумать это , что накладывает маску на все месяцы после февраля:
x 1 2 3 4 5 6 7 8 9 10 11 12
2 mod x 0 0 2 2 2 2 2 2 2 2 2 2

Изменив базовую константу на 28 с добавлением 2 к остальным месяцам получим формулу:

x 1 2 3 4 5 6 7 8 9 10 11 12
f(x) 29 28 31 30 31 30 31 31 30 31 30 31

К сожалению, январь теперь короче на 2 дня. Но, к счастью, получить выражение, которое будет применяться только для первого месяца очень легко: это округлённое вниз обратное к число. Умножив его на 2 получаем окончательную формулу:

x 1 2 3 4 5 6 7 8 9 10 11 12
f(x) 31 28 31 30 31 30 31 31 30 31 30 31

Послесловие

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

Function f(x) { return 28 + (x + Math.floor(x/8)) % 2 + 2 % x + 2 * Math.floor(1/x); }

Часть II

Вступление

В первой части была получена короткая и даже немного изящная формула, основными достоинствами которой являются простота математического аппарата, отсутствие ветвлений и условных выражений, лаконичность. К недостаткам - кроме того, что вы не будете применять её в вашем проекте - можно отнести отсутствие проверки на вискокосный и не високосный год.
Поэтому я поставил перед собой задачу создать функцию f , такую, что значение f(x, y) для каждого месяца x , представленного числом от 1 до 12 и года y , большего 0, равняется количеству дней в месяце x в году y .
Для нетерпеливых под спойлером находится готовый ответ, остальных же прошу следовать за мной.

Ответ

Остаток от деления: mod и ⌊⌋

Для визуальной наглядности договоримся, что в некоторых формулах оператор деления с остатком заменён нижними скобками, там где это показалось мне необходимым:

Високосный год

В високосный год вводится дополнительный день календаря: 29 февраля. Как известно, високосным является год, кратный 4 и не кратный 100, либо кратный 400. Запишем тождественное этому высказыванию выражение:

Для приведения этого выражения в алгебраическое, необходимо применить к результату выражения инъекцию вида:

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

В качестве функции g" можно использовать 1 минус остаток от деления для :

x 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14
g"(x) Infinity 1 0 0 0 0 0 0 0 0 0 0 0 0 0

Легко заметить, что увеличив делимое и делитель на 1 мы получим правильную формулу при :
x 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14
g"(x) 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Таким образом выражение запишем как:


А выражение запишем как:

Применяя этот подход получим следующую функцию g(y) , значением которой будет 1, если год високосный, или 0 в обратном случае:

y 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000
g(y) 0 0 1 0 0 0 1 0 0 0 1
y 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000
g(y) 1 0 0 0 1 0 0 0 1 0 0

Полужирным выделены високосные года.

Напоминаю, что в рамках принятой договорённости оператор получения остатка от деления может быть изображен как mod , так и ⌊⌋.

Наложение маски

В формуле часть является поправкой, прибавляющей 2 дня к январю. Если убрать множитель 2 и в числителе заменить 1 на 2, тогда эта формула будет прибавлять 2 дня к январю и 1 день к февралю, что даёт нам ключ к добавлению дня в високосном году. Для наглядности используем в формуле промежуточное значение g(y) и в качестве y используем 2000 (високосный) и 2001 (не високосный) годы:

Или:

x 1 2 3 4 5 6 7 8 9 10 11 12
f(x, 2000) 31 29 31 30 31 30 31 31 30 31 30 31
f(x, 2001) 31 28 31 30 31 30 31 31 30 31 30 30

Заключение

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

Function f(x, y) { return 28 + ((x + Math.floor(x / 8)) % 2) + 2 % x + Math.floor((1 + (1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1))) / x) + Math.floor(1/x) - Math.floor(((1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1)))/x); }
Пример на C# ideone.com/fANutz .

1 . Я не умею пользоваться подобной мнемоникой, поэтому подсмотрел табличку в интернете.
2 . «Основы», или «Правило С Многими Исключениями», как и большинство правил.
3 . Изначально в римском календаре февраль был последним месяцем года, поэтому есть логика в том, что он короче всех остальных. Также есть логика в добавлении или удалении дня именно в конце года, поэтому его длина является переменной.

Upd. 1:
Альтернативный перевод первой части в

Добрый день уважаемый пользователь!

В этой статье мы поговорим о трёх основных функций дат, это функция ГОД , функция МЕСЯЦ и . Это три составляющие любой даты и теперь рассмотрим, как с ними работать и для чего это нам надо. В первую очередь, эти функции служат для извлечения определенного параметра из имеющейся даты, мы может отдельно изъять год, числовую последовательность месяца или дня. Использование этих функций будет актуально когда, к примеру, нужно будет поделить ваши данные по временным отрезкам по годам, по месяцам или по дням. Часто использование этих функций оправдано при работе со , когда их применение улучшают показатели анализа.

Теперь давайте рассмотрим каждую из функций времени по отдельности:

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

Является возврат года, который будет соответствовать указанной дате. Сам год будет определять функция ГОД как целое число в диапазоне от 1900 года до 9999 года.

Очень простой:

=ГОД(указаная_дата_в_числовом_формате) , где:

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

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

Все даты MS Excel хранит как последовательные числа и именно это позволяет нам работать с ними и использовать в своих вычислениях. По умолчанию первая дата под номером 1, это 1 января 1900 года, а вот, к примеру, 1 января 2018 года будет предоставлен числом 43101, так разница между этими двумя датами будет составлять 43101 день.

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

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

Синтаксис, который использует , простой и незатейливый:

=МЕСЯЦ(ваша_дата_в_числовом_формате) , где:

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

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

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

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

Синтаксис, который использует , так же не отличаеться обилием аргументов:

=ДЕНЬ(ваша_дата_в_числовом_формате) , где:

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

Как и предыдущие функции, функция ДЕНЬ в Excel хранит даты как последовательные числа и значения которые она возвращает, соответствуют датам григорианского календаря.

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

Для работы с датами также вам будет полезно, ознакомится со статьями: « » и « ».

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

"Я хочу жить как бедный человек с деньгами.
"
Пабло Пикассо

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

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

Способ 1: комбинация операторов ДЕНЬ и КОНМЕСЯЦА

Наиболее простым способом решить данную задачу является комбинация операторов ДЕНЬ и КОНМЕСЯЦА .

Функция ДЕНЬ принадлежит к группе операторов «Дата и время» . Она указывает на конкретное число от 1 до 31 . В нашем случае задачей данного оператора будет указание последнего дня месяца при помощи встроенной функции в виде аргумента КОНМЕСЯЦА .

Синтаксис оператора ДЕНЬ следующий:

ДЕНЬ(дата_в_числовом_формате)

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

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

КОНМЕСЯЦА(нач_дата;число_месяцев)

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

Оператор «Число месяцев» указывает на то количество месяцев, на которое следует производить отсчет от заданного числа.

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




Общая формула у нас приняла следующий вид:

ДЕНЬ(КОНМЕСЯЦА(B3;0))

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

Способ 2: автоматическое определение количества дней

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

ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))


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

Способ 3: вычисление количества дней для использования в сложных формулах

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

Нам нужно сделать так, чтобы в ячейке отображалось то количество дней, которое осталось до конца текущего месяца. Как и в предыдущем способе, данный вариант действий не требует открытия Мастера функций . Вы можете просто вбить в ячейку следующее выражение:

ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))-ДЕНЬ(СЕГОДНЯ())

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


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

ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))

А вот во второй части производится вычитание из этого показателя сегодняшнего числа:

ДЕНЬ(СЕГОДНЯ())

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

Способ 4: альтернативная формула

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

  1. Выделяем ячейку для вывода результата и переходим в окно аргументов оператора ДЕНЬ уже привычным для нас способом. Устанавливаем курсор в единственное поле этого окна и кликаем по перевернутому треугольнику слева от строки формул. Переходим в раздел «Другие функции…» .


  2. В окне Мастера функций в группе «Дата и время» выделяем наименование «ДАТА» и жмем на кнопку «OK» .


  3. Запускается окошко оператора ДАТА . Данная функция преобразует дату из обычного формата в числовое значение, которое и должен будет потом обработать оператор ДЕНЬ .

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

    Устанавливаем курсор в поле «Год» . Далее переходим к выбору операторов через знакомый нам треугольник.


  4. Все в той же категории Мастера функций выделяем наименование «ГОД» и щелкаем по кнопке «OK» .


  5. Запускается окно аргументов оператора ГОД . Он определяет год по указанному числу. В единственном поле окна «Дата в числовом формате» указываем ссылку на ячейку, содержащую исходную дату, для которой нужно определить количество дней. После этого не спешим щелкать по кнопке «OK» , а кликаем по наименованию «ДАТА» в строке формул.


  6. Затем мы опять возвращается в окно аргументов ДАТА . Устанавливаем курсор в поле «Месяц» и переходим к выбору функций.


  7. В Мастере функций щелкаем по наименованию «МЕСЯЦ» и жмем на кнопку «OK» .


  8. Запускается окно аргументов функции МЕСЯЦ . Её задачи схожи с предыдущим оператором, только она выводит значение номера месяца. В единственное поле данного окна устанавливаем ту же самую ссылку на исходное число. Затем в строке формул щелкаем по наименованию «ДЕНЬ» .


  9. Возвращаемся в окно аргументов ДЕНЬ . Тут нам предстоит сделать всего один небольшой штрих. В единственное поле окна, в котором уже находятся данные, добавляем в конец формулы выражение «-1» без кавычек, а также ставим «+1» после оператора МЕСЯЦ . После этого кликаем по кнопке «OK» .


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

    ДЕНЬ(ДАТА(ГОД(D3);МЕСЯЦ(D3)+1;1)-1)


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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Вконтакте