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

Обновлено: 21.11.2024

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

Таким образом, Excel знает, какая часть введенной вами даты является месяцем, какая является годом, а какая днем.< /p>

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

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

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

В этом руководстве мы рассмотрим три способа преобразования даты в месяц и год в Excel.

Оглавление

Пример данных

В этом руководстве мы будем использовать следующий набор дат. Мы будем конвертировать эти даты в месяц и год в Excel:

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

В Великобритании и других странах даты начинаются с дня и заканчиваются годом (дд/мм/гггг). В других странах, таких как Китай, Иран и Корея, порядок полностью изменен (гггг/мм/дд).

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

Преобразование даты в месяц и год с помощью функции МЕСЯЦ и ГОД

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

Нажмите на пустую ячейку, где должен отображаться месяц (B2).
Введите: =МЕСЯЦ, за которым следует открывающая скобка (.
Нажмите на первую ячейку, содержащую исходную дату (A2).
Добавить закрывающую скобку )
Нажмите клавишу возврата.
Это должно отображать месяц года, соответствующий исходной дате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.

Вы увидите столбец B, заполненный месяцем года для всех дат столбца A.

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

Нажмите на пустую ячейку, где должен отображаться год (C2)
Введите: =YEAR, за которым следует открывающая скобка (.
Нажмите на первую ячейку, содержащую исходную дату (A2).
Добавить закрывающую скобку )
Нажмите клавишу возврата.
Должен отображаться год, соответствующий исходной дате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.

Вы увидите столбец C, заполненный годом, соответствующим всем датам столбца A.

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

Допустим, вы хотите отображать месяц и год как "2-2018" для даты "10.02.2018" и хотите следовать этому шаблону для всех дат.

Нажмите на пустую ячейку, в которой вы хотите отобразить новый формат даты (D2)
Введите формулу: =B2 & "-" & C2. Можно также ввести: =МЕСЯЦ(A2) & "-" & ГОД(A2).
Нажмите клавишу возврата.
Это должно отображать исходную дату в требуемом формате.Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.

Теперь все ваши ячейки в столбце D2 имеют новый формат:

В качестве альтернативы, если вы хотите отображать месяц и год как 2/2018, вам нужно всего лишь заменить «-» между ними на «/». Таким образом, вы можете отображать месяц и год в любом формате, который вам нравится.

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

Для этого скопируйте ячейки столбца D и вставьте их как значения в тот же столбец (щелкните правой кнопкой мыши и выберите Параметры вставки->Значения во всплывающем меню). Теперь вы можете удалить столбцы от A до C. У вас останутся только преобразованные значения, содержащие месяц и год.

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

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

Функция ТЕКСТ в Excel преобразует любое числовое значение (например, дату, время и валюту) в текст в указанном формате.

Синтаксис функции ТЕКСТ:

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

значение – числовое значение или ссылка на ячейку, которую вы хотите преобразовать.
format_code – это формат, в который вы хотите преобразовать ячейку.

В приведенном выше примере функция ТЕКСТ применяет код_формата, указанный вами в значении, и возвращает текстовую строку в этом формате. Например, если у вас есть дата «10.02.2018» в ячейке A2, тогда =ТЕКСТ(A2, «мм/гггг») вернет «02/2018»

Существует ряд кодов форматов, которые вы можете использовать. Мы перечислили ниже основные строительные блоки для кодов форматов:

Коды формата для года:

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

гг – двузначное представление года (например, 20 или 12)
гггг – четырехзначное представление года (например, 2020 или 2012)

Итак, если вы примените =TEXT(A2, yy) в нашем примере набора данных, он вернет «18».

Если вы примените =TEXT(A2, yyyy), будет возвращено «2018».

Коды формата для месяца года:

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

m – одно- или двухзначное представление месяца (например, 8 или 12).
mm – двухзначное представление месяца (например, 08 или 12).
ммм – месяц, сокращенно состоящий из трех букв (например, август или декабрь).
мммм — месяц, указанный полным названием (например, август или декабрь).

Итак, если вы примените =TEXT(A2, m) в нашем примере набора данных, он вернет «2».

Если применить =ТЕКСТ(A2, мм), будет возвращено значение "02".
Если вы примените =ТЕКСТ(A2, ммм), будет возвращено "Фев".
Если применить =ТЕКСТ(A2, мммм), будет возвращено значение «Февраль».

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

Сначала мы увидим, как преобразовать даты в столбце A в формат, показанный в столбце B на изображении ниже:

Ниже приведены шаги по изменению формата даты и получению только месяца и года с помощью функции ТЕКСТ:

Нажмите на пустую ячейку, в которой вы хотите отобразить новый формат даты (B2)
Введите формулу:
Нажмите клавишу возврата.
Это должно отображать исходную дату в требуемом формате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.
Скопируйте результаты формулы этого столбца, нажав CTRL+C или Cmd+C (если вы работаете на Mac).
Щелкните правой кнопкой мыши столбец и в появившемся всплывающем меню нажмите «Вставить значения» в параметрах вставки.
При этом результаты формулы будут сохранены как постоянные значения в том же столбце. Теперь вы можете удалить столбец А, если хотите.

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

Функция	Результат
=ТЕКСТ(A2, «мм/гг»)< /td>	02/18
=ТЕКСТ(A2, «мм-гг»)	02-18
=ТЕКСТ(A2, «мм-гггг»)	02-2018
=ТЕКСТ(A2, «ммм , гггг")	Февраль 2018 г.
=TEXT(A2, "мммм, гггг")	Февраль 2018 г.< /td>

Итак, вы можете использовать функцию ТЕКСТ для преобразования дат в любой формат по вашему выбору. Все, что вам нужно сделать, это изменить код формата в соответствии с вашими требованиями.

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

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

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

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

Выделите все ячейки, содержащие даты, которые вы хотите преобразовать (A2:A6).
Нажмите правой кнопкой мыши на выделенный элемент и выберите Форматировать ячейки в появившемся всплывающем меню. Кроме того, вы можете выбрать средство запуска диалогового окна в группе Число на вкладке Главная.
Откроется диалоговое окно Формат ячеек. Перейдите на вкладку Число
В разделе Категория в левой части поля выберите параметр Дата.
Справа отобразится несколько вариантов форматирования даты.
Выберите нужный формат. Например, если вы хотите, чтобы первая дата отображалась в формате «18 февраля», выберите соответствующий вариант формата.
Если вы не можете найти параметр для формата, который хотите использовать, вы можете использовать параметр Пользовательский в списке Категория слева. Это позволяет преобразовать ячейку в произвольный формат.
Посмотрите, доступен ли ваш формат среди кодов формата даты в разделе "Тип". Если нет, вы можете ввести код формата в поле ввода чуть ниже Тип. Итак, если вы хотите отобразить первый месяц в формате «2/18», введите «м/гг».
Нажмите "ОК", чтобы закрыть диалоговое окно Формат ячеек.

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

Этот метод отличается от предыдущего (с использованием функции ТЕКСТ) четырьмя способами:

С помощью этого метода вы можете выполнять операцию непосредственно с исходными ячейками.
Вы можете выполнить конверсию за один раз. Таким образом, вам не нужно иметь отдельную ячейку, чтобы ввести формулу, затем вставить по значению, а затем удалить исходные ячейки (как это было бы необходимо при использовании функции ТЕКСТ).
Этот метод изменяет только формат исходной даты, однако базовая дата остается прежней. Поэтому, если вы хотите позже восстановить исходное значение даты (вместе с днем), вы можете легко получить к нему доступ. Однако при использовании функционального метода ТЕКСТ исходное значение даты теряется, поскольку преобразование изменяет все значение даты.
Результаты, которые вы получаете при использовании этого метода, имеют тип Дата, а не Текст, поэтому вы можете выполнять над ними операции с датами напрямую, без необходимости их преобразования.< /li>

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

Мы надеемся, что наши методы оказались вам полезными и вы примените их к своим собственным данным Excel.

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

Концепция даты и времени в Excel

Допустим, вы хотите округлить значение даты до ближайшего месяца.Вы не можете использовать обычные функции округления, такие как ОКРУГЛ, ОКРУГЛВВЕРХ или ОКРУГЛВНИЗ, из-за их ограничения на основе десятичного числа. Вы можете подумать об использовании чисел с основанием 24 или 30 или множителей с такими функциями, как ОКРУГ, ПОТОЛОК.МАТЕМАТИКА или ПОЛ.МАТЕМАТИКА. Хотя такой подход кажется рациональным, результаты будут неправильными из-за множества понятий календарной системы, таких как недели, месяцы или кварталы.

С другой стороны, вы можете легко использовать даты в вычислениях в Excel. Excel сохраняет значения даты и времени в виде чисел. Согласно Excel, история начинается с 1 января 1900 года и принимает эту дату как 1. В то время как целые числа представляют дни, десятичные числа представляют время. Например, если 01.01.2018 равно 43101, 12:00 равно 0,5. Значение даты или времени, которое вы видите на листе, представляет собой не более чем числовое форматирование.

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

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

Как округлить значения даты

Дата округления до следующего рабочего дня

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

Вы можете добавить или вычесть количество рабочих дней из заданной даты, используя каждую функцию. Функция РАБДЕНЬ.МЕЖД отличается поддержкой различных комбинаций выходных.

Округление даты до предыдущего или следующего дня недели

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

В нашей модели мы используем параметр 2, который перечисляет день от 1 до 7, предполагая, что 1 – это понедельник. Итак, воскресенье — 7.

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

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

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

Округление даты до ближайшего месяца

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

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

Сложным моментом является параметр месяца функции DATE. Мы добавляем текущий месяц МЕСЯЦ(B31) с логическим уравнением и нулем. Добавление чего-то с нулем кажется бессмысленным, однако это позволяет нам преобразовать логическое значение в числовое представление. Для Excel ИСТИНА равно 1, а ЛОЖЬ равно 0. Использование любого значения в математической операции преобразует логическое значение в число.

В качестве альтернативы вы можете использовать функцию N или двойной унарный (--) оператор.

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

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

Привет всем,
я пытаюсь округлить записи даты до первого числа месяца, чтобы я мог
свести сводную таблицу в поле даты.
Я пытался использовать функции "Месяц" и "Год" в формуле во вспомогательном
столбце, но результат не распознается как дата (даже если я добавляю "1" в
между два).
Не могу найти ответ в предыдущем посте.
Есть ли у кого идеи?
Заранее спасибо.

Следует простой пример:

ABC
Сакраменто, 04.08.04, 45 долларов
Сакраменто, 09.08.04, 65 долларов,
Сан-Франциско, 01.09.04, 200 долларов,
Лос-Анджелес, 3 декабря 2004 года, 60 долларов.
Лос-Анджелес, 10 декабря 2004 г., 75 долл. США

Я пытаюсь получить сводную таблицу для суммирования 8/04 или 8/1/04, 9/04 и 12/04.

если дата находится в ячейке B1, попробуйте:

=ДАТА(ГОД(B1),МЕСЯЦ(B1),1) во вспомогательном столбце

"Доминик" написал в сообщении
news: B2E560EE-4776-4D1D-A0AF-D719D7BB0D58@microsoft.com.
> Привет всем,
> Я пытаюсь округлить записи даты до первого числа месяца, чтобы я мог
> Сводную таблицу в поле даты.
> Я пытался использовать функции месяца и года в формуле во вспомогательном столбце
>, но результат не распознается как дата (даже если я добавляю "1" в
> >> Между ними).
> Не могу найти ответ в предыдущем посте.
> Есть у кого идеи?
> Заранее спасибо.
>
> Ниже приведен простой пример:
>
> ABC
> Сакраменто, 04.08.04, 45 долларов
> Сакраменто, 09.08.04, 65 долларов.
> Сан-Франциско 01.09.04 200 долларов
> Лос-Анджелес 03.12.04 60 долларов
> Лос-Анджелес 10.12.04 75 долларов
>
> I Я пытаюсь получить сводную таблицу для суммирования 8/04 или 8/1/04, 9/04 и
> 12/04.
>
> Еще раз спасибо.

RP
(удалить ничего из адреса электронной почты, если вы отправляете письмо напрямую)

Большое спасибо вам и Тревору. Отлично работает.

«Боб Филлипс» написал:

> =ДАТА(ГОД(B2),МЕСЯЦ(B2),1)
>
> должен сделать это
>
> --
>
> HTH
>
> RP
> (ничего не удаляйте из адреса электронной почты при прямой рассылке)
>
>
> "Доминик" написал в сообщении
> новости: B2E560EE-4776-4D1D-A0AF-D719D7BB0D58@microsoft.com.
> > Привет всем,
> > Я пытаюсь округлить записи даты до первого числа месяца, чтобы я мог
> > Сводную таблицу в поле даты.
> > Я пытался использовать функции "Месяц" и "Год" в формуле во вспомогательном столбце
> >, но результат не распознается как дата (даже если я добавляю "1" в < br />>> между двумя).
> > Не могу найти ответ в предыдущем посте.
> > Есть у кого идеи?
> > Заранее спасибо.
>>>
> > Ниже приведен простой пример:
> >
> > ABC
> > Сакраменто 4/8/04 45 долларов
> > Сакраменто 09.08.04 65 долларов
> > Сан-Франциско 01.09.04 200 долларов
> > Лос-Анджелес 3.12.04 60 долларов
> > Лос-Анджелес 10.12.04 75 долларов
> >
> > Я пытаюсь получить сводную таблицу для суммирования 8/04 или 8/1/04, 9/04 и
> 12/04.
>>>
> > Еще раз спасибо.
>
>
>

Информация о теме

Пользователи, просматривающие эту тему

В настоящее время эту тему просматривают 1 пользователь. (0 участников и 1 гость)

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

Функция МЕСЯЦ извлекает месяц из заданной даты в виде числа от 1 до 12. Например, учитывая дату "12 июня 2021 г.", функция МЕСЯЦ вернет 6 для июня. МЕСЯЦ принимает только один аргумент, серийный_номер, который должен быть допустимой датой Excel.

Даты можно передавать в функцию МЕСЯЦ в виде текста (например, "13 августа 2021") или в виде собственных дат Excel, которые представляют собой большие порядковые числа.Чтобы создать значение даты с нуля с отдельными входными данными для года, месяца и дня, используйте функцию ДАТА.

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

Функция МЕСЯЦ возвращает число. Если вам нужно название месяца, см. этот пример.

Примеры

Чтобы использовать функцию МЕСЯЦ, укажите дату:

Для даты "15 сентября 2017 г." в ячейке A1 функция МЕСЯЦ возвращает 9:

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

См. ниже дополнительные примеры формул, использующих функцию МЕСЯЦ.

Примечание. Даты – это порядковые номера в Excel, начинающиеся с 1 января 1900 года. Даты до 1900 года не поддерживаются. Чтобы значения дат отображались в удобном для человека формате, примените числовой формат по вашему выбору.

Читайте также: