Как получить КВАРТАЛ из даты в Excel


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

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

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

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

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

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

Здесь я перечислил 5 основных способов, с помощью которых вы можете получить квартал с даты. Каждый из этих методов уникален, и вы можете использовать любой из них в соответствии с вашими потребностями.

  1. Функции ROUNDUP и MONTH
  2. Основа финансового календаря
  3. Код VBA
  4. Получить дату начала квартала
  5. Получить дату окончания квартала

1. Получить квартал с помощью функций ROUNDUP и MONTH

Использование комбинации ROUNDUP и MONTH — лучший способ найти четверть даты. Он возвращает квартал в виде числа (например, 1,2,3,4).

Вот формула.

= ROUNDUP (МЕСЯЦ (A1)/3,0)

Здесь мы используем 26 мая 2018 года в качестве даты, а в результате формула вернет 2. Вы можете использовать приведенную ниже формулу, если хотите добавить Q перед числом квартала.

 = «Q -» & ROUNDUP (MONTH (A1)/3,0  ) 

Как работает эта формула

Эта формула состоит из трех частей.

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

После этого в вторая часть, мы разделили номер месяца на 3, что дает нам число с десятичными знаками.

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

❤️ Вот настоящая вещь:

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

Верно?

А если вы делите все числа месяца w с тремя (да, только с 3) вы получите что-то вроде ниже.

И когда вы их округлите вы всегда получите номер квартала месяца.

2. Получить квартал с помощью финансового календаря

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

Что такое финансовый год?

Финансовый год (FY) — это 12-месячный период, который компания или правительство использует для целей бухгалтерского учета и подготовки финансовой отчетности. Финансовый год может не совпадать с календарным…

… подробнее

А для расчета квартала финансового календаря нам нужно использовать другую формулу, и в этой формуле нам нужно объединить функцию ВЫБОР и МЕСЯЦ.

Вот формула:

 = ВЫБРАТЬ (МЕСЯЦ (A1), 4,4,4,1,1,1,2,2,2  , 3,3,3) 

(Здесь я использовал индийский финансовый год, который начинается 1 апреля и заканчивается 31 марта)

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

Как работает эта формула

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

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

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

Здесь вы можете узнать больше о CHOOSE.

Если вы посмотрите, в функции ВЫБОР мы указали 12 разных чисел (4,4,4,1,1,1,2,2,2,3,3,3), поэтому, когда МЕСЯЦ возвращает число, ВЫБОР возвращает число из этот список.

Вот в чем дело:

Для первых трех чисел мы имеет 4, что означает, что если он вернет 1,2 или 3 (январь-март), мы получим 4, а в следующие три месяца мы получим 1 (это потому, что наш финансовый год начинается с апреля).

3. Преобразование даты в квартал с помощью кода VBA

Здесь мы используем код VBA для простого преобразования даты в ее номер квартала.

Вот код:

Sub convert2Quarter ()

Dim Rng As Range

For Each Rng In Selection

Если IsDate (Rng. Value) = True Then

Rng.Value = DatePart («q», Rng.Value)

Rng.NumberFormat = «General»

End If

Next Rng

End Sub

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

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

вот как работает этот код. ..

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

Здесь мы использовали две разные функции VBA:

  1. ISDATE: чтобы проверить, в ячейке есть дата.
  2. DATEPART: преобразование даты в квартал.

Проверьте это: 100 лучших полезных макросов для новичков VBA

4. Получить дату начала квартала по дате

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

Это означает, что если у нас есть дата 26 мая 2018 года, поэтому вместо квартала номер 2 мы можем иметь 01 апреля 2018 года, которое является датой начала первого месяца квартала.

Вот формула, которую нам нужно использовать:

 = DATE (YEAR (A1),  FLOOR.MATH (  МЕСЯЦ (A1) -1,3) +1,1) 

Когда вы вводите эту формулу, она просто возвращает дату начала квартала.

Как работает эта формула

Эта формула состоит из четырех разных частей, которые дают вам дату начала квартала.

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

Итак, когда MONTH возвращает 5 в качестве номера месяца (тогда минус один из него), во второй части , FLOOR округлите его до 3, а затем прибавьте к нему единицу, чтобы получить 4, что соответствует первому месяцу (апрелю) того же квартала.

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

И в четвертой части мы просто объединили функцию ДАТА, чтобы создать дату, объединяющую значения берутся из первых двух частей.

5. Получить дату окончания квартала из даты

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

 = DATE (YEAR (A2), ((INT ((MONTH (A2) -1)/3) +1)  * 3) +1,1) -1 

Для 26 мая 2018 года возвращает 30 июня 2018 год, который является последней датой квартала.

Как работает эта формула

В этой формуле у нас четыре разных части.

В первая часть, мы объединили функции INT и MONTH.

MONTH возвращает номер месяца в дате, а затем мы разделили его с 3. После этого INT округлите его до ближайшего целого.

В конце концов, мы умножили его на 3, чтобы получить 6 (июнь), который является месяцем конца квартала, а затем добавили к нему 1.

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

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

После этого, в третьей части, просто использовал 1, завершив дату функцией ДАТА.

Извините! еще кое-что.

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

Образец файла

➜ загрузите этот образец файла отсюда, чтобы узнать больше

Заключение

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

И в этом посте мы исследовали четыре формулы + код VBA для этого .

Откровенно говоря, №1 — это наиболее используемый и самый простой способ найти квартал даты, но вы можете использовать rest all, если хотите.

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

Знаете ли вы какой-либо другой метод, который позволяет вычислить число квартала по дате?

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

Tweet0

Вы должны прочитать следующее. Далее

  1. Text to Date : Нам действительно нужны методы, которые мы можем использовать для преобразования текста в действительную дату в правильном формате …
  2. Дата конца месяца : лучший способ вычислить последняя дата месяца — с помощью функции EOMONTH. Но …
  3. Получить название дня из даты : вы можете использовать функцию ТЕКСТ для извлечения названия дня из даты. Допустим, у вас есть …
  4. Получить месяц из даты . Иногда нам нужно использовать только часть даты. Возьмем пример …
  5. Выделить даты между двумя датами : допустим, у вас есть дата начала 17 января 2017 г. и дата окончания 19 марта 2017 г. 2017 …
  6. Сумма значений между двумя датами : Допустим, вы работаете в торговой компании, и ваш начальник просит вас получить …
  7. Проверка данных с диапазоном дат : что ж, это можно сделать, используя настраиваемую формулу на основе AND …
  8. Групповые даты в сводной таблице : с его помощью вы можете мгновенно создавать информативные и готовые к представлению отчеты. Когда вы работаете …
  9. РАЗНДАТА : мы можем использовать его так же, как и все функции Excel. Разница только при попытке вставить …

Об авторе

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

Изучите Excel (видео)

Подключиться к Puneet


Есть ли способ «Q» с помощью функции ВЫБРАТЬ? Когда я попробовал, это не работает.

Ответ


Очень хороший сайт Puneet.
Четкие и логичные описания.

Я загрузил вашу рабочую книгу Quarters и обнаружил, что
мне нужно добавить ваш URL в ячейку, чтобы я мог вернуться на ваш сайт.

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

С уважением,
Дэвид

Ответ


Привет

Как я могу рассчитать фактические даты налогового года

, т.е. он должен отображаться на текущую дату — с 06.04.2019 по 05.04.2020

Я был бы признателен за любую помощь.

Спасибо

AL

Ответ


Замечательно и полезно (как всегда)…
Вот как вычислить начало квартала с помощью Excel 2007, который предположительно не поддерживает вашу формулу, используя вместо этого ВЫБОР:
= ДАТА (ГОД (A1), ВЫБРАТЬ (МЕСЯЦ (A1), 1,1,1,4,4,4,7,7,7,7,10,10,10), 1)

Ответ


Формула в Метод 1 был следующим: —

IF (AND (MONTH (A1) = 1), ” Q1 ″, ЕСЛИ (И (МЕСЯЦ (A1)> = 4, МЕСЯЦ (A1) = 7, МЕСЯЦ (A1) Ответ


Привет, Пунит,
Спасибо за всю хорошую работу, которую вы проделали.

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

1) Использование месяца во вложенных Если: —

ЕСЛИ (И (МЕСЯЦ (A1) = 1), ”Q1 ″, ЕСЛИ (И (МЕСЯЦ (A1)> = 4, МЕСЯЦ (A1) = 7, МЕСЯЦ (A1) )

2) Использование Vlookup (но для этого нам нужна указанная ниже таблица. Я сделал эту таблицу с H5 по I8)
1 q1
4 q2
7 q3
10 q4

ВПР (МЕСЯЦ (A1), H5: I8,2, 1)
.

Ответ



Формат даты Excel — квартал и год

Можно ли изменить формат ячейки для отображения квартала и года q гггг вместо дд-мм-гггг ?


Нет — четверти не являются настраиваемым форматом

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

  1. вместо текущего значения
  2. который тогда больше не будет датой

= INDEX ({"март", "июнь", "сен", "декабрь" }, ROUNDUP (MONTH (A1)/3,0)) & TEXT (A1, "yyyy")

вернет квартал (на основе календарного года) и год дата в формате A1, т.е. сегодняшняя дата: декабрь 2011 г.


4
  = ROUNDUP (MONTH (A1)/3,0) & TEXT (A1, "yyyy")  

Этого достаточно, если вам просто нужно количество квартал

Улучшите этот ответ
отредактировано 28 апр. ’17 в 8:48
ответил 05 декабря 2013 в 16:09
добавить комментарий |

  = ROUNDUP (МЕСЯЦ (A1)/3,0) & ТЕКСТ (A1, «гггг»)  

Этого достаточно, если вам просто нужен номер квартала


1

Использовать указанный формат невозможно.

Улучшите этот ответ
ответил 31 октября 2011 в 10:45
добавить комментарий |

Использовать указанный формат невозможно.


1

путь>

  = IF ([@ Date] = "", "N/A", TEXT ([  @Date], "YYYY-Q") & ROUNDUP (MONTH ([@ Date])/3,0)) = IF (A1 = "", "N/A", TEXT (A1, "YYYY-Q") & ROUNDUP  (МЕСЯЦ (A1)/3,0)  

например. , 15.06.2015 станет 2015-Q3

На всякий случай, если поле даты пустое, эта формула заполнится с N/A вместо получения 1900-Q1

Улучшите этот ответ
отредактировано 22 апреля 2015 г. в 12:34
Machavity ♦
28,1k1717 золотых знаков7373 серебряных знака8989 бронзовых значки
ответ дан 22 апр 2015 в 12:25
добавить комментарий |

  = IF ([@ Date] = "", "N/A", TEXT ([@ Date]  , «ГГГГ-Q») & ЗА НЕДЕЛЮ ВВЕРХ (МЕСЯЦ ([@ Date])/3,0)) = ЕСЛИ (A1 = «», «Н/Д», ТЕКСТ (A1, «ГГГГ-Q») & ОКРУГЛ ВВОД (МЕСЯЦ (  A1)/3,0)  

например, 15.06.2015 станет 2015-Q3

На случай, если поле даты пустое, эта формула заполнится N/A вместо получения 1900-Q1


1

Попробуйте эту формулу:

="Q"&INT((MONTH(AZ2)+2)/3)&"-"&YEAR(AZ2) 

Это будет возьмите 31.12.2016 в ячейку AZ2 и измените его на Q4-2016

Улучшите этот ответ
отредактировал 24 мая ’16 в 19: 42
Noam Hacker
3,68577 золотых знаков2929 серебряных знаков4949 бронзовых знаков
ответил 24 мая ’16 в 18:42
добавить комментарий |

Попробуйте эту формулу:

  = "Q" & INT ((MONTH (AZ2) +2) /3) & "-" & ГОД (AZ2)  

Это займет 31.12.2016 в ячейке AZ2 и измените его на Q4-2016


0

На 07.01.2020 в ячейке A1

  = TEXT (A1, "yyyy") & "Q" & ROUNDUP  (МЕСЯЦ (A2)/3,0)  

Производит: «2020Q1»

Улучшите этот ответ
ответил 08 окт.2020, 18:50
добавить комментарий |

На 07.01.2020 в ячейке A1

  = TEXT (A1  , «гггг») & «Q» & ROUNDUP (МЕСЯЦ (A2)/3,0)  

Производит: «2020Q1»

Оцените статью
logicle.ru
Добавить комментарий