Excel: как преобразовать дату в квартал и год
Вы можете использовать одну из следующих формул для преобразования даты в квартал и год в Excel:
Формула 1: преобразовать дату только в квартал (например, Q1)
="Q" &INT((MONTH( A1 )+2)/3) Формула 2: преобразование даты в квартал и год (например, Q1-2022)
="Q" &INT((MONTH( A1 )+2)/3) & "-" & YEAR( A1 ) Формула 3: преобразование даты в полное название квартала и год (например, 1 квартал 2022 г.)
="Quarter " &INT((MONTH( A1 )+2)/3) & " " & YEAR( A1 ) Обратите внимание, что каждая формула предполагает, что дата находится в ячейке A1 .
В следующих примерах показано, как использовать каждую формулу на практике.
Пример 1: преобразование даты только в квартал
Мы можем использовать следующую формулу для преобразования даты только в квартал:
="Q" &INT((MONTH( A2 )+2)/3) Мы можем ввести эту формулу в ячейку B2 и перетащить ее в каждую оставшуюся ячейку в столбце B:
Квартал для каждой даты в столбце A показан в столбце B.
Пример 2: преобразование даты в квартал и год
Мы можем использовать следующую формулу для преобразования даты в квартал и год:
="Q" &INT((MONTH( A2 )+2)/3) & "-" & YEAR( A2 ) Мы можем ввести эту формулу в ячейку B2 и перетащить ее в каждую оставшуюся ячейку в столбце B:
Квартал и год для каждой даты в столбце A показаны в столбце B.
Пример 3. Преобразование даты в полное название квартала и год
Мы можем использовать следующую формулу для преобразования даты в полное название квартала и год:
="Quarter " &INT((MONTH( A2 )+2)/3) & " " & YEAR( A2 ) Мы можем ввести эту формулу в ячейку B2 и перетащить ее в каждую оставшуюся ячейку в столбце B:
Квартал и год для каждой даты в столбце A показаны в столбце B.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Добавление или вычитание дат в Excel для Mac
Допустим, вам нужно добавить две недели к дате окончания проекта или определить продолжительность отдельной задачи в список задач. Простая формула или функция листа, предназначенная специально для работы с датами, позволит вам добавить к дате нужное количество дней, месяцев и лет или вычесть их из даты.
Добавление и вычитание дней из даты
Допустим, что выплата средств со счета производится 8 февраля 2012 г. Необходимо перевести средства на счет, чтобы они поступили за 15 календарных дней до заданного срока. Кроме того, известно, что платежный цикл счета составляет 30 дней, и необходимо определить, когда следует перевести средства для платежа в марте 2012 г., чтобы они поступили за 15 дней до этой даты. Для этого выполните указанные ниже действия.
- Откройте новый лист в книге.
- В ячейке A1 введите 08.02.2012.
- В ячейке B1 введите =A1-15 и нажмите клавишу RETURN. Эта формула вычитает 15 дней из даты в ячейке A1.
- В ячейке C1 введите =A1+30 и нажмите клавишу RETURN. Эта формула добавляет 30 дней к дате в ячейке A1.
- В ячейке D1 введите =C1-15 и нажмите клавишу RETURN. Эта формула вычитает 15 дней из даты в ячейке C1. В ячейках A1 и C1 отображаются даты выполнения (08.02.12 и 09.03.12) для остатков на счетах за февраль и март. Ячейки B1 и D1 отображают даты (24.01.12 и 23.02.12), по которым вы должны перевести свои средства, чтобы эти средства поступили за 15 календарных дней до наступления срока.
Добавление и вычитание месяцев из даты
Допустим, вам нужно прибавить к дате определенное количество полных месяцев или вычесть их из нее. Быстро сделать это вам поможет функция ДАТАМЕС.
В функции ДАТАМЕС используются два значения (аргумента): начальная дата и количество месяцев, которые нужно добавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента, например =ДАТАМЕС(«15.02.2012»;-5). Эта формула вычитает 5 месяцев из даты 15.02.2012, и ее результатом будет дата 15.09.2011.
Значение начальной даты можно указать с помощью ссылки на ячейку, содержащую дату, или ввести дату в кавычках, например «15.02.2012».
Предположим, нужно добавить 16 месяцев к дате 16 октября 2012 г.
- В ячейке A5 введите 16.10.2012.
- В ячейке B5 введите =ДАТАМЕС(A5;16) и нажмите клавишу RETURN. Функция использует значение из ячейки A5 в качестве начальной даты.
- В ячейке C5 введите =ДАТАМЕС(«16.10.2012»;16) и нажмите клавишу RETURN. В этом случае функция использует введенное значение даты («16.10.2012»). В обеих ячейках B5 и C5 отображается дата 16.02.2014. Почему в результатах отображаются цифры, а не даты? В зависимости от формата ячеек, содержащих введенные формулы, Excel может отображать результаты в виде серийных номеров. в этом случае 16.02.14 может отображаться как 41686. Если результаты отображаются как серийные номера, выполните следующие действия, чтобы изменить формат:
- Выделите ячейки B5 и C5.
- На вкладке Главная в группе Формат выберите элемент Формат ячеек, а затем — элемент Дата. Числовые значения в ячейках должны отобразиться в виде дат.
Добавление и вычитание лет из даты
Допустим, вам нужно прибавить определенное количество лет к определенным датам или вычесть их из этих дат. Соответствующие действия описаны в приведенной ниже таблице.
Количество прибавляемых или вычитаемых лет
Как дату преобразовать в год в эксель
В Excel вы можете отформатировать ячейку даты как несколько типов форматов даты, но в некоторых случаях вы хотите преобразовать дату только в месяц, год, день или месяц год, день месяц или год в текстовом формате. Здесь, в этом руководстве, представлена общая формула для выполнения этой работы в Excel.
Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.
Общая формула:
TEXT(date,”date_format”) аргументы
Date: the date or cell you want to convert to a date as text. Date_format: the date format you want to convert to. Дата Форат
Формат даты аргументы Преобразовать дату только в год гггг Преобразовать дату только в месяц mm Преобразовать дату только в день dd Преобразовать дату в год месяц ггггмм Преобразовать дату в день года ггггдд Преобразовать дату в день месяца ммдд Возвращаемое значение
Формула возвращает серийный номер в текстовом формате.
Как работает эта формула
В ячейке B3 дату, которую необходимо преобразовать в год, месяц, день или другие типы даты в текстовом формате, примените одну из следующих формул:
Преобразовать дату только в год
=TEXT(B3,»yyyy») Преобразовать дату только в месяц
Преобразовать дату только в день
Преобразование даты в год и месяц
=TEXT(B3,»yyyymm») Преобразование даты в год и день
=TEXT(B3,»yyyydd») Преобразовать дату в месяц и день / сильный>
=TEXT(B3,»mmdd») 
Нажмите Enter ключ для получения серийного номера в текстовом формате.
объяснение
ТЕКСТ функция: преобразовать число в текст в определенном формате.
Относительные формулы
- Преобразовать дату в юлианский
Иногда вам нужно преобразовать стандартную дату в дату по юлианскому календарю в Excel, здесь в этой статье представлена формула, которая объединяет функции ТЕКСТ, ГОД и ДАТА для решения этой задачи. - Преобразовать строку даты в дату и время
Иногда, когда вы импортируете или вставляете некоторые данные datetime с других устройств в Excel, datetime может не распознаваться как правильные даты . - Расчет дней, оставшихся между двумя датами
Здесь в этом руководстве представлена формула для быстрого расчета оставшихся дней между двумя датами. - Вычислить часы, минуты, секунды между двумя значениями времени
Здесь, в этом руководстве, вводятся некоторые формулы для быстрого представления результата разницы во времени как единой единицы времени.
Относительные функции
- Функция ЕСЛИ в Excel
Тест на указанные условия, затем возвращает соответствующие значения - Функция Excel VALUE
Преобразуйте текст в число. - Функция МЕСЯЦ в Excel
МЕСЯЦ используется для получения месяца в виде целого числа (от 1 до 12) от даты. - Функция ДЕНЬ в Excel
Функция ДЕНЬ получает день в виде числа (от 1 до 31) от даты - Функция ГОД в Excel
Функция ГОД возвращает год на основе заданной даты в формате 4-значного серийного номера.
Как дату преобразовать в год в эксель
Очень часто можно столкнуться с такой проблемой: когда вы вставляете в ячейки Excel числа, в которых в качестве десятичного разделителя используется точка, то некоторые такие числа Excel преобразовывает в даты, а другие визуально похожи на те же числа, но на самом деле являются текстом, с которым невозможно работать в дальнейшем как с числами.
- Несоответствие регионального формата исходных данных региональным настройкам вашей операционной системы. В исходных данных в качестве десятичного разделителя используется точка (американский формат), а у вас – региональные настройки, в которых десятичным разделителем является запятая, а точка воспринимается как разделитель отдельных составляющих даты – день, месяц, год (например, 17.09.202.
- Особое поведение Excel для ячеек в «Общем» числовом формате (который установлен по умолчанию): если вводить в ячейку что-то похожее на число, то такое значение сохраняется как число, если вводить что-либо, похожее на дату – то такое значение сохраняется как дата. Если вводимое значение не похоже ни на число, ни на дату – то это текст.
В данном примере (выше) поврежденные данные:
Исходные данные сохранились как текст:
- Ячейка D3 – исходное значение (179.3287) мало похоже на дату, так как номер месяца 179 не существует (а вот год 3287 возможен, в далеком будущем).
- Ячейка D4 – исходное значение похоже на дату (июль 1275 года). Однако в Excel точкой отсчета дат является 1 января 1900 года. Поэтому число 1275 (ранее 1900 года) не воспринимается как год.
Исходные данные были преобразованы в даты (и, соответственно, утеряны):
- Ячейка D5 – в исходном значении (7.1972) число до точки похоже на номер месяца 7 (июль), а число после точки – на год 1972 (на самом деле произошло преобразование в дату 01.07.1972)
- Ячейка D6 – в исходном значении (11.2024) число до точки воспринимается как номер месяца (ноябрь), а после точки – год 2024 (произошло преобразование в дату 01.11.2024)
Какие есть способы решения данной проблемы?
Начнем с самых кардинальных и, соответственно, не самых лучших.
Способ-1. Поменять региональные настройки операционной системы (на американские).
Да, но такое глобальное изменение повлияет и на работу других приложений и служб (не только Excel).
Способ-2. Локально в Excel (в параметрах Excel) заблаговременно изменить десятичный разделитель с запятой на точку:
Способ-3. Заблаговременно установить «текстовый» формат ячеек (вместо «общего»), в которые будут вставляться значения.
Тогда мы избавимся от авто преобразования «чисел», похожих на даты, в непосредственно даты, и получим значения в исходном виде, только в формате текста. Далее этот текст легко будет преобразовать в числа с помощью простой формулы:
=SUBSTITUTE(A3;».»;»,»)*1 (в диапазоне C3:C9)
либо еще лучше, формулы:
=NUMBERVALUE(A3;».») (в диапазоне E3:E9)
Русские варианты этих формул:
Способ-4. Формулой (без какой-либо предварительной подготовки):
Можно попробовать восстановить уже испорченные числа формулой
Формула в ячейке G3:
=1*IF(CELL(«format»;D3)=»G»; SUBSTITUTE(D3;».»;»,»); TEXT(D3;»M,YYYY»))
или русский вариант:
=1*ЕСЛИ(ЯЧЕЙКА(«формат»;D3)=»G«; ПОДСТАВИТЬ(D3;».»;»,»); ТЕКСТ(D3;»М,ГГГГ»))
Формула работает следующим образом:
CELL() (ЯЧЕЙКА()) – функция из категории «Информационные». Если первый ее аргумент (тип информации) указан “format”, то результат работы функции — «G» — для текста или чисел и «D3» — для дат.
Если в ячейке находится текст, то функция SUBSTITUTE() (ПОДСТАВИТЬ()) заменяет точку на запятую и умножение на 1 в начале формулы (применение арифметической операции) приводит к преобразованию текста, состоящего из цифр с запятой, — в число.
Если в ячейке не текст (а дата), то функция TEXT() (ТЕКСТ()) преобразует дату в текст в соответствии с кодом формата, указанного в качестве второго ее аргумента. И далее такой текст легко преобразуется в число путем умножения на 1 (в начале формулы).
Используя код формата в функции TEXT, нужно учитывать то, что и даже в англоязычной версии, в зависимости от региональных настроек, вместо “M,YYYY”, возможно, нужно указывать “М,ГГГГ” (где “M” – кириллическое).
ВАЖНО! Формула не является универсальным решением (на все случаи жизни)
Она хорошо работает, если после точки находится от 3-х и более цифр.
А что, если имеем такое:
В первых трех ячейках уже цифры до точки воспринимаются как номер дня (а не номер месяца), а цифры после точки – как номер месяца (а не номер года).
А в последних двух ячейках разные исходные числа привели к получению одной и той же даты.
Если в первом случае можно было бы еще добавить проверку дополнительных условий в формуле, то в последнем – исходные значения безвозвратно утеряны и узнать какие они были – уже не представляется возможным. Очевидно, в таком случае, единственный вариант – это не допустить автоматического преобразования «как бы чисел» в даты, то есть указанный выше способ 3 – чуть ли не единственное решение (не считая кардинальных изменений региональных настроек).
Такие и подобные тонкости работы в Excel мы прорабатываем на курсе «Excel бизнес-анализ и прогнозирование»
За 27 часов изучения Excel у вас не останется никаких вопросов его работе, а если они и возникнут, то наши тренера-поддержки онлайн помогут с ответами.
Автор статьи CEO и тренер DATAbi Евгений Довженко