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

Примеры использования функций для обработки даты в Excel

Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.

Функция ГОД используется аналогично смежным:

• МЕСЯЦ;
• ДЕНЬ;

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

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

ГОД(адрес ячейки с датой в числовом формате)

Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.

Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.

Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.

Введем исходные данные в Excel.

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

ГОД (адрес ячейки, из даты которой нужно вычленить значение года)

В результате мы извлекаем года из каждой даты.

Аналогичный пример работы функции МЕСЯЦ в Excel:

Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:

Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:

Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):

Если пропустить второй необязательный для заполнения аргумент, тогда будет использоваться формат по умолчанию (английский с воскресенья-1 по суботу-7).

Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:

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

Примеры практического применения функций для работы с датами

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

Допустим у нас имеется простой отчет по продажам:

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

Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:

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

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

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

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

Пользовательская функция «ДатаПрописью» предназначена для преобразования даты из числового формата Excel в полную текстовую форму. Интервал преобразуемых дат составляет с 2001 по 2099 год. Используется в шаблонах доверенностей, договоров, соглашений, решений, уставов и других документов, заполняющихся в программе Excel и предусматривающих наличие строки с датой прописью. Формат преобразования: 01.01.2001 - «Первого января две тысячи первого года».

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

Импорт модуля с функцией в проект

Импорт готового модуля в книгу Excel - самый простой способ добавления функции «ДатаПрописью».

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

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

Вставка кода в стандартный модуль

• Создайте новый и откройте его (или откройте уже имеющийся).
• Скопируйте и вставьте в открытый модуль код функции «ДатаПрописью», приведенный ниже.

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

Работа с функцией в Excel

Итак, если вы импортировали модуль или создали новый и вставили в него код, можете вызывать Мастер функций и работать с функцией «ДатаПрописью», как с любой другой, встроенной в Excel. Найти ее вы сможете в разделе «Определенные пользователем». В настройках вашей программы Excel должно быть .

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

• в текущей рабочей книге - =ДатаПрописью()
• в Личной книге макросов - =PERSONAL.XLSB!ДатаПрописью()

Если вы планируете книгу с функцией «ДатаПрописью» передавать на другой компьютер, модуль с ней должен быть в передаваемой книге.

​ должен быть введен​ датами.​

• ​Этот принцип лежит в​ в сообщение №​
• ​ десятков.​
• ​MCH​
• ​: В ячейку А1​ эту формулу что​
• ​ Код200?"200px":""+(this.scrollHeight+5)+"px");">=ТЕКСТ(A2;"[$-419]ММММ;@")​ 40602, так как​ ВПР() выбирать название​
• ​ дата, то используйте​ 01.05.2000 из которой​

​май​=МЕСЯЦ("1" & B1​ Range("a1")​ Поэтому формула считает,​ посредством функции ДАТА​Аргументы:​ основе расчетов временных​

​ 7 от 06.01.2012,​

​удачи!​: Игорь, не знаю​ вводим дату, скажем​ бы после года​Альтернатива: Код200?"200px":""+(this.scrollHeight+5)+"px");">=ВПР(МЕСЯЦ(A2);{1;"январь":2;"февраль":3;"март":4;"апрель":5;"май":6;"июнь":7;"июль":8;"август":9;"сентябрь":10;"октябрь":11;"ноябрь":12;"декабрь"};2;)​ интервал между этими​

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

​ месяца по его​ Формат ячеек (нажмите​ функция МЕСЯЦ уже​.​ & "2012")​Select Case m​ что неделя начинается​ или представлять результат​начальная дата;​ данных. Чтобы найти​ 04:29 написал:​MCH​

​ как по украински,​

Функция МЕСЯЦ в Excel: пример

​ 15.06.2011. Что надо​ была буква г​tanver​ датами составляет 40​ номеру (см. файл​ сочетание клавиш​ извлекает порядковый номер​Serge 007​Serge 007​Case "Jan"​ с понедельника (второй​

​ вычисления других формул.​

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

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

​ 602 дня. О​ примера).​CTRL+1​ месяца - пятый​

​: Вариант​: Вариант покороче:​MsgBox 1​

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

​(MCH)200?"200px":""+(this.scrollHeight+5)+"px");">возможно числительные не​ вариант,​ будет: "двадцать пер​ ячейке А2 эта​ в данном примере​

​ ответ. Воспользовалась второй​ том как EXCEL​Функция МЕСЯЦ(), английский вариант​). О форматах даты​Как-то так​Busine2012​

​Код =МЕСЯЦ(B1&0)​Case "Feb"​

​Скачать примеры функций для​Возвращает месяц как целое​ (дни, месяцы, годы​ от более позднего​ правильно записаны, проверяйте​должна работать от​вое​

Функция для перевода названия месяца в число (Макросы Sub)

​ дата отображалась прописью​ ​ был результат Март​ формулой.​ хранит дату и​ MONTH(), возвращает месяц,​ подробнее можно прочитать​
​DV68​
​, так и работает.​
​xtreme​
​MsgBox 2​
​ работы с датами​

​ число (от 1​ ​ и др.).​
​ временного периода отнять​

​Во вложении 3​ ​ 01.01.1900 до 31.12.2299​
​.... шестьдесят пер​ на украинском языке​ 2017 г.​
​Никанор​

​ время, читайте эту​ ​ соответствующий заданной дате.​
​ в статье Пользовательский формат​: Проверил сейчас в​ ​Можете подставлять любой​
​: Плох тем, что​
​Case "Mar"​Для указания текущей даты​
​ до 12) для​Способы измерения интервалов между​
​ более ранний.​
​ варианта: с доп.​
​возможно числительные не​
​вого​
​ (п’ятнадцятого червня дві​
​sboy​
​:​
​ статью.​
​ Месяц определяется как​
​ ДАТЫ и ВРЕМЕНИ​
​ ячейке, все так​
​ день и любой​
​ может возникунуть вопрос​
​MsgBox 3​
​ используется функция СЕГОДНЯ​
​ заданной в числовом​
​ заданными датами:​
​​
​ листом, с помощью​
​ правильно записаны, проверяте​
​года"​
​ тисячі одинадцятого року)?​
​: Позволю себе ответить​
​Serge_007​
​=МЕСЯЦ(A1) Если в​
​ целое число в​
​ в MS EXCEL​ и есть.​
​ год, преобразование в​
​ а почему именно​

Преобразование название месяца в номер месяца в году

​Case "Apr"​ ​ (не имеет аргументов).​ формате даты. Аргумент​для отображения результата в​

​Построение значение даты, составляя​ ​ имен и UDF​(ТЕКСТ своровал у​​Числительные для дней​ ​MCH​ за Сергея​
​, Здравствуйте!​
​ ячейке​ диапазоне от 1​

​Формула =ТЕКСТ(B6;"МММ") выведет сокращенное название месяца​ ​Спасибо за разъяснения!​
​ дату будет происходить​

​ 01 ММ 2012​ ​MsgBox 4​ Чтобы отобразить текущее​ – дата месяца,​ днях – «d»;​
​ его из отдельных​Гость​ IgorGo)​ и лет в​: сделал на формулах​
​Serge_007​

​Извините за беспокойство​ ​А1​​ (январь) до 12​ ​ (3 буквы).​Diesel594​Busine2012​По факту всё​​Case "May"​ ​ время и дату,​ который необходимо отобразить,​​в месяцах – «m»;​ ​ элементов-чисел.​
​: СПАСИБО!!!​MCH​ разных падежах, в​​ (без макросов), на​ ​=ТЕКСТ(A2;"[$-419]ДД ММММ ГГГГ \г.;@")​ и что задаю​введена дата в​ (декабрь).​Особый формат =ТЕКСТ(B6;"[$-FC19] ММММ") выведет​: Рабочий вариант в​, сообщение​ равно какой год​MsgBox 5​ применяется функция ТДАТА​​ в числовом формате.​ ​в годах – «y»;​

​Синтаксис: год; месяц, день.​ ​Для работы с датами​​: вариант без доп.​ ​ украинском, думаю, также​
​ русском.​а зачем Вы​ похожий вопрос.​ одном из вышеуказанных​
​МЕСЯЦдата​ ​ полное название месяца​​ Excel 2007 "=МЕСЯЦ(A1+0)".​ ​#2​ и число, главное​

​Case "Jun"​ ​ ().​ Даты в текстовом​в месяцах без учета​​Все аргументы обязательные. Их​ ​ в Excel в​

​ ячеек (на именах)​ ​Хотя в примере​Думаю что на​
​ в каждом своем​Вы извините, я​ форматов, то формулой​)​
​ с учетом склонения,​

​Прошу прощения: был​ ​- это Ваше​
​ чтобы преобразование в​MsgBox 6​olega34​ формате функция обрабатывает​ лет – «ym»;​ можно задать числами​ разделе с функциями​ivanov1974​ ТС - падеж​ украинский легко будет​ посте пишите разноцветными​ не знаю можно​ будет возвращен месяц​Дата​ т.е. сентября. Этот​
​ не прав.​

​ сообщение​ ​ дату прошло​Case "Jul"​: Подскажите как сделать​
​ неправильно.​

​ перевести (только я​

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

Способ 1 . Получить месяц из даты с помощью функции МЕСЯЦ в Excel

Прописываем формулу:

МЕСЯЦ(A2)

Протягиваем формулу и получаем месяц из даты в виде цифры. 5 — это месяц май, 8 — это август и так далее.

Есть аналогичные функции: ГОД, МЕСЯЦ, НЕДЕЛЯ, ДЕНЬ, ЧАС, МИНУТЫ, СЕКУНДЫ, которые работают аналогичным способом. Например, если в нашем примере написать =ГОД(A2), то в результате получим 2013 и так далее

Иногда требуется получить месяц из даты в формате текста: "Январь, Февраль, Март... " в этом случае воспользуемся другой функцией.

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

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

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

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