Как в excel даты перевести в недели
Обновлено: 08.07.2024
Необходимость определить и вывести номер недели по дате возникает очень часто. Это особенно актуально для тех людей, которые занимаются различной отчетностью. Например, готовят для руководства краткие итоги работы за неделю.
Вы должны помнить, что существует как минимум несколько походов к нумерации номера недели.
- Первый подход используется России. Согласно стандарту ГОСТ ИСО 8601-2001первой рабочей неделей года считается та, на которую выпадает первый четверг года.
- Второй подход заключается в том, что неделя, на которую приходится 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 меняем на дату или ссылку на дату.
Функция называется НОМНЕДЕЛИ (в английской версии WEEKNUM). Синтаксис очень простой.
Очень важен второй параметр Тип, так как по умолчанию используется второй из вышеописанных подходов к началу отсчета номера недели.
Для России необходимо использовать формулу в следующем формате.
=НОМНЕДЕЛИ( А1 ; 21)
А1 – это дата или ссылка на дату.
В данном случае тип 21 означает, что номер первой недели будет считаться та, на которую приходится первый четверг года и первым днем недели является понедельник (примечание: во многих странах первым днем недели считается воскресенье)
Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.
Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.
Во-вторых, вопрос - какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.
Способ 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 года. Синтаксис этой функции таков:
При работе в программе Excel иногда ставится задача, чтобы после ввода конкретной даты в ячейку выводился день недели, который ей соответствует. Естественно, решить данную задачу посредством такого мощного табличного процессора, как Эксель, возможно, причем несколькими способами. Давайте посмотрим, какие варианты существуют для выполнения данной операции.
Отображение дня недели в Экселе
Существуют несколько способов отобразить день недели по введенной дате, начиная от форматирования ячеек и заканчивая применением функций. Давайте взглянем на все существующие варианты выполнения указанной операции в Экселе, чтобы пользователь мог выбрать самый лучший из них для конкретной ситуации.
Способ 1: применение форматирования
Прежде всего, посмотрим, как при помощи форматирования ячеек можно отобразить день недели по введенной дате. Данный вариант подразумевает преобразование даты в указанное значение, а не сохранение отображения обоих этих видов данных на листе.
- Вводим любую дату, содержащую данные о числе, месяце и годе, в ячейку на листе.
В поле «Тип» окна форматирования вместо значения «ДДДД» можно также ввести выражение:
В этом случае на листе будет отображено сокращенное наименование дня недели.
Способ 2: использование функции ТЕКСТ
Но способ, который был представлен выше, предусматривает преобразование даты в день недели. Существует ли вариант, чтобы оба эти значения выводились на листе? То есть, если в одной ячейке мы вводим дату, то в другой должен выводиться день недели. Да, такой вариант существует. Его можно осуществить при помощи формулы ТЕКСТ. В этом случае нужное нам значение будет выводиться в указанную ячейку в текстовом формате.
-
Записываем дату на любом элементе листа. Затем выделяем любую пустую ячейку. Щелкаем по пиктограмме «Вставить функцию», которая расположена возле строки формул.
В поле «Значение» нам нужно указать адрес той ячейки, в которой содержится дата. Для этого устанавливаем курсор в указанное поле и левой кнопкой мыши щелкаем по этой ячейке на листе. Адрес тут же отобразится.
В поле «Формат» в зависимости от того, какое мы хотим иметь представление дня недели полное или сокращенное, вводим выражение «дддд» или «ддд» без кавычек.
Более того, если в ячейке изменить значение даты, то соответственно автоматически изменится и день недели. Таким образом, меняя дату можно узнавать на какой день недели она будет приходиться.
Способ 3: применение функции ДЕНЬНЕД
Имеется ещё один оператор, который может выводить день недели по заданной дате. Это функция ДЕНЬНЕД. Правда, она выводит не название дня недели, а его номер. При этом пользователь может устанавливать с какого дня (с воскресенья или с понедельника) нумерация будет отсчитываться.
-
Выделяем ячейку для вывода номера дня недели. Щелкаем по значку «Вставить функцию».
В поле «Дата в числовом формате» вводим конкретную дату или адрес ячейки на листе, в которой она содержится.
В поле «Тип» задается число от 1 до 3, которое определяет, как именно будут нумероваться дни недели. При установке числа «1» нумерация будет происходить, начиная с воскресенья, причем этому дню недели будет присвоен порядковый номер «1». При установке значения «2» нумерация будет выполняться, начиная с понедельника. Этому дню недели будет дан порядковый номер «1». При установке значения «3» нумерация так же будет происходить с понедельника, но в этом случае понедельнику будет присвоен порядковый номер «0».
Аргумент «Тип» не является обязательным. Но, если его опустить, то считается, что значение аргумента равно «1», то есть, неделя начинается с воскресенья. Так принято в англоязычных странах, но нам данный вариант не подходит. Поэтому в поле «Тип» ставим значение «2».
Как и с предыдущей функцией, при изменении даты автоматически изменяется номер дня недели в ячейке, в которой установлен оператор.
Как видим, в Экселе существуют три основных варианта представления даты в виде дня недели. Все они относительно просты и не требуют от пользователя наличия каких-то специфических умений. Один из них заключается в применении специальных форматов, а два других для достижения указанных целей используют встроенные функции. Учитывая, что механизм и способ отображения данных в каждом описанном случае существенно отличаются, пользователь должен сам выбрать, какой из указанных вариантов в конкретной ситуации ему подходит более всего.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Есть ли способ получить номер недели по заданной дате или извлечь диапазон дат из определенного номера недели и года в Excel? Для решения этой задачи вам могут помочь следующие формулы.
Преобразование номера недели в дату с помощью формул
Предположим, у меня есть случайный номер года и недели, которые равны 2015 и 15 на листе, как показано на следующем снимке экрана, и теперь я хочу узнать конкретные даты с понедельника по воскресенье по этому заданному номеру недели.
Чтобы рассчитать диапазон дат по конкретному номеру недели, вы можете использовать следующие формулы:
1. Выберите пустую ячейку, вы вернете дату начала (в нашем случае мы выбираем ячейку B5), введите формулу: =MAX(DATE(B1,1,1),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+(B2-1)*7+1) , и нажмите Enter ключ. Смотрите скриншот ниже:
2. Выберите другую пустую ячейку, вы вернете дату окончания (в нашем случае мы выбираем ячейку B6), введите =MIN(DATE(B1+1,1,0),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+B2*7) , и нажмите Enter ключ. Смотрите скриншот ниже:
 | Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия |
Примечание. В обеих формулах выше B1 содержит год и B2 - указанный номер недели, вы можете изменить аргументы по своему усмотрению).
3. Как видите, обе формулы возвращают числа вместо дат. Продолжайте выбирать оба результата расчета и нажмите Главная > Формат номера коробка> Короткое свидание чтобы заменить числа на даты. Смотрите скриншот ниже:
Один щелчок, чтобы преобразовать несколько нестандартных дат / чисел / текста форматирования в обычные даты в Excel
Kutools для Excel Преобразовать в дату Утилита может помочь вам легко определить и преобразовать нестандартные даты или числа (ггггммдд) или текст в обычные даты одним щелчком мыши в Excel. Полнофункциональная бесплатная 30-дневная пробная версия!
Преобразование даты в номер недели с помощью формул
С другой стороны, вы также можете применить функцию WEEKNUM для преобразования даты в соответствующий номер недели.
1. Выберите пустую ячейку, вы вернете номер недели, введите эту формулу: = WEEKNUM (B1,1) и нажмите клавишу Enter. Смотрите скриншот:
Ноты:
(1) В приведенной выше формуле B1 содержит дату, которую вы хотите использовать.
(2) Если вам нужно вернуть номер недели с даты, которая начинается в понедельник, примените следующую формулу: = WEEKNUM (B1,2) .
Особенности расчета: нумерация и определение первого дня
Во-первых, в зависимости от страны может отличаться способ определения первой недели года.
В России (согласно ГОСТ ИСО 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(дата; [тип])
Возвращает номер недели года.
В качестве примера рассмотрим 2017 г. где 1 января приходится на воскресенье, а 2 января на понедельник.
Соответственно, получается, что в американском варианте расчета недели меняются между 7 и 8 января, а в российском между 8 и 9 января:
Также возможные и другие различные значения аргумента Тип:
Спасибо за внимание!
Если у вас остались вопросы по теме статьи — пишите в комментариях.
Читайте также: