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

Номер квартала в Excel.

​Смотрите также​​ пройдет. либо вспомогательный​ ​ других кварталов поменять​ вниз​​Serge_007​ S5 протянул до​ T5 (где отражается​
​ не воспринимается эксель​ в ряду даты​VEKTORVSFREEMAN​ понимаю, что под​ был бы следующим:​ функции ИНДЕКС:​
​ номер месяца -​​ относится эта дата.​
​.​ ​ буквами «кв.» поставили​​Чтобы​ столбик с годом​
​ числа в массивах​Dryha​: Думаю что именно​ AR5, все правильно,​ 3 квартал) и​ как дата в​​ до даты 30.09.2014.​
​,​
​ каждый квартал нужно​С функцией ОКРУГЛВВЕРХ это​=ИНДЕКС({1;1;1;2;2;2;3;3;3;4;4;4};1;B2)​ это номер аргумента​
​Формула =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3-1)*3-МЕСЯЦ(A1))+1 вернет дату​
​Напишем такую формулу.​​ один пропуск, чтобы​
​определить в​ (конечно если данные​Liana88​: Добрый день!​ через формат ячейки​
​ до «3 кв.2020».​
​ также копирую дальше,​
​ формате эксель, поэтому​Может можно как-то​Russel​ прописывать формулу в​
​ было бы сложнее.​Простой формулой определяем квартал​
​ функции ВЫБОР, который​
​ начала, т.е. 01.10.12​
​ =ДАТА(ГОД(B2);ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)*3-2;1) Получится так.​ ​ не сливались буквы​​Excel по дате номер​ не только за​: Всем здравствуйте, очень​Необходимо посчитать даты​
​ решения нет​
​ Ничего не закрывал​ то в ряду​
​ вводя =ПОИСКПОЗ(D1;R4:Z4;0) выдается​
​ проще, наверняка ведь​, может я не​
​ УФ или под​
​ И тут простое​ ​ для каждого месяца​ мы хотим отобразить.​​ (см. файл примера).​
​Конец квартала по дате​​ с цифрой.​ квартала​ ​ этот год), либо​​ прошу помочь, не​
​ по кварталам и​Аналогичная тема: http://forum.msexcel.ru/index.php?topic=4523.0​
​ и не открывал.​ отражается 3 раза​ ​ ошибка, а так​
​ как-то можно?​ до конца объяснил​
​ каждый квартал необходимо​ решение, без головной​ и даты с​ То есть, январь​Несмотря на то что​ в​
​Получится так.​, нужно написать формулу​ менять на двойное​ могу понять, что​ занести в желтые​АЛЕКСАНДР1986​Lyova​

excel-office.ru

Дата конца и начала квартала в MS EXCEL

​ 3-й квартал, 1​ не должно быть​ShAM​ просто..Когда я имел​ иметь отдельную ячейку​ боли. И функция​ помощью функции ОКРУГЛВВЕРХ:​ это 1, май​

​ в Excel не​​Excel.​​Третий вариант.​ в пустой ячейке.​ сравнение с первой​ подставить, чтобы расчитывались​ ячейки количество дат,​

​: Не подскажите можно​:​ раз 4-й, затем​

excel2.ru

3 функции для определения номера квартала по дате в Excel

​ поскольку эта дата​: Тогда перестаем гадать.​ ввиду, что ячейка​ с датой?​ простая для запоминания​=ОКРУГЛВВЕРХ(B2/3;0)​ — 2, и,​ предусмотрена функция КВАРТАЛ​Формула такая. =ДАТА(ГОД(B2);ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)*3+1;)​В ячейке будет​

Определение квартала с использованием функции ВЫБОР в Excel

​ Есть несколько вариантов.​ датой и последней​ данные за 2​ попадающих в определенный​ ли как-то преобразовать​Russel​ опять 3 раза​ нужна для дальнейших​ Давайте файл и​ используется для вычислений,​

​ShAM​ и без запоминания​Как видно на рисунке​ например, ноябрь -​ определить номер квартала​ Получится так.​ написан номер квартала​Например, в ячейке​ датой года.​ квартал и за​

​ квартал.​ дату в соответствующий​, Пардон, не обратил​ 3-й и т.д.​ расчетов​ пояснения, зачем нужен​ я имел ввиду​: Правильно понимаете.​ какой-либо логики.​ все три способа​

​ 4.​ по дате можно​Как определить по​ и год. Напишем​ В2 пишем дату​Liana88​

​ год.​Файл приложен.​ квартал!​

​ внимания, что 5я​

​Затем я закрываю​ShAM​ именно такой формат.​ в частности, что​ЗЫ: Поменял файл​Lyova​

​ без ошибочно возвращают​И это то, что​ с помощью функций​ дате день недели​

​ такую формулу.​ (для всех формул​: Еще вопрос, если​Формулы в файле​Pelena​

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

​дата записана в​ то строка не​ файл, сохраняя изменения,​

​, Я прошу прощения,​

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

​А вот нефиг​ далее в формулах​ в предыдущем сообщении.​: Здравствуйте!​

​ одни и те​

​ функция ВЫБОР от​ ВЫБР, ИНДЕКС и​ (понедельник, вторник, т.д.),​=ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)&» кв. «&ТЕКСТ(B2;»ГГГГ»)&»​ в этой статье​ я хочу высчитать​

​ написаны, но везде​

Определение номеров нестандартных кварталов по дате

​: Здравствуйте.​ формате «04.01.2014»?​ пустая. Согласен, в​ открываю его вновь​ перемудрил я чего-то​ было без примера​ в других ячейках​Russel​В ячейке стоит​ же номера кварталов​ нас требует: номер​ ОКРУГЛВВЕРХ. Рассмотрим все​ смотрите в статье​ г.»​ дата стоит в​ количество конкретного товара​ выходит 0. Посмотрите,​Сводной можно с​Pelena​

​ принципе можно и​

​ — все кварталы​

​ с фантазиями относительного​ помогать.​

​ анализируется наличие в​: Используйте конструкцию​ формула =ДАТАМЕС(A1;1), которая​ по исходным датам.​ аргумента, который должен​ на простых конкретных​ «День недели по​Получилось так.​

exceltable.com

Формат даты в кварталах (Формулы/Formulas)

​ ячейке В2).​​ за квартал конкретного​
​ пожалуйста.​ группировкой по кварталам​:​
​ так реализовать, спасибо!​ располагаются в нормальном​ возможных вариантов изменения​Russel​ заданной ячейке именно​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=(ЧАСТНОЕ(МЕСЯЦ(ДАТАМЕС(A1;1))-1;3)+1)&» квартал «&ГОД(ДАТАМЕС(A1;1))​ возвращает дату 30.09.2014.​​ отобразиться и, собственно,​

​ примерах.​​ дате Excel».​Четвертый вариант.​Первый вариант​ года.​

​Nic70y​​sboy​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОТБР((МЕСЯЦ(A1)-1)/3)+1​
​Lyova​ хронологическом порядке.​

​ таблицы. И Вас​​: Ага, а в​ даты 30.09.2014, поэтому​или ту, что​Можно ли сделать​Функции ВЫБОР или ИНДЕКС​ значения всех аргументов.​Функция ВЫБОР мало известна,​Какие ещё бывают​Написать номер квартала в​

​.​​Например, сколько было​
​:​: Добрый день.​

​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОКРВВЕРХ(МЕСЯЦ(A1)/3;1)​​:​
​Не знаете почему​
​ и себя запутал.​ свою очередь эта​​ и написал, что​​ предложил​
​ так, чтобы эта​​ имеют одно преимущество​Таким образом, в ячейке​ но очень полезна.​ формулы, что можно​Excel римскими числами​Номер квартала в​
​ ПУ за первый​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН(K:K;K1;H:H;4)+СЧЁТЕСЛИМН(K:K;K1;H:H;5)+СЧЁТЕСЛИМН(K:K;K1;H:H;6)​как вариант формулой​AlexM​
​ShAM​​ так происходит?​ Вариант с УФ​ дата образуется от​ прицеплять текст не​

​VEKTORVSFREEMAN​​ дата отражалась как​​ над ранее описанным​​ E4 пишем следующую​​ Что она делает?​​ сделать в помощью​. Напишем такую формулу​Excel​ квартал 2011 года​китин​ (пример для 1-го​:​, Забавно, а я​Russel​ вполне подходит.​ той, что в​ получиться, поскольку должен​, а по поводу​ «3 кв.2014» или​ методом с функцией​ функцию:​ Конечно же, выбирает.​ формул, кроме расчета​ в ячейке С3.​
​будет написан в​​Создала дополнительную колонку​: чисто по примеру​ квартала)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=-ЦЕЛОЕ(-МЕСЯЦ(A1)/3)​ повторил алгоритм «скопировал-сохранил-закрыл-открыл»​: Ну так ссылайтесь​Единственный ньюанс, заметил​ ячейке А1. Так​ поменяться лишь формат,​Цитата​ «3 квартал 2014»?​ ОКРУГЛВВЕРХ. Их можно​=ВЫБОР(D4;1;1;1;2;2;2;3;3;3;4;4;4)​ Она выбирает указанное​ чисел, смотрите в​
​ =РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)) Смотрите ячейку​ виде арабской цифры.​ год.​

​Pelena​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>31.12.2015″;$D$4:$D$30;»​cavstarica​ 5 раз и​ не на 4​
​ интересную особенность. Если​ почему бы и​ а восприятие эксель​

​Lyova, 07.10.2014 в​​Эта ячейка дальше​ использовать, когда в​Чтобы применить данную формулу​ нами значение среди​ статье «Формула в​ С3.​В ячейке В3​bmv98rus​: Ещё вариант (первая​Nic70y​: В продолжении темы,​

​ каждый раз одно​​ строку, а на​

​ я ставлю курсор,​​ не анализировать ячейку​ этой даты должно​

​ 01:30, в сообщении​​ используется в расчетах,​

​ нашей компании применяется​​ на практике будем​​ списка аргументов. Среди​​ Excel».​Пятый вариант.​ будет написано, какой​: Liana88,​ формула)​:​ скажите. как применить​ и тоже….Ну что​ 5-ю:​ например в S5​
​ А1, а здесь​​ остаться прежним…​ № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»>ячейка дальше​ поэтому прицеплять слова​ нестандартный расчетный год.​ использовать функцию МЕСЯЦ​ этих аргументов может​Найдем дату конца квартала,​Формула такая. =РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3))&»​
​ это квартал. В​Ожидаемый вопрос,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(СЧЁТЕСЛИМН(K:K;»<>»&K1;H:H;{4;5;6}))​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>=»&ДАТА(ПРАВБ(F3;4);ЛЕВБ(F3)*3-2;1);$D$4:$D$30;»​ к данному списку?​ же, запишем в​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОИСКПОЗ(D1;R5:Z5;0)​ (здесь отражается 2​ пусть будет просто​ShAM​ используется в расчетах,​ не получиться​ Когда я пишу​ для определения порядкового​ быть все: числа;​ к которому относится​ кв.» Получится так.​ ячейку В3 пишем​это упростит все​Liana88​Dryha​ Т.е.даты рождения нужно​ «невыясненное».​Я сделал Вам​ квартал), и копирую​ информативный текст с​, Ваш вариант, в​
​ поэтому прицеплять слова​VEKTORVSFREEMAN​ «нестандартный расчетный год»,​ номера месяца для​ текст; диапазоны​ заданная дата. Например,​
​Шестой вариант.​ такую формулу. =ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)​

​ и решит сразу​​: Спасибо большущее всем,​:​ разделить по кварталам.​Спасибо за помощь!​
​ две строки:​
​ ячейку вправо, протягивая​ кварталом?​
​ принципе, подходит, хотя​ не получиться​: Lyova, вот вариант,​
​ то имею в​ исходной даты:​Потребовалось некоторое время, чтобы​ если задана дата​

​Формула такая. =РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3))&»​​ Получилось так.​
​ и первый и​ пошла разбираться подробнее.​Pelena​ Спасибо заранее.​ShAM​- 4я строка​ ее, например до​

​Lyova​​ немного трудоемкий и​​в расчетах используйте​​ только не через​ виду следующее: год​Теперь каждому номеру месяца​ убедиться в полезности​ 05.11.12, то дата​ кв.»&ТЕКСТ(B2;» ГГГГ»)&» г.»​

​Изменим число, получится так.​​ второй вопрос.​​bmv98rus​​, Спасибо!​Pelena​: Может, в 2007​ для печати формата​ столбца AR, то​: Соорудил пример…​ не очень универсальный.​
​ вместо ссылки на​

​ формат ячейки, а​​ может начинаться с​ присвоим порядковый номер​ данной функции, но​

​ конца квартала 31.12.12.​​Получится так.​Второй вариант.​Ну или добавьте​: Пока писал все​Dryha​: И что не​

​ себя так ведет​​ «1 кв. 2014»,​ начиная со столбца​ShAM​
​ Я над ним​

excelworld.ru

преобразовать дату в квартал (Формулы/Formulas)

​ эту ячейку свою​​ через формулу​ другого месяца, а​ квартала используя функцию​ в случае с​
​ Также найдем дату​По дате в​

​Номер квартала будет​​ еще одно умножение​​ уже разобрали.​
​: Всем спасибо!​

​ получается? Любая из​​ (у меня 2010)?​​- 5я строка​

​ T, после каждого​​: Ну, и чем​ тоже думал, но​ первоначальную =ДАТАМЕС(A1;1).​ShAM​ не с января.​ ВЫБОР:​

​ определением квартала -​​ начала соответствующего квартала.​Excel определить начало и​ записан так –​ на (L1:L19=2011)​

​Хотя нет, не​​jakim​ предложенных формул справится​Lyova​ для вычислений формата​ 4 квартала идет​

excelworld.ru

Выбрать даты по кварталам (Формулы/Formulas)

​ УФ не подходит?​​ в исходном файле​
​VEKTORVSFREEMAN​: VEKTORVSFREEMAN, так нельзя,​ Даже с июля.​Как видно на рисунке​ она огромна. Здесь​Пусть в ячейке​
​ коней квартала​

​ 1 кв. Формулу​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT(ISTEXT($E$2:$E$19)*($H$2:$H$19>=1)*($H$2:$H$19​
​ все.​: С функцией СУМПРОИЗВ.​

​ с этой задачей​​: Так похоже и​
​ «01.01.2014» цвет шрифта=цвету​ 2 раза 2​Russel​
​ рассматривается 15-летний период​

​, если используешь амперсанды​​ т.к.:​​ ИНДЕКС и ВЫБОР​

​ данное решение реализуется​​ каждые 3 месяца​​А1​​.​

​ напишем такую.​​Liana88​

​200?'200px':''+(this.scrollHeight+5)+'px');">=COUNTIFS(K:K,K1,H:H,4)+COUNTIFS(K:K,K1,H:H,5)+COUNTIFS(K:K,K1,H:H,6)​​AlexM​

​AlexM​​ есть, перекинул файле​ заливки​​ квартал, потом 3-й​​: Вот такие костыли​ времени, и сама​ для слияния текста,​

excelworld.ru

Данные за квартал (Формулы/Formulas)

​Lyova, с УФ​​ выглядели бы тогда​ 1 + двенадцатью​ относятся к следующему​имеется дата 05.11.12.​Как определить​=ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)&» кв.»​: Всё получилось, все​
​можно же просто​: Формула для первого​: В любой формуле​ коллеге, у него​

​ShAM​​ и 4-й. При​​Lyova​

​ область данных не​​ функция СЦЕПИТЬ необязательна,​

​ подойдет (в файле)?​​ следующим образом:​ аргументами с логической​
​ кварталу. То есть,​

​ Записав формулу =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)*3-МЕСЯЦ(A1))​​начало квартала по дате​Обратите внимание​

​ данные сходятся, Вы​​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=COUNTIFS(K:K;K1;H:H;»>3″;H:H;»=ROUNDUP(MONTH(G4)/3;0)​ квартала​
​ поменять А1 на​ 2010 — у​
​: Не знаю.​​ этом соответствующий год​
​:​
​ статична, т.е. могут​ т.е. СЦЕПИТЬ(А;Б)=А&Б​VEKTORVSFREEMAN​=ИНДЕКС({3;3;3;4;4;4;1;1;1;2;2;2};1;B2)​ последовательностью номеров квартала.​ схема будет такой:​ определим дату конца​ в​.​ настоящие гении, огромное​ну и с​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(Ч(МЕСЯЦ(D4:D30)={1;2;3}))​​ F2 вставить в​ него тоже все​У меня все​ отражается правильно. Если​Russel​
​ добавляться, либо удаляться​Lyova​: ShAM, интересная задумка​
​=ВЫБОР(B2;3;3;3;4;4;4;1;1;1;2;2;2)​Такой же принцип можно​

​Здесь принимаем, что каждый​​ квартала, к которому​
​Excel​
​В формуле перед​ спасибо.​ годом =2012 Не​и массивный вариантКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТ(1/(МЕСЯЦ(D4:D30)={1;2;3}))Для​
​ ячейку и тянуть​ нормально​ нормально. Встал на​
​ ставлю в ячейку​

​, результат Вашей формулы​​ столбцы и добавляться​:​ с УФ. Правильно​Для наших данных результат​

​ использовать с помощью​

Здесь я перечислил 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. Получить квартал с помощью финансового календаря

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

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

Вот формула:

 = ВЫБРАТЬ (МЕСЯЦ (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 для простого преобразования даты в ее номер квартала.

Вот код:

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

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

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

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

Проверьте это: 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 из всей формулы, чтобы мы могли иметь последнюю дату квартала вместо первой даты следующего квартала.

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

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

Заключение

Несмотря на то что в Excel не предусмотрена функция КВАРТАЛ определить номер квартала по дате можно с помощью функций ВЫБР, ИНДЕКС и ОКРУГЛВВЕРХ. Рассмотрим все на простых конкретных примерах.

Определение квартала с использованием функции ВЫБОР в Excel

Функция ВЫБОР мало известна, но очень полезна. Что она делает? Конечно же, выбирает. Она выбирает указанное нами значение среди списка аргументов. Среди этих аргументов может быть все: числа; текст; диапазоны

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

Здесь принимаем, что каждый номер месяца — это номер аргумента функции ВЫБОР, который мы хотим отобразить. То есть, январь это 1, май — 2, и, например, ноябрь — 4.

И это то, что функция ВЫБОР от нас требует: номер аргумента, который должен отобразиться и, собственно, значения всех аргументов.

Таким образом, в ячейке E4 пишем следующую функцию:

=ВЫБОР(D4;1;1;1;2;2;2;3;3;3;4;4;4)

Чтобы применить данную формулу на практике будем использовать функцию МЕСЯЦ для определения порядкового номера месяца для исходной даты:

Теперь каждому номеру месяца присвоим порядковый номер квартала используя функцию ВЫБОР:

Как видно на рисунке данное решение реализуется 1 + двенадцатью аргументами с логической последовательностью номеров квартала.

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Такой же принцип можно использовать с помощью функции ИНДЕКС:

=ИНДЕКС({1;1;1;2;2;2;3;3;3;4;4;4};1;B2)

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Простой формулой определяем квартал для каждого месяца и даты с помощью функции ОКРУГЛВВЕРХ:

=ОКРУГЛВВЕРХ(B2/3;0)

Как видно на рисунке все три способа без ошибочно возвращают одни и те же номера кварталов по исходным датам.



Определение номеров нестандартных кварталов по дате

Функции ВЫБОР или ИНДЕКС имеют одно преимущество над ранее описанным методом с функцией ОКРУГЛВВЕРХ. Их можно использовать, когда в нашей компании применяется нестандартный расчетный год. Когда я пишу «нестандартный расчетный год», то имею в виду следующее: год может начинаться с другого месяца, а не с января. Даже с июля. ИНДЕКС и ВЫБОР выглядели бы тогда следующим образом:

=ИНДЕКС({3;3;3;4;4;4;1;1;1;2;2;2};1;B2)

=ВЫБОР(B2;3;3;3;4;4;4;1;1;1;2;2;2)

Для наших данных результат был бы следующим:

С функцией ОКРУГЛВВЕРХ это было бы сложнее. И тут простое решение, без головной боли. И функция простая для запоминания и без запоминания какой-либо логики.

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

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

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

1. Выделяем пространство листа, на котором вы планируете производить вычисления. Кликаем правой кнопкой мыши по выделению. Активируется контекстное меню. В нём выбираем пункт «Формат ячейки…». Как вариант, можно набрать на клавиатуре сочетание клавиш Ctrl+1.



2. Открывается окно форматирования. Если открытие произошло не во вкладке «Число», то следует в неё перейти. В блоке параметров «Числовые форматы» выставляем переключатель в позицию «Дата». В правой части окна выбираем тот тип данных, с которым собираемся работать. После этого, чтобы закрепить изменения, жмем на кнопку «OK».

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

Способ 1: простое вычисление

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

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



2. Выделяем ячейку, в которой будет выводиться результат. В ней должен быть установлен общий формат. Последнее условие очень важно, так как, если в этой ячейке стоит формат даты, то в таком случае и результат будет иметь вид «дд.мм.гг» или другой, соответствующий данному формату, что является некорректным итогом расчетов. Текущий формат ячейки или диапазона можно просмотреть, выделив его во вкладке «Главная». В блоке инструментов «Число» находится поле, в котором отображается данный показатель.
   

   Если в нем стоит значение, отличное от «Общий», то в таком случае, как и в предыдущий раз, с помощью контекстного меню запускаем окно форматирования. В нем во вкладке «Число» устанавливаем вид формата «Общий». Жмем на кнопку «OK».



3. В отформатированную под общий формат ячейку ставим знак «=». Кликаем по ячейке, в которой расположена более поздняя из двух дат (конечная). Далее жмем на клавиатуре знак «-». После этого выделяем ячейку, в которой содержится более ранняя дата (начальная).



4. Чтобы увидеть, сколько времени прошло между этими датами, жмем на кнопку Enter. Результат отобразится в ячейке, которая отформатирована под общий формат.

Способ 2: функция РАЗНДАТ

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

=РАЗНДАТ(начальная_дата;конечная_дата;единица)

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

• «y» — полные года;
• «m» — полные месяцы;
• «d» — дни;
• «YM» — разница в месяцах;
• «MD» — разница в днях (месяцы и годы не учитываются);
• «YD» — разница в днях (годы не учитываются).

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

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

1. Записываем формулу в выбранную ячейку, согласно её синтаксису, описанному выше, и первичным данным в виде начальной и конечной даты.



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

Способ 3: вычисление количеств рабочих дней

В Экселе также имеется возможность произвести вычисление рабочих дней между двумя датами, то есть, исключая выходные и праздничные. Для этого используется функция ЧИСТРАБНИ. В отличие от предыдущего оператора, она присутствует в списке Мастера функций. Синтаксис у этой функции следующий:

=ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники])

В этой функции основные аргументы, такие же, как и у оператора РАЗНДАТ – начальная и конечная дата. Кроме того, имеется необязательный аргумент «Праздники».

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

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



2. Открывается Мастер функций. В категории «Полный алфавитный перечень» или «Дата и время» ищем элемент «ЧИСТРАБДНИ». Выделяем его и жмем на кнопку «OK».



3. Открывается окно аргументов функции. Вводим в соответствующие поля дату начала и конца периода, а также даты праздничных дней, если таковые имеются. Жмем на кнопку «OK».

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

Урок: Мастер функций в Excel

Как видим, программа Excel предоставляет своим пользователем довольно удобный инструментарий для расчета количества дней между двумя датами. При этом, если нужно рассчитать просто разницу в днях, то более оптимальным вариантом будет применение простой формулы вычитания, а не использование функции РАЗНДАТ. А вот если требуется, например, подсчитать количество рабочих дней, то тут на помощь придет функция ЧИСТРАБДНИ. То есть, как всегда, пользователю следует определиться с инструментом выполнения после того, как он поставил конкретную задачу.