Получить месяц из даты с помощью функции ТЕКСТ в Excel
Синтаксис будет следующим:
ТЕКСТ (значение; формат)
Значение — это ссылка на ячейку с датой
Формат: для обозначения месяцев необходимо использовать заглавную букву «M». Кроме того, формат отображения будет зависеть от его количества (первая буква месяца, полное название месяца, краткое название в виде двузначной и одной цифры)
Это хорошо видно на скриншоте.
Так, например, чтобы получить месяц в формате «Январь, Февраль, Март», вам нужно ввести «ММММ».
Формулы будут выглядеть так.
= ТЕКСТ (A2; «ММММ») Затем вы можете быстро рассчитать сумму транзакции в месяц с помощью функции СУММЕСЛИ
Функция “ВЫБОР”.
Теперь предположим, что вы хотите получить собственное название месяца или название на другом языке вместо обычного номера или названия.
В этой ситуации вам помогут функции SELECT и MONTH, которые мы уже прошли. Построим формулу. Для этого вам необходимо указать собственное имя для всех 12 месяцев функции и использовать функцию месяца, чтобы получить номер месяца от даты.
= ВЫБОР (МЕСЯЦ (A1); «Январь»; «Фев»; «Март»; «Апр»; «Май»; «Июнь»; «Июль»; «Август»; «Сентябрь»; «Октябрь»; «Ноябрь» «;»Декабрь”)
Следовательно, когда функция month возвращает номер месяца от даты, функция select вернет произвольное название месяца вместо этой даты.
Пользовательское форматирование.
Если вы не хотите использовать какой-либо из вышеперечисленных методов, вы можете использовать простой метод преобразования даты в месяц, применив настраиваемое форматирование .
Что и как делать?
1. Выберите диапазон ячеек или столбец.
2. Нажмите сочетание клавиш Ctrl + 1.
3. В параметре форматирования выберите «все форматы.
4. Теперь введите «ММ», «МММ» или «ММММММ» в строке ввода «Тип».
Щелкните ОК.
ВАЖНЫЙ!
Поскольку значением является дата, когда вы копируете и вставляете его в другое место в качестве значений, это снова будет та же исходная дата, а не текст. Если кто-нибудь изменит формат, название месяца будет потеряно.
Сравнение функций ДНИ и ДНЕЙ360 в Excel
Пример 2. В банке внесен депозит на сумму 100 000 руб. Условия договора: ставка — 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
Используя таблицу из первого примера, добавьте столбец, в котором будет отображаться количество дней до дня рождения каждого сотрудника. Если у сотрудника сегодня день рождения, просмотрите соответствующую запись.
Исходная таблица:
Для расчетов используем следующую формулу:
Функция DIITDATA вычисляет разницу между двумя указанными датами и представляет результирующее значение в зависимости от типа третьего аргумента, используемого для форматирования результатов. Например, чтобы получить количество лет, используйте символ «y», соответствующий первой букве слова «год», «d» для вычисления количества дней (дней).
Выражение ДАТА (ГОД (B3) + ДАТА (B3; СЕГОДНЯ (); «y») + 1; МЕСЯЦ (B3); ДЕНЬ (B3)) определяет следующий день рождения сотрудника. Следовательно, функция DIITDATA находит количество дней между сегодняшней датой и следующим днем рождения и возвращает количество дней («d”).
Результаты расчета для остальных сотрудников:
Чтобы просмотреть запись о сегодняшнем дне рождения сотрудника, используйте следующую формулу:
Эта формула представляет собой слегка измененную версию предыдущей формулы. В частности, проверяются два условия: И (МЕСЯЦ (B13) = МЕСЯЦ (СЕГОДНЯ ()); ДЕНЬ (СЕГОДНЯ ()) = ДЕНЬ (B13)), т.е если сравниваются числа месяцев и дней в дате рождения с текущей датой. Если результат вычислений ИСТИНА, будет возвращена текстовая строка «DR», если ЛОЖЬ — количество дней до следующего дня рождения будет вычислено.
Например, давайте сделаем расчет для сотрудника, у которого сегодня день рождения:
Анализ дебиторской задолженности до текущей даты в Excel
Компании выдан долг на следующих условиях:
- За каждый день просрочки начисляются штрафы в размере 5% от суммы текущего долга плюс ежедневно начисляемые проценты.
- По истечении 20 дней с даты первого начисления санкции слушание дела в суде будет проведено без процентов.
вам необходимо ввести формулу, которая рассчитывает фактическую сумму долга на ежедневной основе.
Исходная таблица данных:
Для расчета текущего долга воспользуемся формулой:
Эта формула представляет собой модифицированную версию формулы расчета прибыльности с известной начальной суммой и количеством периодов начисления сложных процентов:
Выражение DAYS (TODAY (); B4) используется как показатель степени, определяющий количество дней, прошедших с даты выпуска долга до сегодняшнего дня.
Поскольку причитающаяся сумма будет увеличиваться в течение 20 дней, мы используем следующую запись:
Поэтому добавлена проверка условий, если с момента выдачи долга прошло 20 или более дней. В таком случае сумма долга будет фиксированной и рассчитываться по формуле B2 * СТЕПЕНЬ ((1 + B3); 20).
Результат расчета:
Какой день недели для последнего дня месяца?
На следующем рисунке показано, как рассчитать день недели и дату последнего дня февраля для любого года:
Формула состоит из двух частей, разделенных оператором конкатенации и амперсандом (&” – “&):
- В первой части функция ТЕКСТ преобразует числовое значение, возвращаемое функцией ДАТА, в формат даты. Он указывается во втором аргументе функции — «dd.mm.yy”
- Во второй части функция ТЕКСТ определяет день недели для исходной даты в своем первом аргументе, используя формат ячейки «дддд», указанный во втором аргументе.
Как определить високосный год или нет в Excel?
Есть много решений для определения високосного года в Excel. Вот некоторые из них:
При работе с датами особая сложность заключается в том, что один раз в 4 года количество дней в году составляет 366, а в обычных годах — 365. Поэтому иногда необходимо использовать предыдущие примеры и формулы для различных расчетов в Excel.
Как преобразовать название месяца в порядковый номер
Теперь мы преобразуем название месяца обратно в соответствующий порядковый номер года.
Пример 1. Формула один:
= МЕСЯЦ (ДАННЫЕ ЗНАЧЕНИЯ (A2 & «1″))
Excel автоматически преобразует 1 декабря в 1 декабря. Затем функция ДАТАЗНАЧ преобразует дату в число. После использования функции МЕСЯЦ мы получаем порядковый номер года для имени текущего месяца. На самом деле формулу можно упростить:
= МЕСЯЦ (LA2 & «1″)
Пример 2. Формула два:
= ВПР (A2; $ G $ 1: $ H $ 12; 2; 0)
Пример 3. Формула три:
Оптимальный вариант — лаконичная формула = МЕСЯЦ (A2 и «1»). Нет необходимости резервировать названия месяцев и присваивать им соответствующие серийные номера. Кроме того, он использует только одну функцию.
Синтаксис функции СЕГОДНЯ()
Функция СЕГОДНЯ () не имеет аргументов (их нет сегодня и вряд ли появятся завтра
Как определить последнее воскресенье мая месяца в Excel
Пример 2. Используйте формулы, которые включают функцию EONMESE, чтобы определить следующие значения:
- Какой день недели — последний день мая месяца текущего года?
- Какое число дня соответствует последнему воскресенью мая текущего года?
Для будущей проверки результатов расчета формул сразу уточним, что на момент написания этого урока 2019 год был в стадии разработки.
Чтобы найти последний день мая текущего года, воспользуемся следующей формулой:
Функция ТЕКСТ используется для преобразования данных (отображения текстового представления дня недели). Функция WEEKDAY возвращает числовое значение дня недели (от 1 до 7). Функция EONMONTH вычисляет дату последнего дня текущего месяца (аргументы — СЕГОДНЯ и 0 соответственно).
Результат расчета:
Чтобы найти день, соответствующий последнему воскресенью мая текущего года, воспользуемся формулой:
Результат:
Чтобы убедиться, что получен правильный результат, используйте формулу для проверки последнего понедельника мая текущего года. В ячейку B9 вводим формулу:
Результат расчета:
Способ 3: вычисление количества дней для использования в сложных формулах
В приведенных выше примерах мы показали, как рассчитать количество дней в месяце для указанной календарной даты или автоматически для текущего месяца с выводом результата в отдельной ячейке. Но нахождение этого значения может понадобиться и для расчета других показателей. В этом случае расчет количества дней будет производиться по сложной формуле и не будет отображаться в отдельной ячейке. Посмотрим, как это сделать, на примере.
Нам нужно, чтобы в ячейке отображалось количество дней, оставшихся до конца текущего месяца. Как и в предыдущем способе, этот вариант не требует открытия мастера. Вы можете просто ввести в ячейку следующее выражение:
= ДЕНЬ (E-МЕСЯЦЫ (СЕГОДНЯ (); 0)) — ДЕНЬ (СЕГОДНЯ())
После этого в указанной ячейке будет отображаться количество дней до конца месяца. Каждый день результат будет обновляться автоматически, и обратный отсчет начнется заново с начала нового периода. Получается своего рода обратный отсчет.
Как видите, эта формула состоит из двух частей. Первое из них — это уже знакомое выражение для вычисления количества дней в месяце:
= ДЕНЬ (E-МЕСЯЦЫ (СЕГОДНЯ (); 0))
Но во второй части из этого показателя вычитается сегодняшнее число:
-ДНЯ В ДЕНЬ())
Поэтому при выполнении этого расчета формула для расчета количества дней является частью более сложной формулы.
Способ 4: альтернативная формула
Но, к сожалению, в более ранних версиях программы, чем Excel 2007, нет оператора EUMMON. А как быть тем пользователям, которые используют старые версии приложения? Для них эта возможность существует через другую формулу, более масштабную, чем описанная выше. Давайте посмотрим, как рассчитать количество дней в месяце для заданной календарной даты с помощью этой опции.
- Выделите ячейку, чтобы просмотреть результат, и перейдите в окно аргументов оператора ДЕНЬ, как мы привыкли. Поместите курсор в единственное поле этого окна и щелкните перевернутый треугольник слева от строки формул. Перейдите в раздел «Другие функции…».
- В окне мастера в группе «Дата и время» выберите название «ДАТА» и нажмите кнопку «ОК».
- Открывается окно оператора ДАТА. Эта функция преобразует дату в обычном формате в числовое значение, которое затем должен обработать оператор DAY.
В открывшемся окне есть три поля. В поле «День» можно сразу ввести цифру «1». Это будет одно и то же действие для любой ситуации. Но с двумя другими областями придется разобраться тщательно.
Установите курсор в поле «Год». Далее мы переходим к выбору операторов через семейный треугольник.
- Все в той же категории, что и мастер, выберите название «ГОД» и нажмите кнопку «ОК».
- Откроется окно аргументов оператора YEAR. Определяет год по указанной дате. В единственном поле окна «Дата в числовом формате» указываем ссылку на ячейку, содержащую исходную дату, для которой вы хотите определить количество дней. После этого не спешите нажимать кнопку «ОК», а щелкните имя «ДАТА» в строке формул.
- Итак, давайте снова вернемся к окну темы DATE. Установите курсор в поле «Месяц» и выберите функции.
- В мастере щелкните по названию «МЕСЯЦ» и нажмите кнопку «ОК».
- Откроется окно аргументов функции МЕСЯЦ. Его задачи аналогичны предыдущему оператору, только отображает значение номера месяца. В единственном поле этого окна установите такую же ссылку на исходный номер. Затем в строке формул щелкните название «ДЕНЬ».
- Вернитесь в окно аргументов ДЕНЬ. Здесь нам нужно сделать только небольшую растяжку. В единственном поле окна, которое уже содержит данные, добавьте выражение «-1» без кавычек в конце формулы, а также вставьте «+1» после оператора МЕСЯЦ. Затем нажмите кнопку «ОК».
- Как видите, в предварительно выбранной ячейке отображается количество дней в месяце, которому принадлежит указанное число. Общая формула выглядит так:
= ДЕНЬ (ДАТА (ГОД (D3); МЕСЯЦ (D3) +1; 1) -1)
Секрет этой формулы прост. Мы используем его для определения даты первого дня следующего периода, затем вычитаем из него день, получая количество дней в указанном месяце. Переменная в этой формуле является ссылкой на ячейку D3 в двух местах. Если вы замените его адресом ячейки, где указана дата в вашем конкретном случае, вы можете просто перенести это выражение на любой элемент листа без использования функции мастера.
Как записать месяц прописью?
Как и в примере выше, мы будем использовать функцию ТЕКСТ. Формат «MMMM» даст нам полную запись месяца (в именительном падеже):
Другой способ — совместное использование функций МЕСЯЦ (в английской версии МЕСЯЦА) и ВЫБРАТЬ (в английской версии ВЫБРАТЬ).
Месяц даст нам порядковый номер месяца указанной даты (от 1 до 12), а SELECT будет соответствовать значению числового текста (где 1 — январь, 2 — февраль,…, 12 — декабрь):
Если мы напишем число с месяцем и годом, часто месяц необходимо указывать в родительном падеже (а не в именительном падеже, как в примере выше), например 2 февраля 2015 г или 14 августа 2012 г и т.д.
Опять же, нам поможет формат [$ -FC19] MMMM, который записывает месяц на дату в родительном падеже, отличие от предыдущей версии записи только в том, что мы удалили день и год из записи:
Также есть альтернативный вариант. На помощь снова придут знакомые функции МЕСЯЦ и ВЫБОР, только здесь вместо именительного падежа месяцами прописываем родительный падеж (давайте изменим окончания):
