В excel номер недели по дате

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

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

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

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

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

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

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

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

Способ 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 — среда и т.д.)

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

Источник: tutorexcel.ru

Как вывести номер недели по дате в 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). Синтаксис очень простой.

Читайте также:  Пароль на открытие файла excel снять

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

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

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

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

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

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

Источник: sirexcel.ru

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

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

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

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

Способ 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 – наш.

Источник: www.planetaexcel.ru

Сводные таблицы 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).

Читайте также:  Excel как разделить на два окна

Функцию для расчета номера недели по 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) .

Источник: pivot-table.ru

Определение дня недели по дате в Excel

Иногда перед пользователем во время работы в Эксель встаёт задача – вывести название дня недели, соответствующее указанной в определенной ячейке дате. Что делать в таком случае? Сверяться с календарем и прописывать день недели вручную? Конечно же, нет. Программа Excel обладает очень мощным функционалом, и вполне способна справиться с такой, казалось бы, непростой задачей. Более того, ее можно выполнить по-разному.

Итак, давайте разберем все возможные способы вывода дня недели по заданной дате в таблице Эксель.

1. Отображение дня недели через формат ячеек

Отличительная особенность данного метода в том, что в результате будет выведен только конечный результат с указанием дня недели. Сама же дата не сохранится, т.е. дата в ячейке преобразуется в соответствующий ей день недели. А саму дату можно будет увидеть, лишь выделив ячейку с днем недели (информация отобразится в строке для ввода формул).

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

  1. Например, у нас есть ячейка таблицы, в которой указана какая-то конкретная дата.
  2. Щелкаем по ячейке с датой правой кнопкой мыши. Затем в появившемся списке команд кликаем по строке “Формат ячеек…”.
  3. В открывшемся окне переходим во вкладку “Число” (если вдруг по какой-то причине мы сразу не оказались в ней). В перечне “Числовые форматы” кликаем по варианту “(все форматы)”. После этого обращаем внимание на поле “Тип”, кликаем по области ввода значений и пишем в ней “ДДДД” (без кавычек). После этого нажимаем OK, чтобы сохранить изменения и закрыть окно форматирования.
  4. В результате дата в ячейке таблицы будет преобразована в название дня недели согласно календарю. Для того, чтобы увидеть саму дату, отмечаем эту ячейку, и в поле для ввода формул будет отображена исходное значение в формате даты.

Примечание: Вместо “ДДДД” можно задать формат “ДДД” (т.е. 3 буквы, а не 4), и в этом случае день недели будет выведен в ячейку в сокращенном виде. Посмотреть, как это будет выглядеть в итоге можно сразу же в окне форматирования в поле “Образец”.

2. Определение дня недели с помощью оператора ТЕКСТ

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

Для того, что бы дата оставалась неизменно в своей начальной ячейке, а день недели выводился в другой, потребуется применить специальную функцию ТЕКСТ. Давайте разберем на практике, как применяется данный оператор и каковы его возможности в Эксель.

  1. Итак, у нас есть определенная дата в таблице. Выбираем другую ячейку, где планируется отобразить название дня недели и отмечаем ее мышью. Далее нажимаем кнопку “Вставить функцию” (слева от строки формул).
  2. В открывшемся окне щелкаем по текущей категории функций и в раскрывшемся перечне кликаем по строке “Текстовые”.
  3. Из предложенного списка функций нам нужен “ТЕКСТ”, щелкаем по нему и далее жмем кнопку OK.
  4. В результате перед нами откроется окно с настройками функции “ТЕКСТ”, где требуется заполнить значения его аргументов. Сама формула функции выглядит следующим образом: =ТЕКСТ(Значение;Формат вывода). Для заполнения предлагаются 2 аргумента:
    • “Значение” – это та самая дата, день недели которой требуется вывести. В этом поле можно как прописать дату вручную, так и указать координаты ячейки. Чтобы это сделать щелкаем по полю ввода значений, затем – по требуемой ячейке с датой.
    • В поле “Формат” пишем нужный нам вариант отображения дня недели: “дддд” – полное название, “ддд” – сокращенное. По завершении кликаем OK.
  5. В результате в ячейке с формулой будет выведен день недели, а дата останется в своей ячейке.
  6. Кроме того, если мы теперь изменим дату, то и день недели изменится автоматически, что действительно, очень удобно.
Читайте также:  Как в эксель сделать альбомный лист

3. Номер дня недели через оператор ДЕНЬНЕД

Есть еще одна функция для выполнения поставленной задачи – это оператор ДЕНЬНЕД, однако, при его использовании будет выводиться не название дня недели, а его порядковый номер. Более того, вовсе не обязательно, чтобы понедельник был под номером 1, так как порядок нумерации дней недели устанавливается пользователем.

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

  1. Как всегда, для начала нам нужна ячейка с датой. Кликаем по другой ячейке, куда будет выводиться результат. После этого жмем кнопку “Вставить функцию”.
  2. В открывшемся окне вставки функций выбираем категорию “Дата и время” и из перечня операторов – “ДЕНЬНЕД”. Подтверждаем действие нажатием OK.
  3. Откроется окно настройки функции, формула которой имеет следующий вид: =ДЕНЬНЕД(дата;[тип]). Как мы видим, у нее тоже 2 аргумента, значения которых требуется указать.
    • В поле “Дата” печатаем нужную дату или указываем координаты ячейки, содержащей дату.
    • В поле “Тип” указываем день недели, с которого будет производиться отсчет. Значениями этого аргумента могут быть только цифры 1, 2 или 3:
      • 1 – первым днем недели будет воскресенье
      • 2 — отсчет начнется с понедельника
      • 3 — как и в случае с цифрой “2”, отсчет начнется с понедельника, но ему будет присвоена цифра “0”.
      • если аргумент “Тип” не будет указан, по умолчанию ему будет присвоено значение “1”, т.е. первым днем недели будет считаться воскресенье. Нам же привычнее вести отсчет с понедельника, поэтому ставим значение “2” и завершаем настройку нажатием кнопки OK.
  4. В ячейку с функцией будет отображен результат в виде числа, соответствующего дню недели. В нашем случае день недели – пятница, и ему присвоен порядковый номер “5” согласно выбранному нами варианту отсчета.
  5. Если изменить дату в исходной ячейке, то в конечной также будут автоматически внесены изменения.

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

Заключение

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

Источник: microexcel.ru

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

Как найти номер недели в Excel?

Если вы часто анализируете или проверяете данные, вам необходимо их группировать. Для сравнения с предыдущими периодами или прослеживания динамики вам нужна группировка. Быстро и правильно сгруппировать данные — одна из основ анализа. Очень часто группировка проходит по номеру недели. Как найти номер недели в Excel, мы разберем в этой статье.

Основные понятия. Номер недели в Excel. НОМНЕДЕЛИ

Задача: Найти номер недели для каждой даты в таблице (таблица может быть огромной).

Конечно, будем рассчитывать функцией, она называется =НОМНЕДЕЛИ( ).

  • Тип. Проставлять необязательно. Но важно! В большинстве англоязычных стран неделя начинается в воскресенье, поэтому, если ничего не поставить в этом реквизите в функцию, то 9.10.2016 будет считаться не 41 неделей (как в России), а 42 неделей. Чтобы все было как принято в РФ, нужно проставить реквизит Тип=3.

Будьте внимательны: незнание этого нюанса может серьезно испортить анализ!

Обратил внимание, что функция НОМНЕДЕЛИ не работает, если в качестве первого реквизита поставить столбец B:B.

Примечание: В большинстве стран если первая неделя года меньше 4 дней, то она не считается 1 неделей. В т.ч. используется в ISO 8601.

Номер недели в Excel. Как считать номер недели в сводной таблице?

Что такое сводная таблица, вы можете прочитать здесь . Если вы еще не знаете, нужно узнать обязательно.

У нас так же рассмотрен вариант группировки данных в сводной таблице . Можно предположить, что можно сгруппировать данные в ней по неделям. Но:

В возможных вариантах нет недель!

Какой здесь выход: Выберите вариант дни и заполните поле по окном «с шагом» Количество дней равным 7.

Источник: excelworks.ru