Номер недели по дате функцией НОМНЕДЕЛИ
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.
Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.
Во-вторых, вопрос - какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.
Способ 1. Стандарт ГОСТ ИСО 8601-2001 и функция НОМНЕДЕЛИ.ISO
Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52 неделей предыдущего года.
Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу:
=ОТБР(ОСТАТ(A1+3-ДЕНЬНЕД(A1;2);365,25)/7+1)
=TRUNC(MOD(A1+3-WEEKDAY(A1;2);365,25)/7+1)
В Excel 2013 функцию для расчета номера недели по ISO добавили в стандартный набор – она называется НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)
Способ 2. Неделя с 1-м января и функция НОМНЕДЕЛИ (WEEKNUM)
В этом случае первой неделей года считается та, на которую попадает 1 января. Здесь также возникает сложность в том, что в году может оказаться 52 или 53 недели и, плюс ко всему, 1 января может выпасть на воскресенье, т.е. шесть последних дней года могут оказаться уже в неделе с номером 1, что затрудняет отчетность. Тем не менее, если такой способ нумерации вам нужен, то его можно реализовать функцией НОМНЕДЕЛИ (WEEKNUM), появившейся в Excel начиная с 2007 года. Синтаксис этой функции таков:
=НОМНЕДЕЛИ(Дата; Тип_отсчета)
где
- Дата - ячейка с датой, для которой нужно определить номер недели
- Тип_отсчета - число (1, 2 или 3), обозначающее тип нумерации дней в неделе. Вариант 1 - американский (неделя начинается с воскресенья), вариант 2 - наш.
Ссылки по теме
- Основные принципы работы с датами и временем в Microsoft Excel
- Номер квартала по дате
- Вычисление возраста или стажа в годах, месяцах, днях
- Выпадающий календарь для удобного ввода даты из надстройки PLEX
=INT((A1-40182)/7)
Тоже отлично работает, проблем ниразу не было..
Т.е. формула не универсальна. Вообще, в ГОСТе дословно сказано следующее: "Каждая неделя может быть отнесена только к одному году. Первой неделей года является неделя, содержащая первый четверг данного года, иначе она считается последней неделей предыдущего года"..
Ексель 2007 - отлично разделил недели при переходе 2011-2012,
31 декабря - 53 неделя
1 января - 1 неделя, хотя это было воскресенье той же недели.
Исправьте в тексте, пожалуйста. Уверен, есть лентяи, кто не читает комменты под текстом
При использовании функции НОМНЕДЕЛИ вторым параметром после ячейки с датой предлагается задать день начала недели. При этом, в списке предлагаемых Excel'ем вариантов дважды встречается воскресенье (1 и 17) и трижды - понедельник (2, 11 и 21).
В чем разница между этими тремя "понедельниками" и двумя "воскресеньями"?
Т,е., например, тип=11 - это по первой системе с неделей с понедельника, а тип=21 - это по второй системе с неделей с понедельника.
А простые типы 1 и 2 оставлены для совместимости со старыми версиями, я полагаю, где ничего, кроме 1 и 2 тогда не было, двузначные варианты добавили начиная с 2013 версии.
Моя ошибка - слишком глубоко начал копать.
Спасибо, что надоумили.
Сам себе и отвечу (может кому пригодится, кто работает с графиками работ).
В ячейке В16 стоит дата (пусть 15), І6 - год (2014), В9 - месяц (1).
15.01.2014 есть среда
В ячейке В16 выбираем формат/условное форматирование/формула (формат ячейки на свой вкус):
=WEEKDAY(DATE($I$6;B$9;B16);2)>5
Дальше копируем ячейку В16, выделяем ячейки в таблице, вставка/специальная вставка/форматы.
Вот и все!
нужно ещё что-то выдумывать
корректный вариант ниже
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6));1;5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6));1;3)))/7)
Неделя по ISO для России:
=НОМНЕДЕЛИ("01.01.2017";21)
С 2013 Excel, можно еще так:
=НОМНЕДЕЛИ.ISO("01.01.2017")
Я прав?
Почему по формуле =ОТБР(ОСТАТ(B126+3-ДЕНЬНЕД(B126;2);365,25)/7+1) ... 7 августа 2017 г. -1-я неделя? Применяю формулу с
(Хотела вставить картинку...не получается)
При этом, 31 июля (понедельник) = 31-я неделя, а 7 августа...елки-палки = 1-я
Да, ячейка В126 в составе массива (так же как и предыдущие ячейки, по которым номер недели считается правильно)
Т.е. указав 3.02.2022 получить: за 1 нед. февраля 2022 г.
Подсказали: