Excel

Месяц в число в excel: Как преобразовать название месяца в число в Excel?

Содержание

МЕСЯЦ (функция МЕСЯЦ) - Служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции МЕСЯЦ в Microsoft Excel.

Описание

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

Синтаксис

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

Аргументы функции МЕСЯЦ описаны ниже.

  • Дата_в_числовом_формате    Обязательный аргумент.

    Дата месяца, который необходимо найти. Дата должна быть введена с использованием функции ДАТА либо как результат других формул или функций. Например, для указания даты 23 мая 2008 года следует воспользоваться выражением ДАТА(2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.

Замечания

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

Значения, возвращаемые функциями ГОД, МЕСЯЦ и ДЕНЬ, соответствуют григорианскому календарю независимо от формата отображения для указанного значения даты.

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

Пример

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

Дата

15-апр-2011

Формула

Описание

Результат

=МЕСЯЦ(A2)

Месяц даты в ячейке A2

4

См.

также

Функция ДАТА

Сложение или вычитание дат

Функции даты и времени (справка)

Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel. Определение номера года или месяца в Эксель

Количество рабочих дней между датами в Excel

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

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

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

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

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

 

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

 

Поделитесь нашей статьей в ваших соцсетях:

Похожие статьи

Количество рабочих дней между датами в Excel

Функции для извлечения различных параметров из дат и времени в Excel

Весь курс: урок 1 | урок 2 | урок 3

| урок 4 | урок 5

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

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

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

ГОД()

Возвращает значение года, соответствующее заданной дате. Год может быть целым числом в диапазоне от 1900 до 9999.

В ячейке A1 представлена дата в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это пользовательский (нестандартный) числовой формат представления даты и времени в Excel.

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

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

МЕСЯЦ()

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

ДЕНЬ()

Возвращает значение дня, соответствующее заданной дате. День возвращается как целое число в диапазоне от 1 до 31.

ЧАС()

Данная функция возвращает значение часа, соответствующее заданному времени. Час возвращается как целое число в диапазоне от 0 до 23.

МИНУТЫ()

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

СЕКУНДЫ()

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

ДЕНЬНЕД()

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Как в Excel получить месяц из даты (функция ТЕКСТ и МЕСЯЦ)

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

Дата и время
25.08.2013 18:03
14.05.2013 18:04
07. 08.2013 18:02
11.09.2013 18:01
15.05.2013 10:32

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

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

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

=МЕСЯЦ(A2)


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

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

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

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

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

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

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

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

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

Функция ДАТА — как использовать формулу ДАТА в Excel

Функция ДАТА возвращает число, представляющее дату определенного дня, месяца и года. Функция дата используется, как правило, для построения даты из составных частей, таких как день, месяц и год. Минимальное и максимальное значение дат, которое может принять Excel — 1 января 1900 года и 31 декабря 9999 года соответственно.

Синтаксис функции ДАТА

Формула ДАТА состоит из трех частей:

ДАТА(год; месяц; день)

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

Месяц – второй аргумент, который указывает, какой месяц будет фигурировать в нашей дате. Обратите внимание, что значения месяца не ограничены рамками от 1 до 12, а могут принимать любые значения, даже отрицательные. К примеру, если вы напишите формулу =ДАТА(2013;13;1),  Excel вернет дату, которая отстоит от 1-го января 2013 года на 13 месяцев, т.е. 1/1/2014.

День – часть функции, которая задает день необходимой даты. Так же, как и аргумент Месяц, может принимать любые значения (не только от 1 до 31). К примеру, если вы введете формулу =ДАТА(2013;01;35), Excel вернет дату, которая отстоит от 1-го января 2013 года на 35 дней, т. е. 4/02/2013. Точно так же вы можете использовать отрицательные значения, чтобы создавать прошедшие даты.

Давайте рассмотрим пример использования функции ДАТА. Предположим, что у нас имеется год, месяц и день, находящиеся в трех различных полях. Необходимо объединить все три значения для создания даты. Как показано выше, вводим формулу =ДАТА(A2;B2;C2). Результатомм в данном случае будет дата 19-го июня 2013 года.

Формат даты в Excel

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

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

Возможные ошибки формул ДАТА

Функция дата может вернуть следующие ошибки:

Ошибка #ЧИСЛО! формулы ДАТА выдается, когда введенная дата выходит за границы допустимых значений (с 0/1/1900 по 31/12/9999). К примеру, если вы хотели ввести год 2013, но по ошибке ввели 20013, скажем =ДАТА(20013;6;19), Excel вернет ошибку.

Ошибка #ИМЯ! формулы ДАТА возвращается, когда один из аргументов введен не корректно (ввод текста вместо числового значения). К примеру, вы ввели «июнь» как аргумент месяц, вместо 6 (порядковый номер месяца июнь) и ввели следующую формулу =ДАТА(2013; июнь; 19). В этом случае Excel вернет ошибку #ИМЯ!

Скачать файл с примерами применения функции ДАТА

Вам также могут быть интересны следующие статьи

Как использовать формулу МЕСЯЦ в Excel

Функция Excel MONTH извлекает месяц из списка дат. Месяц отображается в виде порядкового номера от 1 до 12. Если вы хотите преобразовать это число в текст, создайте именованный диапазон. Вот как использовать функцию MONTH в Excel, чтобы получить номер месяца из даты и превратить его в название месяца.

Инструкции в этой статье относятся к Excel для Office 365, Excel 2019, Excel 2016 и Excel 2013.

Синтаксис функции MONTH

Функция MONTH в Excel возвращает число от 1 до 12. Это число соответствует месяцу для даты в выбранной ячейке или диапазоне.

Дата должна быть правильно введена с помощью функции ДАТА в Excel.

Синтаксис функции MONTH : MONTH (serial_number)

Serial_number является дата , с которой вы хотите извлечь месяц и должна быть действительной датой Excel.

Как использовать Excel, чтобы получить месяц от даты

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

  1. Выберите ячейку, в которой будет отображаться серийный номер месяца.

  2. Перейдите на панель формул и введите = месяц . При вводе Excel предлагает функцию.

  3. Дважды щелкните МЕСЯЦ .

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

  5. Введите закрывающую скобку, затем нажмите Enter .

  6. Результат появится в выбранной ячейке.

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

  8. Серийные номера для дат отображаются в выделенных ячейках.

Как конвертировать номер месяца в текст

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

Создать именованный диапазон

Первым шагом для преобразования серийного номера в название месяца является создание диапазона. Этот диапазон содержит номер и соответствующий месяц.

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

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

  2. Выберите обе ячейки.

  3. Перетаскивайте ручку заливки, пока рядом с ручкой заливки не появится число 12 .

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

  5. Перетащите маркер заполнения вниз, пока рядом с ручкой заполнения не появится слово « декабрь» .

  6. Выберите серийный номер и ячейки с названием месяца.

  7. Перейдите в поле «Имя» и введите имя для диапазона.

  8. Нажмите Enter, чтобы создать именованный диапазон.

Преобразовать число в текст

Следующим шагом является выбор столбца, в который вы хотите вставить текстовую версию месяца.

  1. Выберите ячейку рядом с первым серийным номером в столбце.

  2. Enter = vlookup . По мере ввода Excel предлагает возможные функции. Дважды щелкните VLOOKUP .

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

  4. Введите названный диапазон, затем введите запятую.

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

  6. Выберите месяц и перетащите маркер заполнения в конец столбца.

  7. Названия месяцев отображаются в столбце.

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

Функция МЕСЯЦ возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь).

Синтаксис

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

Дата_в_числовом_формате — это дата, месяц которой необходимо найти. Даты должны вводиться с использованием функции ДАТА или как результат других формул и функций. Например, следует использовать ДАТА(2008,5,23) для 23-го мая 2008 года. Проблемы могут возникнуть, если даты вводятся как текст.

Внимание!

Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января 2008 — номер 39448, так как интервал в днях между этими датами составляет 39448. Microsoft Excel для «Макинтоша» по умолчанию использует другую систему дат.

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

Еще про Excel.

О технических ресурсах корпорации Майкрософт

При возникновении вопросов о приложении, входящем в состав Microsoft Office, прежде всего обратитесь к помощнику по Office и просмотрите руководство Знакомство с Microsoft Office XP или файл Readme для данного приложения. Если ответ на вопрос найти не удастся, можно обратиться для получения нужных сведений к перечисленным ниже техническим ресурсам.

Веб-узел Microsoft Office

Работая в приложении Microsoft Office, можно подключиться к веб-узлу Microsoft Office при помощи команды Office в Интернете из меню Справка. На веб-узле Microsoft Office можно получить ответы на наиболее часто задаваемые вопросы о Microsoft Office. Кроме того, можно получить последние советы, шаблоны, рисунки и обновленные файлы справки.

Компакт-диски Microsoft TechNet и Microsoft Developer Network

Microsoft TechNet.  Всеобъемлющий источник сведений для оценки, внедрения и поддержки продуктов корпорации Майкрософт. Подписка на компакт-диск «Microsoft TechNet» позволяет ежемесячно получать компакт-диски с полным объемом информации о поддержке продуктов корпорации Майкрософт. TechNet предоставляет быстрые и полные ответы на вопросы по программам, управлению сетями, работе систем, администрированию баз данных, драйверам и обновлениям, направлениям технологий и оценке продуктов. Более 250 тысяч страниц подробной технической информации помогут работать качественно и быстро.

Microsoft Developer Network (MSDN).  Всеобъемлющий источник сведений по программированию и инструментальным средствам для создателей приложений для операционных систем Microsoft Windows, а также для тех, кто использует продукты корпорации Майкрософт для разработки собственных приложений. Подписчики MSDN регулярно и своевременно получают самые свежие новости из информационных бюллетеней и других источников.

Microsoft TechNet и MSDN доступны в России через стандартный канал поставок. Вы можете приобрести коробку с этим программным продуктом у одного из партнеров корпорации Майкрософт и затем оформить годовую подписку.

База знаний Microsoft Knowledge Base (KB)

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

KB находится также на компакт-дисках Microsoft TechNet и Microsoft Developer Network.

Библиотека программ Microsoft Software Library

Библиотека программных продуктов Microsoft Software Library (MSL) представляет собой собрание двоичных (не текстовых) файлов, расположенных в Интернете и предназначенных для всех продуктов корпорации Майкрософт. Например, MSL содержит драйверы внешних устройств, служебные программы, файлы электронных справок и технические статьи.

Библиотека MSL также доступна на компакт-дисках Microsoft TechNet и Microsoft Developer Network.

Издательство Microsoft Press

Издательство Microsoft Press предлагает обучающие и справочные материалы, помогающие более полно использовать возможности Microsoft Office, Microsoft Windows 98 и Microsoft Windows 2000.

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

Чтобы найти ближайший магазин, распространяющий продукцию Microsoft Press, посетите веб-узел Microsoft Press или обратитесь в ближайшее представительство корпорации Майкрософт. Телефон в США: (800) MS-PRESS.  Телефон в Канаде: (800) 667-1115.

Статистика за текущую неделю, за месяц и за год

Когда вы работаете с импортом данных с отметкой даты, объем информации может быть огромным. С первого взгляда сложно сказать, имеют ли показатели эффективности тенденцию к росту, снижению, ускорению или замедлению. Сводные статистические данные, такие как данные за неделю до даты, за месяц до даты и за год до даты, часто легче читать и рассказывать вам больше о том, что на самом деле происходит. Давайте узнаем, как реализовать агрегированную статистику X-To-Date с помощью вспомогательных столбцов, функций даты Excel и СУММЕСЛИМН .

Изучите данные

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

Что такое вспомогательные столбцы?

Мы собираемся добавить столбцы Week-To-Date, Month-To-Date и Year-To-Date в эту электронную таблицу. Чтобы упростить процесс и облегчить понимание формул, мы собираемся использовать несколько вспомогательных столбцов, чтобы распределить работу и упростить задействованную логику.

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

Добавление вспомогательных столбцов для текущей недели

Как работает расчет текущей даты? Он начинается в начале недели и суммирует все строки, которые встречаются на той же неделе того же года, вплоть до текущего дня.Чтобы вычислить столбец Week-To-Date, нам нужно иметь возможность сравнивать неделю, в которой находится каждая строка, и день недели для каждой строки. Мы также проверим, указаны ли даты в одном году. К счастью, в Excel есть функции именно для этих целей. WEEKDAY () вычисляет день недели. WEEKNUM () вычисляет количество недель в текущем году. ГОД () вычисляет год даты. Синтаксис этих функций следующий:

 =  WEEKDAY  ( серийный_номер ,  [return_type] ) 
 =  НОМЕР НЕДЕЛИ  ( серийный_номер ,  [return_type] ) 
  =  ГОД  (серийный_номер)  

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

[return_type] позволяет сообщать Excel, когда начинается ваша неделя. 0 (ноль) - стандартная неделя с воскресенья по субботу. 1 - понедельник - воскресенье. 2 - вторник - понедельник и т. Д.

В ячейке E2 создайте формулу для расчета дня недели для даты в A2 . Мы собираемся начать наши недели в понедельник. Формула будет выглядеть так:

 =  ДЕНЬ НЕДЕЛИ  ( A2 , 2) 

В ячейке F2 создайте формулу, которая вычисляет номер недели даты в A2 .Опять же, неделя начнется в понедельник. Эта формула будет выглядеть так:

 =  НЕДЕЛЮ  ( A2 , 2) 

Наконец, мы вычислим год даты в ячейке G2 . Формула будет выглядеть так:

 =  ГОД  ( A2 ) 

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

Расчет текущей даты с использованием СУММЕСЛИМН

Теперь, когда у нас есть вспомогательные столбцы, мы можем собрать функцию СУММЕСЛИМН для суммирования задач, выполненных за каждую неделю.Синтаксис СУММЕСЛИМН следующий:

  =  СУММЕСЛИМН  (диапазон_сумм, диапазон_критерия1, критерий1,  . ..) 

Поле диапазон_суммы - это набор ячеек, которые вы хотите суммировать СУММЕСЛИМН , если они соответствуют критериям.

Поле critera_range1 - это набор ячеек, с которыми вы хотите протестировать свой первый оператор IF.

Поле критерий1 - это условный оператор, по которому нужно проверить диапазон_ критериев1 .

СУММЕСЛИМН может принимать столько наборов из критериев_диапазона и критериев , сколько необходимо. диапазон сумм будет столбцом C .

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

Первый диапазон_критериев будет столбцом E . Чтобы узнать, равен ли день недели текущей строке или меньше, нам нужно установить критерий как WEEKDAY даты в ячейке A2 .

Второй диапазон_критерия будет столбцом F . Чтобы увидеть, совпадает ли номер недели с текущей строкой, нам нужно установить критерий как WEEKNUM даты в ячейке A2 .

Третий диапазон_критериев будет столбцом G . Чтобы увидеть, соответствует ли год текущей строке, нам нужно установить критерий как YEAR даты в ячейке A2 .

Последний оператор СУММЕСЛИМН будет выглядеть так:

 =  СУММЕСЛИМН  ( C: C ,  E: E , "<=" &  WEEKDAY  ( A2 , 2),  F: F ,  WEEKNUM  ( A2 , 2) ,  G: G ,  ГОД  ( A2 )) 

Это учитывает подсчет выполненных задач , но нам все равно нужно рассчитать общее количество задач для каждого вычисления. К счастью, нам не нужно проделывать всю эту работу заново. Выберите оператор SUMIFS , который мы только что создали, и скопируйте все, кроме знака равенства в начале. Единственное, что мы будем менять, это sum_range . Формула СУММЕСЛИМН для второй половины расчета будет выглядеть так:

 =  СУММЕСЛИМН  ( B: B ,  E: E , "<=" &  WEEKDAY  ( A2 , 2),  F: F ,  WEEKNUM  ( A2 , 2) ,  G: G ,  ГОД  ( A2 )) 

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

 =  СУММЕСЛИМН  ( C: C ,  E: E , "<=" &  WEEKDAY  ( A2 , 2),  F: F ,  WEEKNUM  ( A2 , 2) ,  G: G ,  ГОД  ( A2 )) /
   СУММЕСЛИМН  ( B: B ,  E: E , "<=" &  WEEKDAY  ( A2 , 2),  F: F ,  WEEKNUM  ( A2 , 2),  G: G ,  ГОД  ( A2 )) 

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

Теперь мы закончили с первыми двумя вспомогательными столбцами, поэтому мы можем скрыть их. Выберите метки для столбцов E и F . Щелкните выделение правой кнопкой мыши и выберите Hide .

Добавление вспомогательных столбцов для текущего месяца

Процесс создания вспомогательных столбцов для столбца «Месяц до даты» очень похож на процесс «Неделя до даты».

Месяц до даты начинается с начала месяца и складывает все строки, которые встречаются в том же месяце того же года, вплоть до текущего дня.Чтобы вычислить столбец «Месяц до даты», нам нужно иметь возможность сравнивать месяц, в котором находится каждая строка, и день месяца для каждой строки. Мы также проверим, указаны ли даты в одном году. Функция Excel МЕСЯЦ () вычисляет месяц даты. DAY () вычисляет день месяца. Мы будем использовать тот же столбец расчетов YEAR в столбце G . Синтаксис новых функций очень прост:

 =  МЕСЯЦ  ( серийный_номер ) 
 =  ДЕНЬ  ( серийный_номер ) 

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

В ячейке I2 создайте формулу для вычисления месяца даты в A2 . Формула будет выглядеть так:

 =  МЕСЯЦ  ( A2 ) 

В ячейке J2 создайте формулу, которая вычисляет номер недели даты в A2 . Эта формула будет выглядеть так:

 =  ДЕНЬ  ( A2 ) 

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

Расчет текущего месяца с использованием СУММЕСЛИМН

Теперь, когда у нас есть вспомогательные столбцы, мы можем собрать функцию СУММЕСЛИМН для суммирования задач, выполненных за каждый месяц. Процесс такой же, как и для Week-To-Date.

Первый диапазон_критерия будет столбцом I . Чтобы узнать, равен ли месяц текущей строке, нам нужно установить критерий как МЕСЯЦ даты в ячейке A2 .

Второй диапазон_критерия будет столбцом J . Чтобы узнать, равен ли день текущей строке или меньше, нам нужно установить критерий как ДЕНЬ даты в ячейке A2 .

Третий диапазон_критериев будет столбцом G . Чтобы увидеть, соответствует ли год текущей строке, нам нужно установить критерий как YEAR даты в ячейке A2 .

Первый оператор СУММЕСЛИМН будет выглядеть так:

 =  СУММЕСЛИМН  ( C: C ,  I: I ,  МЕСЯЦ  ( A2 ),  J: J , «<=» &  ДЕНЬ  ( A2 ),  G: G ,  ГОД  ( A2 )) 

Проделаем тот же трюк, чтобы сдвинуть диапазон сумм на для второй функции СУММЕСЛИМН . Второй оператор СУММЕСЛИМН будет выглядеть так:

 =  СУММЕСЛИМН  ( B: B ,  I: I ,  МЕСЯЦ  ( A2 ),  J: J , «<=» &  ДЕНЬ  ( A2 ),  G: G ,  ГОД  ( A2 )) 

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

 =  СУММЕСЛИМН  ( C: C ,  I: I ,  МЕСЯЦ  ( A2 ),  J: J , «<=» &  ДЕНЬ  ( A2 ),  G: G ,  ГОД  ( A2 )) /
  СУММЕСЛИМН  ( B: B ,  I: I ,  МЕСЯЦ  ( A2 ),  J: J , "<=" &  DAY  ( A2 ),  G: G ) ,  ГОД  ( A2 )) 

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

Теперь мы закончили со вспомогательными столбцами Месяц и День , поэтому мы можем их скрыть. Выберите метки для столбцов I и J . Щелкните выделение правой кнопкой мыши и выберите Hide .

Добавление вспомогательного столбца для текущего года

У нас есть еще один вспомогательный столбец для создания с начала года до

.

Year-To-Date начинается с начала года и складывает все строки, которые встречаются в том же году, вплоть до текущего дня.Чтобы вычислить столбец Year-To-Date, нам нужно иметь возможность сравнивать год, в котором находится каждая строка, и день года для каждой строки. Функция Excel YEARFRAC () вычисляет процент времени, прошедшего с даты начала. DATE () вычисляет день в формате Excel с учетом месяца, дня и года. Мы будем использовать его для расчета доли года. Мы будем использовать тот же столбец расчетов YEAR в столбце G . Синтаксис новых функций следующий:

 =  YEARFRAC  ( начальная_дата ,  конечная_дата ,  [базисная] ) 
 =  ДАТА  ( год ,  месяц ,  день ) 

start_date определяет начало вашего года - ваш год может начинаться произвольно, и не обязательно, чтобы он начинался 1 января.

end_date - это конец периода, который вы хотите вычислить как долю года.

[базис] позволяет нам определять «год» несколькими различными способами. 1 даст нам фактический год.

Функция ДАТА принимает числовой год , числовой месяц от 1 до 12 и день от 1 до 31.

В ячейке L2 создайте формулу для вычисления доли года, прошедшей на дату в A2 .Формула будет выглядеть так:

 =  ГОД FRAC  ( ДАТА  ( G2 , 1,1),  A2 , 1) 

Мы сообщаем YEARFRAC , чтобы год начинался 1 января, года в ячейке G2 , и сообщаем нам, как далеко продвинулся год в ячейке A2 .

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

Расчет с начала года с использованием СУММЕСЛИМН

Нам нужны только 2 критерия для расчета с начала года.

Первый диапазон_критериев будет столбцом G . Чтобы узнать, равен ли год текущей строке, нам нужно установить критерий как YEAR даты в ячейке A2 .

Второй диапазон_критерия будет столбцом L . Чтобы узнать, равна ли доля года текущей строке или меньше, нам нужно установить критерий как Доля года даты в ячейке A2 .

Первый оператор СУММЕСЛИМН будет выглядеть так:

 =  СУММЕСЛИМН  ( C: C ,  G: G ,  ГОД  ( A2 ),  L: L , "<=" &  L2 ) 

Проделаем тот же трюк, чтобы сдвинуть диапазон сумм на для второй функции СУММЕСЛИМН . Второй оператор СУММЕСЛИМН будет выглядеть так:

 =  СУММЕСЛИМН  ( B: B ,  G: G ,  YEAR  ( A2 ),  L: L , "<=" &  L2 ) 

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

 =  СУММЕСЛИМН  ( C: C ,  G: G ,  ГОД  ( A2 ),  L: L , "<=" &  L2 ) /  СУММЕСЛИ  ( B: B ,  G: G ,  ГОД  ( A2 ),  L: L , "<=" &  L2 ) 

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

Теперь мы закончили с остальными вспомогательными столбцами, поэтому мы можем скрыть их.Выберите метки для столбцов G и L . Щелкните выделение правой кнопкой мыши и выберите Hide .

Пример сводной статистики X-To-Date

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

Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel.Программа Excel Tactics призвана помочь вам в этом.

Подпишитесь на информационный бюллетень, чтобы быть в курсе последних статей. Зарегистрируйтесь, и вы получите бесплатное руководство с 10 быстрыми клавишами для экономии времени!

Как создать сравнение за месяц до даты (MTD) с помощью сводной таблицы + видео

Итог: Узнайте, как создавать расчеты по месяцам (MTD) в сводных таблицах для сравнений по месяцам, кварталам или годам.

Уровень квалификации: Средний

Задача: Начальник хочет видеть отчет, в котором показаны числа за первые 15 дней каждого месяца (на самом деле это был отличный вопрос, заданный Кристофером).

Проблема: Как видно из приведенной выше сводной таблицы, цифры за январь 2016 года намного ниже, чем за январь 2105 года. Это связано с тем, что у нас есть данные только за первые 15 дней января 2016 года и данные за 31 день за январь. 2015.

Итак, нам нужно найти способ вычислить числа до текущей даты (MTD) для других месяцев в году.

Видеоурок: Как фильтровать сводные таблицы для сравнений MTD

Посмотреть видео на YouTube

Дополнительные ресурсы:

Загрузить файл примера

Загрузите файл-пример, чтобы продолжить.

Месяц до даты Сравнение MTD в сводной таблице.xlsx (111,0 КБ)

Следующий файл содержит расчеты для недели месяца или года. См. Комментарий ниже для получения дополнительной информации.

Сводная таблица сравнения MTD за месяц до даты - Weeks.xlsx (127,7 КБ)

Решение: фильтрация по дням месяца с помощью слайсера

Видео выше объясняет все решение, если вам не нравится читать. 🙂

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

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

Примечание. Срезы доступны в Excel для Windows 2010 и более поздних версий, Mac 2016 и более поздних версий. Если у вас более старая версия, у меня есть альтернативное решение ниже, в котором вместо срезов используются формулы.

Шаг 1.

Добавьте столбец, содержащий день месяца

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

Функция ДЕНЬ вернет день месяца из значения даты.

= ДЕНЬ (серийный_номер)

Функция ДЕНЬ имеет только один аргумент, серийный_номер . Serial_number - это значение даты.

Итак, мы можем добавить столбец к нашим исходным данным за день месяца. Мы просто введем формулу ДЕНЬ в ячейку D2. Эта формула ссылается на дату в той же строке в ячейке A2.

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

Шаг 2. Добавьте срез в сводную таблицу для нового поля дня

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

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

Вот инструкция, как вставить слайсер:

  1. Выберите любую ячейку в сводной таблице.
  2. Перейдите на вкладку «Анализ / Параметры» на ленте и нажмите кнопку «Вставить срез».
  3. Вы увидите список всех полей в сводной таблице.
  4. Установите флажок для поля День.
  5. Нажмите ОК

Срез будет добавлен на рабочий лист рядом со сводной таблицей.

Вы можете изменить макет в соответствии с моим, изменив количество столбцов среза на 7, а затем изменив размер среза, чтобы просмотреть числа на кнопках среза.

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

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

Щелкните левой кнопкой мыши и удерживайте кнопку «1» на слайсере, а затем наведите указатель мыши на «15». Отпустите кнопку мыши, и будут выбраны дни с 1 по 15.

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

Это позволяет нам просматривать числа MTD для каждого месяца, а также делать сравнения месяц за месяцем или год за годом. Потрясающие!

Альтернативное решение: создание расчета MTD в исходных данных

Для первого решения требуется, чтобы в вашей версии Excel были срезы (Excel для Windows 2010 и более поздних версий, Mac 2016 и более поздних версий).

Также требуется вручную выбирать элементы при добавлении новых данных (дней) к исходным данным.

Мы также можем рассчитать сумму MTD, используя формулу IF. Я добавил это решение в файл примера с более подробным объяснением.

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

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

Оставьте комментарий ниже, если вам интересно узнать больше о том, как это работает, и я буду рад снять об этом видео.

8 способов извлечь название месяца из даты в Excel

Можно ли извлечь название месяца, учитывая значение даты в Excel?

Например, учитывая 2020-04-23 , можете ли вы вернуть значение апреля ?

Да, конечно можно! Excel может все.

В этом посте вы узнаете 8 способов получить название месяца из значения даты.

Длинный формат даты

Вы можете получить название месяца, отформатировав даты!

Хорошие новости, это очень просто сделать!

Отформатируйте даты:

  1. Выберите даты, которые нужно отформатировать.
  2. Перейдите на вкладку Home в командах ленты.
  3. Щелкните раскрывающийся список в разделе «Числа».
  4. Выберите в меню опцию Long Date .

Это отформатирует дату 2020-04-23 как 23 апреля 2020 г. , так что вы сможете увидеть полное название месяца на английском языке.

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

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

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

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

В диалоговом окне Формат ячеек .

  1. Перейдите на вкладку Число .
  2. Выберите Custom в параметрах категории.
  3. Введите мм мм в поле ввода Тип.
  4. Нажмите кнопку OK .

Это отформатирует дату 2020-04-23 как апреля , так что вы увидите только полное название месяца без части дня или года.

Опять же, это форматирование не изменит базовое значение, оно просто будет по-другому отображаться в сетке.

Вы также можете использовать пользовательский формат mmm для создания сокращенного названия месяца, начиная с даты, например, апреля вместо апреля .

Флэш-заливка

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

Начните вводить пару примеров названия месяца в столбце рядом с отформатированными датами.

Excel угадывает узор и закрашивает его светло-серым цветом. Затем вы можете нажать Enter, чтобы принять эти значения.

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

ТЕКСТ Функция

Предыдущие методы форматирования изменяли только внешний вид даты для отображения названия месяца.

С помощью функции ТЕКСТ вы сможете преобразовать дату в текстовое значение.

  = ТЕКСТ (B3, «мммм»)  

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

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

Excel имеет функцию МЕСЯЦ, которая может извлекать месяц из даты.

МЕСЯЦ и ПЕРЕКЛЮЧАТЕЛЬ

Извлекается как числовое значение от 1 до 12, поэтому вам также нужно будет каким-то образом преобразовать это число в имя. Для этого вы можете использовать функцию ПЕРЕКЛЮЧАТЕЛЬ.

  = ПЕРЕКЛЮЧАТЕЛЬ (
    МЕСЯЦ (B3),
    1, "Январь", г. 
    2, «Февраль», г.
    3, «Март», г.
    4, "Апрель", г.
    5, «Май», г.
    6, "Июнь", г.
    7, "Июль", г.
    8, «Август», г.
    9, "Сентябрь", г.
    10, «Октябрь», г.
    11, «Ноябрь», г.
    12, "Декабрь"
)  

Приведенная выше формула получит номер месяца из даты в ячейке B3 с помощью функции MONTH .

Функция ПЕРЕКЛЮЧАТЕЛЬ затем преобразует это число в имя.

МЕСЯЦ и ВЫБЕРИТЕ

Есть еще более простой способ использовать функцию МЕСЯЦ, как указал Уэйн в комментариях.

Вы можете использовать функцию ВЫБРАТЬ, поскольку номера месяцев могут соответствовать выбору функции ВЫБОР.

  = ВЫБРАТЬ (
    МЕСЯЦ (B3),
    "Январь",
    "Февраль",
    "Маршировать",
    "Апреля",
    "Может",
    "Июнь",
    "Июль",
    "Август",
    "Сентябрь",
    "Октябрь",
    "Ноябрь",
    "Декабрь"
)  

Приведенная выше формула получит номер месяца от даты в ячейке B3 с помощью функции MONTH , затем CHOOSE вернет соответствующее название месяца на основе числа.

Power Query

Вы также можете использовать мощный запрос для преобразования дат в имена.

Сначала вам нужно преобразовать данные в таблицу Excel.

Затем вы можете выбрать свою таблицу и перейти на вкладку Data и использовать команду From Table / Range .

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

Преобразует столбец даты в название месяца.

  1. Выберите столбец дат для преобразования.
  2. Перейдите на вкладку Transform в командах ленты редактора запросов питания.
  3. Нажмите кнопку Дата в разделе «Дата и время».
  4. Выберите Месяц из меню.
  5. Выберите Название месяца из подменю.

Это преобразует ваш столбец дат в текстовое значение полного названия месяца.

  = Таблица. TransformColumns (# "Changed Type", {{"Date", each Date.MonthName (_), type text}})  

Это автоматически создаст для вас приведенную выше формулу M-кода, а ваши даты будут преобразованы.

Затем вы можете перейти на вкладку Home и нажать Close & Load , чтобы загрузить преобразованные данные обратно в таблицу в книге Excel.

Значения сводной таблицы

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

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

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

Перетащите поле Date в область Rows и поле Sales в область Values ​​.

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

Расчетная колонка Power Pivot

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

Это вычислит значение для каждой строки данных и создаст новое поле для использования в списке полей сводной таблицы.

Выполните те же шаги, что и выше, чтобы вставить сводную таблицу.

В диалоговом окне Create Pivot Table отметьте опцию Добавить эти данные в модель данных и нажмите кнопку OK .

После создания сводной таблицы перейдите на вкладку Data и нажмите команду Manage Data Model , чтобы открыть редактор Power Pivot.

  = ФОРМАТ (Таблица1 [Дата], «мммм»)  

Создайте новый столбец с приведенной выше формулой в редакторе Power Pivot.

Закройте редактор, и новый столбец станет доступен для использования в списке полей сводной таблицы.

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

Выводы

Это 8 простых способов получить название месяца по дате в Excel.

Если вы просто хотите отобразить имя, параметров форматирования может быть достаточно.

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

Какой ваш любимый метод?

Примеры функции даты

Excel с видео и бесплатной рабочей тетрадью

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

Расчет количества дней

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

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

Однако результат не может быть отрицательным числом , поэтому вычтите более раннюю дату (меньшее число) из более поздней даты (большее число).

Вычесть даты в Excel

В этом примере в ячейке A2 указана дата начала января, а в ячейке B2 - дата окончания июня.

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

Совет : Если результат выглядит как дата, а не число, измените числовой формат ячейки в Общий

Проблема при вычитании дат

Иногда, когда вы работаете с функциями даты, в ячейке формулы отображаются все числовые знаки (хэш-теги, знаки решетки) вместо числа или даты.

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

В этом примере в ячейке формулы отображаются все числовые знаки, если вычесть более позднюю дату (B4) из более ранней даты (A4). Результатом является отрицательное число, и Excel не может отобразить результат

Получить текущую дату

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

= СЕГОДНЯ ()

ПРИМЕЧАНИЕ: Чтобы ввести текущую дату как статическое значение, которое не будет обновляться, используйте горячую клавишу: Ctrl +;

Получить номер года, месяца, недели или дня

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

Год: = ГОД (A2)

Месяц: = МЕСЯЦ (A2)

Неделя: = НОМЕР НЕДЕЛИ (A2)

День: = ДЕНЬ (A2)

ПРИМЕЧАНИЕ. Если результат выглядит как дата, а не число, измените числовой формат ячейки в Общий

Получить название месяца или дня

Если ячейка содержит дату, вы можете извлечь части этой даты в виде текста.Например, покажите название месяца или название дня недели для даты. В в этом примере дата находится в ячейке A2.

Название месяца (краткое): = ТЕКСТ (A2, «ммм»)

Название месяца (длинное): = ТЕКСТ (A2, «мммм»)

Название дня недели (краткое): = ТЕКСТ (A2, "ddd")

Название дня недели (длинное): = ТЕКСТ (A2, "dddd")

Получить дату конца месяца

Если вам нужно вычислить первый или последний день месяца, EOMONTH (конец месяца) функция работает хорошо.В этом примере дата находится в ячейке БИ 2.

EOMONTH использует 2 аргумента - дату начала и количество месяцев. К получить дату в текущем месяце, использовать ноль в качестве количества месяцев.

Вот несколько примеров:

--Последний день предыдущего месяца: = EOMONTH (B2, -1)

--Первый день выбранного месяца: = EOMONTH (B2), - 1) +1

--Последний день выбранного месяца: = EOMONTH (B2,0)

- Первый день следующего месяца: = EOMONTH (B2,0) + 1

--Последний день выбранного месяца в прошлом году: = EOMONTH (B2, -12)

Или объедините функцию EOMONTH с TODAY, чтобы получить даты относительно текущая дата.Например:

--Первый день текущего месяца: = EOMONTH (TODAY (), - 1) +1

Получить значение даты с INT

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

Функция INT возвращает только целую часть этого числа, которое представляет дату. В этом примере объединенная дата / время находится в ячейке A2.

Чтобы получить дату, введите в ячейку B2 следующую формулу: = INT (A2)

Получить номер дня недели

Чтобы получить номер дня недели для даты, используйте функцию WEEKDAY. Например, с датой в ячейке B4 эта формула покажет номер дня недели (воскресенье = 1, понедельник = 2 и т. Д.):

= ДЕНЬ НЕДЕЛИ (B4)

WEEKDAY Тип возврата

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

  • Если этот аргумент опущен, используется return_type по умолчанию, равный 1, а дни недели пронумерованы от воскресенья (1) до субботы (7)
  • В более старых версиях Excel доступны только варианты 1, 2 и 3.

Получить номер недели

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

.

= НОМЕР НЕДЕЛИ (B2)

WEEKNUM Тип возврата

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

  • Если этот аргумент опущен, используется return_type по умолчанию 1, недели начинаются с воскресенья
  • В более старых версиях Excel доступны только варианты 1 и 2.
WEEKNUM Systems

Для номеров недель используются две системы - Система 1 и Система 2. Когда вы щелкаете по типу возврата в списке, вы можете увидеть, какую систему он использует.

Система 1 - Неделя, содержащая 1 января, является первой неделей года и пронумерована неделей 1.

Система 2 - Неделя, содержащая первый четверг года, является первой неделей года и пронумерована как неделя 1. Европейская система нумерации недель (ISO 8601)

Найти дату начала недели

Чтобы сгруппировать данные по неделям, используйте функцию ДЕНЬ НЕДЕЛИ, чтобы вычислить дату начала недели для каждой записи. На снимке экрана ниже в ячейках A2: A9 указаны даты.Первые 7 дат приходятся на одну неделю, с датой начала воскресенье, 27 ноября. Конечная дата наступает на следующей неделе, которая начинается в воскресенье, 4 декабря.

Используйте эту формулу, чтобы вычислить воскресенье как дату начала: = A2-WEEKDAY (A2 + 1,3)

«3» в конце этой формулы указывает Excel использовать числа от 0 до 6 для обозначения с понедельника по воскресенье. Дата корректируется на один день, чтобы вычесть 0, если дата воскресенье, 1 в понедельник и т. Д.

Даты в формуле GetPivotData

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

Чтобы помочь вам избежать этой проблемы, в этом коротком видео показано несколько обходных путей Date для функции GetPivotData. Более подробная информация и письменные шаги приведены в GETPIVOTDATA. страница.

Получить дату из текстовой строки

Функции даты в Excel не будут работать, если значение является текстовой строкой, а не реальной датой.Например, импортированные данные могут иметь даты в этом формате - ГГГГММДД.

Чтобы получить дату из этой строки (она может быть отформатирована как текст или число), вы можете использовать:

- функции LEFT, MID и RIGHT (традиционная, более длинная формула)

- функция ТЕКСТ (короткая формула)

Получить дату с функциями LEFT, MID, Right

Наиболее распространенный способ получить дату из строки или числа ГГГГММДД - использовать функцию ДАТА, при которой ЛЕВО, СРЕДНЕЕ и ВПРАВО извлекают номера года, месяца и дня.

= ДАТА (ЛЕВЫЙ (A2,4), СРЕДНИЙ (A2,5,2), ВПРАВО (A2,2))

В этом видео показано, как выделить год, месяц и день в отдельные столбцы, используя эти 3 формулы:

  • Год: = ЛЕВЫЙ (A2,4)
  • Месяц: = MID (A2,5,2)
  • День: = ВПРАВО (A2,2)

Получить дату с помощью функции ТЕКСТ

Вместо использования длинных формул LEFT, MID, RIGHT UniMord предоставил гораздо более короткую формулу.

= - ТЕКСТ (A2, «0000-00-00»)

В этой короткой формуле:

  • Функция ТЕКСТ форматирует 8-значное число ГГГГММДД как «0000-00-00»
  • 2 знака минус преобразуют результат в положительное число.

ПРИМЕЧАНИЕ : Это, вероятно, может работать не для всех региональных настроек, поэтому внимательно проверьте его перед использованием в своих книгах. Unimord основал эту формулу на ответе StackOverflow от Рона Розенфельда.

Загрузить образец файла

Чтобы увидеть, как работают эти формулы, загрузите образец Date Рабочая тетрадь. Файл заархивирован и находится в формате xlsx. формат

Связанные руководства

Список функций

Расчет високосного года

Формат фиксации дат Excel

N-й день недели в месяце

30 функций за 30 дней

СЧЁТ / СЧЁТЕСЛИ

СУММ / СУММЕСЛИ

Выбор даты

Часто задаваемые вопросы о дате и времени

Не пропустите наши советы по Excel

Не пропустите мои последние советы и видео по Excel! Нажмите OK, чтобы получать мой еженедельный информационный бюллетень с советами по Excel и ссылками на другие новости и ресурсы Excel.

Расчет количества месяцев между двумя датами в Excel (простые формулы)

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

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

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

Итак, приступим!

Использование функции РАЗНДАТ (получение количества завершенных месяцев между двумя датами)

Маловероятно, что вы получите даты с идеальным количеством месяцев. Скорее всего, эти две даты охватывают какое-то количество месяцев или несколько дней.

Например, с 1 января 2020 года по 15 марта 2020 года есть 2 месяца и 15 дней.

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

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

Ниже приведена формула РАЗНДАТ, которая сделает это:

 = РАЗНДАТ (A2, B2, "M") 

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

РАЗНДАТ - одна из немногих недокументированных функций в Excel. Когда вы вводите = РАЗНДАТ в ячейку в Excel, вы не увидите никакого IntelliSense или каких-либо указаний о том, какие аргументы он может принимать.Итак, если вы используете DATEDIF в Excel, вам необходимо знать синтаксис.

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

 = РАЗНДАТ (A2, B2, «M») & «M» & РАЗНДАТ (A2, B2 , "MD") & "D" 

Примечание: функция РАЗНДАТ исключает дату начала при подсчете номеров месяцев. Например, если вы начинаете проект 1 января, а он заканчивается 31 января, функция РАЗНДАТ выдаст количество месяцев как 0 (поскольку она не учитывает дату начала и, согласно ей, только 30 дней в январе были покрыто)

Использование функции YEARFRAC (получить общее количество месяцев между двумя датами)

Другой метод получения количества месяцев между двумя указанными датами - использование функции YEARFRAC.

Функция YEARFRAC примет дату начала и дату окончания в качестве входных аргументов и даст вам количество лет, прошедших в течение этих двух дат.

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

Например, если моя дата начала - 1 января 2020 года, а дата окончания - 31 января 2020, результат функции YEARFRAC будет 0,833. Когда у вас есть значение года, вы можете получить значение месяца, умножив его на 12.

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

Ниже приведена формула, которая сделает это:

 = YEARFRAC (A2, B2) * 12 

Это даст вам месяцы в десятичных дробях.

Если вы хотите получить только количество полных месяцев, вы можете заключить приведенную выше формулу в INT (как показано ниже):

 = INT (YEARFRAC (A2, B2) * 12) 

Еще одно важное отличие между функцией DATEDIF и функцией YEARFRAC заключается в том, что функция YEARFRAC будет рассматривать дату начала как часть месяца. Например, если дата начала - 01 января, а дата окончания - 31 января, результат по приведенной выше формуле будет 1

. Ниже приводится сравнение результатов, полученных из DATEDIF и YEARFRAC.

Использование формулы ГОД и МЕСЯЦ (подсчитать все месяцы, когда проект был активен)

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

Предположим, у вас есть набор данных, как показано ниже:

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

 = (ГОД (B2) -ГОД (A2)) * 12 + МЕСЯЦ (B2) -МЕСЯЦ (A2) 

Эта формула использует функцию ГОД (которая дает номер года с использованием даты) и функцию МЕСЯЦ (которая дает номер месяца с использованием даты).

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

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

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

 = (ГОД (B2) -ГОД (A2)) * 12+ (МЕСЯЦ (B2) -МЕСЯЦ ( A2) +1) 

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

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

  • Используйте метод функции РАЗНДАТ, если вы хотите получить общее количество завершенных месяцев между двумя датами (он игнорирует дату начала )
  • Используйте метод YEARFRAC, если вы хотите получить фактическое значение месяцев, прошедших между датами буксировки. Он также дает результат в десятичном виде (где целочисленное значение представляет количество полных месяцев, а десятичная часть представляет количество дней)
  • Используйте метод YEAR и MONTH, если вы хотите узнать, сколько месяцев покрыто между двумя датами ( даже если промежуток между датой начала и датой окончания составляет всего несколько дней)

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

Надеюсь, вы нашли это руководство по Excel полезным .

Вам также могут понравиться следующие советы и руководства по Excel:

Формула Excel - добавление месяцев к дате

Добавление месяцев к дате

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

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

A
1 = СЕГОДНЯ () = суббота, 1 мая 2021 г. Май 2021 г. 08:04:17
3 = EDATE (A1,1) = 44348
4 = EDATE (A1,3) = воскресенье, 1 августа , 2021 12:00 AM
5 = ДАТА ( ГОД (A1), МЕСЯЦ (A1) + 3 + 1,0) = вторник, 31 августа 2021 12:00 AM
6 = ДАТА ( ГОД (A1), МЕСЯЦ (A1 + 3), МИН ( ДЕНЬ (A1), ДЕНЬ (A5))) = Суббота, 1 мая 2021 г. , 00:00

Встроенные функции

DATE - The date как порядковый номер даты с указанием года, месяца, дня.
ДЕНЬ - День как целое число с порядковым номером даты.
ДАТА - порядковый номер даты, который представляет собой определенное количество месяцев до или после даты.
MIN - Наименьшее значение в списке или массиве чисел.
МЕСЯЦ - Месяц как целое число с порядковым номером даты.
СЕЙЧАС - порядковый номер текущей системной даты и времени.
СЕГОДНЯ - порядковый номер, представляющий сегодняшнюю дату.


Связанные формулы

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


© 2021 Better Solutions Limited. Все права защищены. © 2021 Better Solutions Limited Лучшие

формул Excel для дат первого и последнего дней месяца

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

Последнее обновление: 26 мая 2020 г., автор: Дэвид Уоллис.


Дата последнего дня текущего месяца

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

= ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()) + 1,1) -1

Если ваша версия Excel поддерживает функцию EOMONTH, используйте ее в конце этого месяца следующим образом:

= EOMONTH (СЕГОДНЯ (); 0)

Объяснение отдельных функций в составных формулах:

  • СЕГОДНЯ Возвращает дату, взятую с часов вашего компьютера, и возвращает дату завтрашнего дня, когда на часах вашего компьютера наступает полночь.СЕГОДНЯ нужны открытая и закрытая скобки - = СЕГОДНЯ () - но ничего между ними.
  • ГОД. Возвращает в виде четырехзначного числа год даты, которую вы передаете функции. Итак, = ГОД (СЕГОДНЯ ()) возвращает год сегодняшней даты. = YEAR ("13 февраля 1959") возвращает 1959. And = YEAR (47301) возвращает 2029.
  • МЕСЯЦ Возвращает в виде числа от 1 до 12 месяц даты, которую вы передаете функции. Итак, = МЕСЯЦ (СЕГОДНЯ ()) возвращает номер месяца сегодняшней даты. = МЕСЯЦ ("13 февраля 1959") возвращает 2.И = МЕСЯЦ (47301) возвращает 7.
  • DATE DATE имеет три аргумента - DATE ( год , месяц , день ), каждый из которых является подходящим числом. Укажите их, и ДАТА вернет целое число, которое представляет положение этой даты во встроенном календаре Excel -. = DATE (2020,2,13) ​​фактически вернет 43874.
  • EOMONTH EOMONTH имеет два аргумента - EOMONTH ( дата , месяц ). Вы даете EOMONTH дату в качестве отправной точки и номер для месяцев в будущем или прошлом, для которых вам нужна дата окончания месяца. Представьте, что сегодня 13 февраля 2020 года.

= EOMONTH ("13-февраля-2020", 0) возвращает 29 февраля 2020 г., Excel правильно распознает 2020 г. как високосный год. Ноль означает, что вы не , а перемещаете месяц даты, предоставленной функции.


Дата последнего дня в будущих месяцах

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

= ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()) + 2,1) -1

= EOMONTH (СЕГОДНЯ (); 1)

И эти, последний день месяца после следующего:

= ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()) + 3,1) -1

= EOMONTH (СЕГОДНЯ (); 2)

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

Вот еще несколько примеров работы EOMONTH:

= EOMONTH ("13-февраля-2020", - 1) возвращает вас на назад на в месяц до конца января 2020 года.

= EOMONTH («13-февраля-2020», -12) возвращает назад 12 месяцев до конца февраля 2019 года, Excel правильно распознает, что 2019 год не високосный.

= EOMONTH («13/02/2020», 12) займет у вас 12 месяцев до конца февраля 2021 года, Excel правильно распознает, что 2021 год не високосный.

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


Встроенный календарь Excel

Excel сохраняет дату в виде целого числа на основе календаря, который начинается с первого дня 1 января 1900 года. Введите цифру 1 в ячейку и примените формат краткой даты, чтобы подтвердить это.

Следовательно, 43874 представляет 13 февраля 2020 года, то есть 43874 дня после 1 января 1900 года.

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


Ваша поддержка для DMW TIPS

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

Чтобы сделать взнос через PayPal в фунтах стерлингов (£ стерлингов) -

Чтобы сделать взнос через PayPal в долларах США ($ US) -

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

Счет-фактура

Спасибо, в ожидании .


Заявление об отказе от ответственности

DMW Consultancy Ltd не принимает любая ответственность за потерю или повреждение данных, к которым применяются любые методы, методы или код, включенные в этот веб-сайт.Сделайте резервную копию ваших данных; тщательно протестировать перед использование на живых данных.

.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *