Как получить четверть свидания?
В Excel нет прямой функции получения квартала по дате. Но вы можете создать для этого формулу.
Да, вы можете использовать формулу для расчета квартала от даты. Даже есть более одного метода, который вы можете использовать. Ладно, как вы знаете, всего в году четыре квартала, но дело в том, что получить квартал от даты можно разными способами.
Первый вариант предполагает год в обычном календаре или второй в налоговом календаре. И в сегодняшнем посте я покажу вам методы получения номера квартала, используя оба календаря, а также поделюсь с вами несколькими методами для этого.
Итак, начнем.
1. Получите квартал, используя функции ОКРУГЛВВЕРХ и МЕСЯЦ.
Использование комбинации ROUNDUP и MONTH — лучший способ найти четверть даты. Он возвращает четверть в виде числа (например, 1,2,3,4).
Вот формула.
=ROUNDUP(MONTH(A1)/3,0)
Здесь мы используем 26 мая 2018 года в качестве даты, и формула возвращает результат 2. Вы можете использовать формулу ниже, если хотите добавить Q перед номером квартала.
="Q-"&ROUNDUP(MONTH(A1)/3,0)
Как работает эта формула?
Эта формула работает в трех частях.
В первой части мы использовали функцию МЕСЯЦ и обращались к дате, которая возвращает номер месяца.
После этого во второй части мы поделили число этого месяца на 3, что дает нам число с десятичными знаками.
Наконец, в третьей части мы использовали функцию ОКРУГЛВВЕРХ для округления (это число до десятичных знаков), что дает нам фактическую четверть.
Вот реальная вещь: вы знаете, что есть 4 квартала и двенадцать месяцев. ВЕРНО? А если разделить все числа месяцев на три (да, только на 3), то получится что-то вроде ниже.
И когда вы их округляете, вы всегда получаете номер квартала за месяц.
2. Получите квартал с помощью финансового календаря
В приведенном выше примере мы использовали простую формулу для получения квартала от даты, но иногда вам нужно использовать финансовый календарь.
Что такое финансовый год?
Финансовый год (FY) — это 12-месячный период, который предприятие или правительство использует для целей бухгалтерского учета и подготовки финансовой отчетности. Финансовый год может не соответствовать календарному году…
А для расчета квартала финансового календаря нам нужно использовать другую формулу, и в этой формуле нам нужно объединить функции ВЫБОР и МЕСЯЦ.
Вот формула:
=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
(Здесь я использовал индийский финансовый год, который начинается 1 апреля и заканчивается 31 марта)
В результате возвращается 1, поскольку месяц — май, а наш год начинается с апреля, а май — второй месяц года, который попадает под первый квартал.
Как работает эта формула?
Здесь мы объединили две разные функции, и они работают в двух частях.
В первой части мы использовали функцию МЕСЯЦ, чтобы получить номер месяца по дате, и здесь она возвращает 5.
После этого во второй части мы использовали функцию для получения числа на основе номера месяца, возвращаемого функцией МЕСЯЦ.
Вы можете узнать больше о ВЫБОР здесь.
Если вы посмотрите, в функции ВЫБОР мы указали 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 If IsDate(Rng.Value) = True Then Rng.Value = DatePart("q", Rng.Value) Rng.NumberFormat = “General” End If Next Rng End SubЧтобы использовать этот код, просто добавьте его в свой редактор VB.
А затем просто выберите ячейки, в которых нужно преобразовать дату в число кварталов, и наконец запустите этот код.
Вот как работает этот код…
Когда вы запускаете этот код, он сначала проверяет, есть ли в ячейке дата или нет, и если это дата, он преобразует ее в номер квартала.
Здесь мы использовали две разные функции VBA:
- ISDATE: Чтобы проверить, есть ли в ячейке дата.
- DATEPART: преобразовать дату в квартал.
Проверьте это: 100 лучших полезных макросов для новичков в VBA | Что такое VBA в Excel
4. Получить дату начала квартала по дате
Давайте забудем номер квартала и воспользуемся датой начала квартала.
Это означает, что если у нас есть дата 26 мая 2018 года, вместо номера квартала 2 мы можем иметь 1 апреля 2018 года, что является датой начала первого месяца квартала. Вот формула, которую нам нужно использовать:
=DATE(YEAR(A1),FLOOR.MATH(MONTH (A1)-1,3)+1,1)
Когда вы вводите эту формулу, она просто возвращает дату начала квартала.
Как работает эта формула?
Эта формула состоит из четырех разных частей, которые определяют дату начала семестра.
В первой части мы использовали функцию МЕСЯЦ, которая возвращает номер месяца даты.
Итак, когда МЕСЯЦ возвращает 5 в качестве номера месяца (затем минус один), во второй части ФЛОР округляет его до 3, а затем добавляет единицу, чтобы мы могли получить 4, что является первым месяцем (апрелем) того же квартала?
После этого в третьей части мы использовали функцию ГОД, чтобы получить год из фактической даты.
А в четвертой части мы просто объединили функцию ДАТА, чтобы создать дату, объединяющую значения из первых двух частей.
5. Получить дату окончания квартала по дате
Итак, вам нужна дата окончания квартала вместо даты или числа начала, и для этого мы можем использовать следующую формулу:
=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1
Для 26 мая 18 года он возвращается 30 июня 18 года, что является последней датой квартала.
Как работает эта формула?
В этой формуле у нас есть четыре разные части.
В первой части мы объединили функции INT и MONTH. МЕСЯЦ возвращает номер месяца даты, затем мы разделили его на 3. После этого INT округляет его до ближайшего целого числа.
В конце мы умножаем его на 3, чтобы получить 6 (июнь), что является конечным месяцем квартала, а затем добавляем к нему 1.
Проще говоря, эта часть формулы просто преобразует месяц фактической даты в первый месяц следующего квартала.
Во второй части мы использовали функцию ГОД, чтобы получить год из фактической даты.
После этого в третьей части просто используйте 1, чтобы завершить дату с помощью функции ДАТА.
Извини! Еще кое-что.
В четвертом минус 1 всей формулы, чтобы мы могли иметь последнюю дату квартала вместо первой даты следующего квартала.
Получить файл Excel
Заключение
Просто комбинируя функции, вы можете создать простую формулу для расчета квартала от даты. В этой статье мы рассмотрели для этого четыре формулы + код VBA.
Честно говоря, № 1 — наиболее часто используемый и простой способ найти квартальную дату, но вы можете использовать и остальные, если хотите. Я надеюсь, что эта статья оказалась для вас полезной, но теперь скажите мне вот что.
Знаете ли вы другой метод расчета номера квартала по дате?
Поделитесь со мной в комментариях, мне будет интересно услышать ваше мнение, и не забудьте поделиться этим советом со своими друзьями. Я уверен, они это оценят.