Функция КОНМЕСЯЦА вычисляет последний день месяца в Excel

Получить месяц из даты с помощью функции ТЕКСТ в Excel

Синтаксис будет следующий

ТЕКСТ(значение, формат)

Значение это ссылка на ячейку с датой
Формат — для получения месяцев необходимо использовать заглавную букву «М». Причем от ее количества будет зависеть формат отображения (Первая буква месяца, Полное название месяца, короткое название, в виде двойной цифры и одной цифры)
Наглядно это можно посмотреть на скриншоте.

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

=ТЕКСТ(A2;"ММММ") 
После этого можно быстро посчитать сумму сделок по месяцам с помощью функции СУММЕСЛИ

Функция “ВЫБОР”.

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

В этой ситуации вам поможет функция ВЫБОР и уже пройденная нами функция МЕСЯЦ. Построим формулу. Для этого нам необходимо указать пользовательское имя для всех 12 месяцев в функции и использовать функцию месяца, чтобы получить номер месяца из даты.

=ВЫБОР(МЕСЯЦ(A1);”Январь”;”Фев”;”Март”;”Апр”;”Май”;”Июнь”;”Июль”;”Авг”;”Сен”;”Октябрь”;” Ноябрь”;”декабрь”)

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

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

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

Что и как нужно сделать?

1. Выберите диапазон ячеек или столбца.

2. Нажмите сочетание клавиш Ctrl + 1.

3. От опции формата, перейдите к «все форматы.

4. Теперь в строке ввода «Тип» введите «ММ», «МММ» или «ММММММ».

Нажмите ОК.

ВАЖНО!

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

Сравнение функций ДНИ и ДНЕЙ360 в Excel

Пример 2. В банк сделан депозит на сумму 100000 рублей. Условия договора: ставка – 17%, дата оформления – 31.08.2012, капитализация – ежедневно. Определить, какая сумма была накоплена на текущий период. Найти отдельно искомые значения с использованием функций ДНИ и ДНЕЙ360. Имеются ли различия в полученных значениях?

Вид таблицы данных:

Для расчета суммы депозита с ежедневной капитализацией используется формула S=P*(1+%/N)*T, где P – начальная сумма, N – число дней в году, T – число дней между датой оформления и текущим моментом.

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

=B2*(1+(B3/365)*ДНИ(СЕГОДНЯ();B4))

=B2*(1+(B3/360)*ДНЕЙ360(B4;СЕГОДНЯ()))

Примечание:

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

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

Автоматизированный график дней рождений сотрудников в Excel

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

Исходная таблица:

Для вычислений используем следующую формулу:

Функция РАЗНДАТ вычисляет разницу двух указанных дат и представляет полученное значение в зависимости от вида третьего аргумента, используемого для форматирования результатов. Например, для получения количества лет используется символ “y”, соответствующий первой букве слова «year» (год), “d” – для расчета количества дней (days – дни).

Выражение ДАТА(ГОД(B3)+РАЗНДАТ(B3;СЕГОДНЯ();”y”)+1;МЕСЯЦ(B3);ДЕНЬ(B3)) определяет дату следующего дня рождения сотрудника. Таким образом функция РАЗНДАТ находит количество дней между датой на сегодняшний день и датой следующего дня рождения и возвращает количество дней (“d”).

Результаты вычислений для остальных сотрудников:

Для отображения записи о том, что день рождения сотрудника сегодня, используем следующую формулу:

Данная формула является немного модифицированным вариантом предыдущей формулы. В частности, выполняется проверка двух условий: И(МЕСЯЦ(B13)=МЕСЯЦ(СЕГОДНЯ());ДЕНЬ(СЕГОДНЯ())=ДЕНЬ(B13)), то есть, соответствуют ли номера месяцев и дней в дате рождения в сравнении с текущей датой. Если результат вычислений – ИСТИНА, будет возвращена текстовая строка «ДР», если ЛОЖЬ – производится расчет количества дней до следующего дня рождения.

Например, произведем расчет для сотрудника, у которого сегодня день рождения:

Анализ дебиторской задолженности до текущей даты в Excel

Предприятию был выставлен долг со следующими условиями:

  1. За каждый день просрочки начисляются штрафы по ставке 5% от текущей суммы долга плюс начисленные проценты в день.
  2. По истечению 20-дневного срока с момента первого начисления штрафа будет проведено слушание в суде, проценты начисляться не будут.

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

Исходная таблица данных:

Для расчета текущей задолженности используем формулу:

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

В качестве показателя степени используется выражение ДНИ(СЕГОДНЯ();B4), которое определяет количество прошедших дней от даты выставления долга до сегодняшней даты.

Поскольку сумма задолженности будет расти на протяжении 20 дней, используем следующий вариант записи:

Таким образом, была добавлена проверка условия, прошло ли 20 и более дней с момента выставления долга. Если да, сумма долга будет фиксированной и рассчитана по формуле B2*СТЕПЕНЬ((1+B3);20).

Результат вычислений:

Какой день недели для последнего дня месяца?

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

Формула состоит из двух частей, разделенных соединяющим оператором амперсантом (&” – “&):

  1. В первой части функция ТЕКСТ преобразует числовое значение, которое возвращает функция ДАТА в формат даты. Он указано во втором аргументе функции – “дд.мм.гг”
  2. Во второй части функция ТЕКСТ определяет день недели для исходной даты в первом ее аргументе, с помощью формата ячеек “дддд” указанном во втором аргументе.

Как определить високосный год или нет в Excel?

Чтобы определить високосный год или нет в Excel существует множество решений. Вот несколько из них:

При работе с датами особую сложность предоставляет тот факт, что раз в 4-е года количество дней в году составляет 366, тогда как в обычных годах 365. Поэтому иногда приходится воспользоваться вышеописанными примерами и формулами для различных вычислений в Excel.

Как преобразовать название месяца в порядковый номер

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

Пример1. Формула первая:

=МЕСЯЦ(ДАТАЗНАЧ(A2&”1″))

Значение Декабрь1 Excel автоматически преобразует в дату дек.01. Далее функция ДАТАЗНАЧ преобразует дату в число после с помощью функции МЕСЯЦ мы получаем порядковый номер в году для текущего названия месяца. По сути формулу можно упростить:

=МЕСЯЦ(A2&”1″)

Пример2. Формула вторая:

=ВПР(A2;$G$1:$H$12;2;0)

Пример3. Формула третья:

Наиболее оптимальным вариантом является лаконичная формула =МЕСЯЦ(A2&”1″). В ней не нужно резервировать названия месяцев и присваивать им соответственные порядковые номера. К тому же в ней используется всего лишь одна функция.

Синтаксис функции СЕГОДНЯ()

У функции СЕГОДНЯ() нет аргументов (их нет сегодня и вряд ли они появятся завтра :)

Как определить последнее воскресенье мая месяца в Excel

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

  1. Каким днем недели является последний день мая месяца текущего года?
  2. Какой номер дня соответствует последнему воскресению мая месяца в текущем году?

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

Для нахождения последнего дня мая месяца для текущего года используем следующую формулу:

Функция ТЕКСТ используется для преобразования данных (вывода текстового представления дня недели). Функция ДЕНЬНЕД возвращает числовое значение дня недели (от 1 до 7). Функция КОНМЕСЯЦА вычисляет дату последнего дня текущего месяца (аргументы СЕГОДНЯ и 0 соответственно).

Результат вычислений:

Для нахождения дня, соответствующего последнему воскресению в мае месяце текущего года используем формулу:

Полученный результат:

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

Результат вычислений:

Способ 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 в двух местах. Если её заменить на адрес той ячейки, в которой находится дата в вашем конкретном случае, то можно просто вбить данное выражение в любой элемент листа без помощи Мастера функций.

Как записать месяц прописью?

Как и в примере выше воспользуемся функцией ТЕКСТ. Формат «ММММ» даст нам полную запись месяца (в именительном падеже):

Ещё одним способом является совместное применение функций МЕСЯЦ (в английской версии MONTH) и ВЫБОР (английский вариант CHOOSE).
Месяц вернёт нам порядковый номер месяца указанной даты (от 1 до 12), а ВЫБОР сопоставит числовому значению текстовое (где 1 — январь, 2 — февраль, …, 12 — декабрь):

Если же мы пишем число с месяцем и годом, то зачастую месяц нужно указать в родительном падеже (а не в именительном как в примере выше), например, 2 февраля 2015 года или 14 августа 2012 года и т.п.

Здесь нам опять поможет формат [$-FC19]ММММ, который записывает месяц в дате в родительном падеже, отличие от предыдущего варианта записи только в том, что мы убрали из записи день и год:

Есть и альтернативный вариант. На помощь опять придут уже знакомые функции МЕСЯЦ и ВЫБОР, только тут вместо именительного падежа для месяцев прописываем родительный (меняем окончания):

Источники


  • https://sirexcel.ru/priemi-excel/data-i-vremya/kak-v-excel-poluchit-mesyac-iz-daty/
  • https://zen.yandex.ru/media/id/5a25282b7800192677cc044f/5dea1b66e4fff000adb66297
  • https://exceltable.com/funkcii-excel/funkciya-dney360
  • https://exceltable.com/funkcii-excel/primery-raboty-funkcii-segodnya
  • https://exceltable.com/formuly/posledniy-den-mesyaca
  • https://exceltable.com/funkcii-excel/nazvanie-mesyaca-v-nomer
  • https://excel2.ru/articles/funkciya-segodnya-v-ms-excel-segodnya
  • https://exceltable.com/funkcii-excel/posledniy-den-mesyaca-konmesyaca
  • https://lumpics.ru/formula-for-number-of-days-in-month-in-excel/
  • https://tutorexcel.ru/tekst/data-i-mesyac-propisyu-v-excel/

Рейтинг
( Пока оценок нет )
Понравилась статья? Поделиться с друзьями:
Все об Экселе: формулы, полезные советы и решения