Excel номер недели по дате

Номер недели по дате функцией НОМНЕДЕЛИ

Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.

Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.

Во-вторых, вопрос — какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.

Способ 1. Стандарт ГОСТ ИСО 8601-2001 и функция НОМНЕДЕЛИ.ISO

Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52 неделей предыдущего года.

Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу:

В Excel 2013 функцию для расчета номера недели по ISO добавили в стандартный набор – она называется НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)


Способ 2. Неделя с 1-м января и функция НОМНЕДЕЛИ (WEEKNUM)

В этом случае первой неделей года считается та, на которую попадает 1 января. Здесь также возникает сложность в том, что в году может оказаться 52 или 53 недели и, плюс ко всему, 1 января может выпасть на воскресенье, т.е. шесть последних дней года могут оказаться уже в неделе с номером 1, что затрудняет отчетность. Тем не менее, если такой способ нумерации вам нужен, то его можно реализовать функцией НОМНЕДЕЛИ (WEEKNUM) , появившейся в Excel начиная с 2007 года. Синтаксис этой функции таков:

=НОМНЕДЕЛИ( Дата ; Тип_отсчета )

  • Дата — ячейка с датой, для которой нужно определить номер недели
  • Тип_отсчета — число (1, 2 или 3), обозначающее тип нумерации дней в неделе. Вариант 1 — американский (неделя начинается с воскресенья), вариант 2 — наш.

Номер недели по дате в Excel

Рассмотрим различные варианты получения номера недели по дате в Excel в зависимости от способа определения первого дня и способа нумерации.

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

Особенности расчета: нумерация и определение первого дня

Во-первых, в зависимости от страны может отличаться способ определения первой недели года.
В России (согласно ГОСТ ИСО 8601-2001, действует с 2002 г.) первой неделей считается неделя, которая содержит первый четверг года, т.е. неделя содержащая 4 января (другими словами, 1 января это любой день от понедельника до четверга). В США используют другой вариант нумерации, к примеру, у них 1 января начинается новая календарная неделя, независимо от дня недели.

Во-вторых, первый день недели в разных странах также может отличаться.
Для жителей России и стран Европы начало недели начинается с понедельника, но, например, в странах Северной и Южной Америки неделя начинается с воскресенья, а в некоторых странах Ближнего Востока с субботы.

Читать еще:  Перевод в часы в excel

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

Способ 1. Неделя содержит первый четверг года

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

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


Алгоритм расчета:

  • К вычисляемой дате (напомню, что дата в Excel хранится в числовом виде) прибавляем 3 дня и вычитаем номер дня недели (1 для понедельника, 2 для вторника и т.д.); Этим шагом мы добьемся, что для понедельника, вторника и среды вычисляемая дата увеличится, а для остальных дней (четверга, пятницы, субботы и воскресенья) уменьшится, что и позволит добиться включения первого четверга года в первую неделю.
  • Далее возьмем остаток от деления на 365,25 (средняя длительность года с учетом високосного года); Этой операцией мы получим количество дней в текущем году для вычисляемой даты.
  • Делим полученный результат делим на 7; Переводим дни в недели.
  • Прибавляем 1 и берем целую часть числа.

Начиная с версии Excel 2013 добавлена стандартная функция НОМНЕДЕЛИ.ISO (по стандарту ИСО), которую можно применить вместо выше приведенной формулы.
Синтаксис и описание функции НОМНЕДЕЛИ.ISO (в английской версии WEEKNUM.ISO):

НОМНЕДЕЛИ.ISO(дата)
Возвращает номер недели в году по стандарту ISO для указанной даты.

  • Дата(обязательный аргумент) — код даты-времени.

Проверим работу функции на тех же датах, что и в предыдущем примере:


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

Способ 2. Первая неделя начинается 1 января

Для такого способа нумерации недель тоже есть вариант стандартной функции, причем он доступен и в более ранних версиях Excel.
Синтаксис и описание функции НОМНЕДЕЛИ (в английской версии WEEKNUM):

НОМНЕДЕЛИ.ISO(дата; [тип])
Возвращает номер недели года.

  • Дата(обязательный аргумент) — код даты-времени;
  • Тип(необязательный аргумент) — тип возвращаемого значения, 1 (по умолчанию; американский вариант, неделя начинается в воскресенье) и 2 (российский вариант, неделя начинается в понедельник).

В качестве примера рассмотрим 2017 г. где 1 января приходится на воскресенье, а 2 января на понедельник.
Соответственно, получается, что в американском варианте расчета недели меняются между 7 и 8 января, а в российском между 8 и 9 января:


Также возможные и другие различные значения аргумента Тип:


Первая цифра типа означает способ определения первой недели года (1 — неделя начинается 1 января, 2 — содержит первый четверг года), а вторая цифра определяет с какого дня начинается неделя (1 — понедельник, 2 — вторник, 3 — среда и т.д.)

Спасибо за внимание!
Если у вас остались вопросы по теме статьи — пишите в комментариях.

Номер недели в году в MS EXCEL

Существует 2 способа определения номера недели: в соответствии со стандартом ИСО и с помощью функции НОМНЕДЕЛИ().

Рассмотрим 2 способа определения номера недели:

Используя функцию НОМНЕДЕЛИ()

Функция НОМНЕДЕЛИ() считает, что первая неделя года начинается с 1-го января. В ней может оказаться от 1 до 7 дней (1 января соответственно попадает на воскресенье или понедельник) и, как следствие, первая неделя может начинаться не с понедельника. У этой функции есть второй аргумент тип, который может принимать значение 1 (первым днем недели считается воскресенье) или 2 (первым днем недели считается понедельник).

Предполагается, что в ячейке А3 находится дата. Формула определяет номер недели, которой принадлежит эта дата.

Альтернативой использованию функции НОМНЕДЕЛИ() с типом 2 (как принято в РФ) является формула (см. файл примера ):
=1+ЦЕЛОЕ((A3-(ДАТА(ГОД(A3);1;2)-ДЕНЬНЕД(ДАТА(ГОД(A3);1;0))))/7)

Альтернативой использованию функции НОМНЕДЕЛИ() с типом 1 является формула:
=1+ЦЕЛОЕ((A3-(ДАТА(ГОД(A3);1;2)-ДЕНЬНЕД(ДАТА(ГОД(A3);1;1))))/7)

По стандарту ISO8601:2000 (ГОСТ ИСО 8601-2001)

По стандарту ISO8601:2000 все недели начинаются с понедельника и длятся 7 дней. Первой неделей считается та, на которую попадает первый четверг нового года (т.е. первая неделя должна содержать не менее 4 дней из нового года).

Читать еще:  Динамический диапазон excel

Формула для расчета номера недели по стандарту ИСО:
=ЦЕЛОЕ((A3-ДАТА(ГОД(A3-ДЕНЬНЕД(A3-1)+4);1;3)+
ДЕНЬНЕД(ДАТА(ГОД(A3-ДЕНЬНЕД(A3-1)+4);1;3))+5)/7)

Следствием того, что все недели начинаются с понедельника, может получиться так, что в первую неделю попадут дни из прошлого года.

Решение обратной задачи – когда известен номер недели, а нужно найти, например, дату последнего дня этой недели – приведено в статье Последний день недели.

Как вывести номер недели по дате в Excel

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

Вы должны помнить, что существует как минимум несколько походов к нумерации номера недели.

  1. Первый подход используется России. Согласно стандарту ГОСТ ИСО 8601—2001первой рабочей неделей года считается та, на которую выпадает первый четверг года.
  2. Второй подход заключается в том, что неделя, на которую приходится 1 января и считается первой неделей года.

Еще раз напомним вам, что в России используется первый из описанных выше походов.

Если у вас Excel 2003, то мы рекомендуем вам воспользоваться готовой формулой, которая выглядит следующим образом:

=ЦЕЛОЕ(( A1 -ДАТА(ГОД( A1 -ДЕНЬНЕД( A1 -1)+4);1;3)+ДЕНЬНЕД(ДАТА(ГОД( A1 -ДЕНЬНЕД( A1 -1)+4);1;3))+5)/7)

При использовании английской версии Excel формула выглядит как:

=INT (( A1 -DATE (YEAR ( A1 -WEEKDAY ( A1 -1)+4),1,3)+WEEKDAY (DATE (YEAR ( A1 -WEEKDAY ( A1 -1)+4),1,3))+5)/7)

В случае если вам все таки необходим второй подход, то для определения номера недели используйте формулу:

=1+ЦЕЛОЕ(( A1 -ДАТА(ГОД( A1 +4-ДЕНЬНЕД( A1 +6));1;5)+ДЕНЬНЕД(ДАТА(ГОД( A1 +4-ДЕНЬНЕД( A1 +6));1;3)))/7)

формула для англоязычной версии:

=1+INT (( A1 -DATE (YEAR ( A1 +4-WEEKDAY ( A1 +6)),1,5)+WEEKDAY (DATE (YEAR ( A1 +4-WEEKDAY ( A1 +6)),1,3)))/7)

Во всех вышеуказанных формулах A1 меняем на дату или ссылку на дату.

Все намного проще, если вы используете Excel 2007 или 2010. Начиная с этих версий появилась специальная функция, позволяющия определять номер недели по дате. (примечание: эту функцию можно включить и в Excel 2003, для этого необходимо подключить надстройку Пакет анализа через меню Сервис-Надстройки, но в данном случае, если документ будет открывать человек у которого эта настройка не включена, то он вместо номера недели увидит ошибку #ЗНАЧ!, поэтому применять ее в Excel 2003 мы не будем)

Функция называется НОМНЕДЕЛИ (в английской версии WEEKNUM). Синтаксис очень простой.

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

Для России необходимо использовать формулу в следующем формате.

=НОМНЕДЕЛИ( А1 ; 21)

А1 – это дата или ссылка на дату.

В данном случае тип 21 означает, что номер первой недели будет считаться та, на которую приходится первый четверг года и первым днем недели является понедельник (примечание: во многих странах первым днем недели считается воскресенье)

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Сводные таблицы Excel

Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача на первый взгляд простая, но (как всегда) есть несколько весьма существенных нюансов.

Во-первых, в разных странах отсчет начала и конца самой недели – различается. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье. Во-вторых, вопрос – какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.

Читать еще:  Суммпроизв в excel

Способ 1. Стандарт ГОСТ ИСО 8601-2001

Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52-й неделей предыдущего года.

Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу (предполагается, что дата в ячейке А1):

Этот вариант официально принят в России

В англоязычной версии это будет =TRUNC(MOD(A1+3-WEEKDAY(A1;2);365,25)/7+1) . В Excel 2013 / 2016 функцию для расчета номера недели по ISO добавили в стандартный набор – она называется НОМНЕДЕЛИ.ISO (WEEKNUM.ISO).

Функцию для расчета номера недели по ISO добавили в стандартный набор

Способ 2. Неделя с 1-м января

В этом случае первой неделей года считается та, на которую попадает 1 января. Здесь также возникает сложность в том, что в году может оказаться 52 или 53 недели и, плюс ко всему, 1 января может выпасть на воскресенье, т.е. шесть последних дней года могут оказаться уже в неделе с номером 1, что затрудняет отчетность. Тем не менее, если такой способ нумерации вам нужен, то его можно реализовать функцией НОМНЕДЕЛИ (WEEKNUM).

Если у вас Excel не 2007 версии или новее, то сначала придется подключить надстройку Пакет анализа через меню Сервис → Надстройки (Tools → Add-Ins → Analysis ToolPak), чтобы к списку функций Excel в категории Дата и время добавилась функция НОМНЕДЕЛИ (WEEKNUM). В Excel 2007–2016 ничего специально подключать не надо – эта функция входит в стандартный набор.

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

В Excel 2007–2016 ничего специально подключать не надо

При всей внешней простоте этого решения тут есть две тонкости. Во-первых, если у того пользователя, который откроет ваш файл, не будет заранее подключена надстройка Пакет анализа или у него не Excel 2007–2016 – он увидит вместо номера недели ошибку #ЗНАЧ!. Excel сам не распознает эту функцию, нужно обязательно заранее подключить надстройку. Во-вторых, названия функций из надстроек Excel сам не переводит на другой язык при открытии книги в Excel с другой версией.

Т.е. если даже у пользователя и подключена надстройка, но версия Excel англоязычная, то он тоже увидит вместо номера недели ошибку #VALUE!, потому что нужно вручную исправить русское название функции НОМНЕДЕЛИ на английское WEEKNUM – только так она заработает. Поэтому использование этой функции из надстройки Пакет анализа хорошо только в тех случаях, когда предполагается, что никто, кроме вас, с файлом работать не будет.

А если будет? Тогда лучше пойти другим путем. Он более примитивен, зато надежен. Если предположить, что дата хранится в ячейке А1, то вот такая формула посчитает такой же номер недели, как и функция НОМНЕДЕЛИ: =1+ЦЕЛОЕ((A1-ДАТА(ГОД(A1+4-ДЕНЬНЕД(A1+6));1;5)+ДЕНЬНЕД(ДАТА(ГОД(A1+4-ДЕНЬНЕД(A1+6));1;3)))/7) или в англоязычной версии: =1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6));1;5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6));1;3)))/7) .

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector