Финансовые кварталы с даты

Я искал другие решения этой проблемы, используя код =ROUNDUP(MONTH(A1)/3,0).

Однако я не использую стандартные даты для кварталов

Q1 - 02 февраля / 03 мая Q2 - 04 мая / 02 августа Q3 - 03 февраля / 01-ноябрь Q4 - 02 ноября / 31 января

Я знаю, что могу сместить Квартал от значения =ROUNDUP(MONTH(A1)/3,0), однако это будет 1/2/3-е число месяца в неправильном квартале.

Любая идея, как я могу решить это?

Источник

1 ответ

Решение

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

Поэтому для подсчета количества дней в предыдущем месяце мы используем: =DAY(DATE(YEAR(A1);MONTH(A1);1)-1), Это занимает первое число этого месяца и вычитает один день, а затем позволяет Excel вычислить дневную часть этой даты, которая является числом дней в этом месяце.

Далее, чтобы сдвинуть количество дней назад до воскресенья, мы вычтем неделю и добавим номер дня этой недели, начиная с 1 в понедельник, используя: =WEEKDAY(DATE(YEAR(A1);MONTH(A1);1);2)

Наконец, после смещения дней мы можем вычислить квартал так, как вы предложили: =ROUNDUP(MONTH(A1)/3,0),

Теперь, если у вас есть дата в A1, вы можете вычислить четверть "Филиппа Кеннеди" с помощью (и я сделаю отступ для ясности):

=ROUNDUP(                                           // Regular "roundup for quarter
    MONTH(
        A1-                                         // Take the date, but subtract...
        DAY(DATE(YEAR(A1);MONTH(A1);1)-1)-          // ...the days of last month...
        7 + WEEKDAY(DATE(YEAR(A1);MONTH(A1);1);2)   // ...and the weekdays before sunday
    ) 
/ 3;0 )`.

Или в одну строку: =ROUNDUP( MONTH(A1-DAY(DATE(YEAR(A1);MONTH(A1);1)-1)-7+WEEKDAY(DATE(YEAR(A1);MONTH(A1);1);2))/3,0)

Источник
Другие вопросы по тегам