Определить неделю в финансовом году в Excel

Question

Я пытаюсь получить 1-53-ю неделю финансового года для набора данных в диапазоне 2/1/14-1/31/15. Я ставлю первый день финансового года как отдельную неделю.

Функции =WEEKNUM() дают неделю года для календарного года, но создают наложения для финансового года. Например, в обычную календарную неделю пять будут помечены в дни 2/1/2014 и 31.01.15.

Пример набора данных:

Transaction_Date
2/1/14
2/2/14
3/5/14
10/2/14
1/1/15

Желаемый результат:

Transaction_Date    Week_Of_Fiscal_Year
2/1/14               1
2/2/14               2
3/5/14               6
10/2/14             36
1/1/15              49
1/31/15             53

Answer 1

Мой подход заключается в том, чтобы "перенести" финансовый год на начало календарного года, поэтому weeknum рассчитывает правильный результат:
=WEEKNUM(A2-31,10+WEEKDAY(DATE(YEAR(A2-31),1,2),2)) , где:

• A2-31 - это "сдвинутая" дата: перенесите все на 31 день раньше, поэтому 1 февраля будет 1 января
• WEEKDAY(DATE(YEAR(A2-31),1,2),..) чтобы получить первый день недели в текущем финансовом году (1 февраля - первая неделя, а вторая неделя начинается со второго февраля). поэтому первый день недели также является днем после сдвинутой даты (2 января)
  • 10+WEEKDAY(...,2))
  • Вторым аргументом WEEKNUM является первый день недели: 11 в понедельник, 12 во вторник ...
  • вторым аргументом для WEEKDAY является начальный день недели, нужно использовать 2 для понедельника
  • 10+ конвертировать из 1-7 в 11-17

Эта формула работает для каждого года, начало финансового года 1 февраля и начало второй недели 2 "жестко закодированы" в формуле.

Answer 2

Я начал работать с переводами WEEKNUM, переходом на календарный год и т.д., А затем понял, что есть еще один простой и понятный подход:

Колонка А это ваши даты. Колонка B - ваша неделя финансового года. Первый день финансового года (1 февраля) вводится в A2 в качестве контрольной даты, и он всегда будет на неделе 1, поэтому его можно жестко запрограммировать в B2.

Формула для последующих дат может быть скопирована из B3, которая будет:

=CEILING((WEEKDAY(A$2)+A3-A$2)/7,1)

объяснение

Это зависит только от разницы между датой транзакции и днем 1 финансового года и тем фактом, что недели содержат 7 дней. Функция WEEKDAY определяет, в какой день недели начинается финансовый год, а разность дат и функция CEILING обрабатывают дробные недельные разницы в зависимости от дня недели, на которую приходится транзакция.

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

______

Решение без справочной даты

Если вы не хотите вводить первый день финансового года, вы можете получить его из даты транзакции. Например, в B3 вместо ссылки на ячейку A $ 2 вы можете использовать:

DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1)

Это определяет финансовый год с даты транзакции и преобразует ее в 1 февраля того же года. Простая формула в исходном решении станет:

=CEILING((WEEKDAY(DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1))+A3-DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1))/7,1)

Answer 3

Я нашел гораздо более простое решение для расчета номера недели на основе графика 4 4 5 финансового года.

= ISOWEEKNUM(СЕГОДНЯ ()- 301)

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

Очевидно, что если вы хотите вычислить значение даты в ячейке, просто измените TODAY() на ссылку на ячейку. = ISOWEEKNUM(A1-301)