→ → Excel день недели из числа. Установка дня недели по дате в Microsoft Excel. Определение дня в году

Excel день недели из числа. Установка дня недели по дате в Microsoft Excel. Определение дня в году

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

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

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

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

ЦЕЛОЕ((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)

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

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

сайт – Безграничные возможности Excel

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

В этом случае при помощи встроенных в Excel функций введённые данные в формате даты преобразуются в день недели. В начале нужно ввести информацию в ячейку: . По клику правой кнопки мыши на ячейке отобразится контекстное меню, в котором нужно выбрать пункт «Формат». В появившемся окне на вкладке ЧИСЛО нужно выбрать из предложенных вариантов строку «все форматы».

Далее в правом окне понадобится ввести значение ДДДД . В поле «Образец» автоматически отобразится день недели, соответствующий введённой ранее дате. В редакторе содержимое строки изменилось, но, если навести на него курсор и нажать левую кнопку мыши, в строке формул отобразится только дата. За тип форматирования также можно принять строку ДДД , которая преобразует содержимое в сокращённое название дня недели (вторник – Вт, среда – Ср и т.д.)

Вариант №2: Применение функции ТЕКСТ, чтобы получить день недели в Excel

Редактор Excel в комбинации с необходимыми для форматирования функциями представляет из себя универсальный инструмент по работе c таблицами. Для преобразования даты следует ввести её в выбранную ячейку и выделить любую свободную область на листе. Левее поля «Строки формул» находится кнопка «Вставить функцию». После нажатия на неё левой кнопкой мыши появится окно настроек:

  • Нужно выбрать из списка категорий значение «Текстовые».
  • В перечне доступных функций выбрать вариант под названием ТЕКСТ и нажать OK, чтобы перейти к вводу аргументов.
  • В параметрах отмечены два поля для установки: значение и формат. В качестве первого вводится адрес ячейки с датой. Это можно сделать вручную или поставить в нужное место курсор и клацнуть левой кнопкой мыши. После этого адрес должен автоматически отобразиться в строке «Значение».
  • В поле «Формат» понадобится ввести выражения ДДДД или ДДД (для стандартного сокращения текста) и согласиться с аргументами.

Таким образом на листе появляются два значения одного типа, но в разном формате: дата и наименование дня недели, соответствующее ей. Этот метод позволяет использовать данные в совместимом виде. При изменении даты, преобразуется и значение дня недели. В строке формул можно вручную задавать функции, чтобы ускорить процесс редактирования: =ТЕКСТ(A1;«ДДДД »).

Вариант №3: Вставка функции ДЕНЬНЕД

Эта функция аналогична предыдущей, но преобразует текстовое значение в числовое. В этом случае у пользователя есть расширенные возможности.: установка старта отсчёта на один из двух выбранных дней – понедельника и воскресенья. Для ввода нужно пройти следующую процедуру:

  • Выделить ячейку, в которую будет помещено преобразованное значение.
  • Войти в настройки мастера функций и из каталога категорий выбрать строку «Дата и время».
  • Из представленного списка понадобится функция ДЕНЬНЕД , аргументами которой являются «дата в числовом формате» и тип.
  • В качестве первого нужно ввести необходимую дату ДД.ММ.ГГГГ . Для удобства можно указать адрес ячейки с соответствующим содержимым.
  • Аргумент ТИП принимает три значения: 1, 2, 3. При указании единички, дни недели будут отсчитываться с воскресенья. Двоечка – это понедельник. Тройка – отсчёт начинается с понедельника, который будет приниматься счётчиком как «0» (то есть вторник при этом будет отображён в виде «1»).

Для ручного ввода в строке формул нужно написать: =ДЕНЬНЕД(A3;2). Не обязательно указывать параметр ТИП. В случае его отсутствия, редактор устанавливает значение по-умолчанию «1». Это актуально для Великобритании и других англоязычных государств, где неделя начинается именно с воскресенья. В странах СНГ понадобится самостоятельно вводить параметр при каждой активации функции.

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

Поделитесь нашей статьей в ваших соцсетях:

Предположим, вам необходимо определить, какому дню недели соответствует дата, находящаяся в интервале от 1900 до 9999 года. Сделать это в Excel довольно просто, но в качестве ответа вы получите порядковый номер дня недели. К тому же в зависимости от установок номеру могут отвечать разные дни. Конечно, это не очень удобно. Поэтому давайте составим модуль, который будет определять по дате день недели и возвращать не его порядковый номер, а название.

День недели позволяет определить функция "ДЕНЬНЕД", которую можно ввести, вызвав мастер функций и выделив в списке Категория элемент Дата и время (рис. 6.1). Синтаксис функции следующий:

Она возвращает день недели, соответствующий первому аргументу дата_в_числовом_формате. День недели будет представлен как целое число в интервале от 1 (воскресенье) до 7 (суббота).

Аргумент дата_в_числовом_формате - это код дата-время, который Excel применяет для операций в датами. Данный аргумент можно задавать и в текстовом виде, заключив строку в кавычки. Тип - это число, определяющее нумерацию дней недели. Аргумент Тип может принимать следующие значения:

1 (либо опущено) - число от 1 (воскресенье) до 7 (суббота);
- 2 - число от 1 (понедельник) до 7 (воскресенье);
- 3 - число от 0 (понедельник) до 6 (воскресенье).

Таким образом, в зависимости от типа, указанного в функции "ДЕНЬНЕД", 5-й день недели может быть четвергом, пятницей или субботой. Чтобы не задавать второй аргумент, будем считать, что 5-й день недели - это четверг.

Введите в ячейку А1 любую дату, например 6.01.2002 г. Для наглядности задайте для этой ячейки формат даты. Перейдите в ячейку А2 и вызовите панель функции "ДЕНЬНЕД" (рис. 6.2). В поле Дата_как_число введите адрес ячейки А1 .

Здесь рассмотрим, как преобразовать дату в день недели в Excel . Этот прием пригодится при составлении календаря, бланка, табеля, т.д. У нас есть таблица. В столбце В стоят даты. В столбце А мы хотим написать дни недели (Пн, Вт, т.д.). Устанавливаем в столбце А даты.
Теперь в столбце А заменим числа дат на день недели. Выделяем ячейки столбца А (А3:А10). Заходим в формат ячеек, выбираем функцию «Все форматы».
В строке «Тип» диалогового окна «Формат ячеек» пишем такой формат – ДДД.
Нажимаем «ОК». Получилось так.
Этот способ можно применить в табеле учета рабочего времени. В строке с датами устанавливем формат даты - только число (Д). Тогда, в ячейке будем писать всю дату, например - 12.01.2016. А в ячейке с этим форматом будет видно только число - 12. Достаточно установить такой формат в первой ячейке, написать в ней дату. Затем, протянуть эту ячейку по столбцу или строке, и, одновременно, скопируется формат даты и ячейки заполнятся последовательными датами.
Какие форматы дат можно установить, смотрите в статье "Как создать пользовательский формат в Excel" .
Бланк табеля учета рабочего времени можно посмотреть и скачать в статье "Табель учета рабочего времени в Excel".
Как определить по дате номер квартала, начало и конец квартала, читайте в статье "Номер квартала в Excel ".
Как определить дату по дню недели или, например, дату третьего воскресенья марта 2016 г, т.д., смотрите в статье "Узнать дату в Excel по дню недели, т.д." . Таким способом можно узнать дату и будущюу и прошедшую.
Как сложить даты, прибавить к дате дни, месяцы, года, как посчитать стаж по датам, возраст, т.д., смотрите в статье "Дата в Excel. Формула" .
В Excel можно быстро передвинуть столбец, таблицу, строку. Как это сделать, смотрите в статье "

День недели, а именно "понедельник", "вторник", "среда" и так далее, можно получить в Excel формулой по дате. То есть, когда есть дата и необходимо, чтобы рядом в ячейке появилось названия дня недели, можно это сделать формулой. Причем, в Интернете "гуляет" несколько формул для этого. Мы не будем рассматривать их, приведем только одну (по нашему мнению, наиболее оптимальную), итак

Формула день недели по дате

ТЕКСТ(B8;"ДДДД")

И небольшая модификация (если необходимо, чтобы день недели был написан с прописной буквы).

ПРОПНАЧ(ТЕКСТ(B8;"ДДДД"))

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

Мы кратко рассмотрели очередную формулу в рамках серии статей, которые мы назвали "1 формула в день". Если Вас интересует базовый уровень Excel, или Вам необходим "продвинутый", , чтобы получить помощь, обучение или консультацию. Сайт ExcelSkype – удаленное обучение Excel через Skype.

 

 
Это интересно: