Excel меняем месяц меняются дни недели. Как функция деньнед определяет конкретные даты в excel

В этой статье описаны синтаксис формулы и использование функции ДЕНЬНЕД в Microsoft Excel.

Описание

Возвращает день недели, соответствующий дате. По умолчанию день недели определяется как целое число в интервале от 1 (воскресенье) до 7 (суббота).

Синтаксис

ДЕНЬНЕД(дата_в_числовом_формате;[тип])

Аргументы функции ДЕНЬНЕД описаны ниже.

Дата_в_числовом_формате Обязательный. Порядковый номер, соответствующий дате, день недели для которой необходимо найти. Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. следует воспользоваться выражением ДАТА(2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.

Тип Необязательный. Число, определяющее тип возвращаемого значения.

	Возвращаемое число
1 или опущен	Число от 1 (воскресенье) до 7 (суббота). Аналогично предыдущей версии Microsoft Excel
	Число от 0 (понедельник) до 6 (воскресенье)
	Число от 1 (понедельник) до 7 (воскресенье)
	Число от 1 (вторник) до 7 (понедельник).
	Число от 1 (среда) до 7 (вторник).
	Число от 1 (четверг) до 7 (среда).
	Число от 1 (пятница) до 7 (четверг).
	Число от 1 (суббота) до 7 (пятница).
	Число от 1 (воскресенье) до 7 (суббота).

Замечание

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дню 1 января 1900 г. соответствует число 1, а 1 января 2008 г. - число 39 448, поскольку интервал между этими датами составляет 39 448 дней.

Если значение "дата_в_числовом_формате" находится вне диапазона для текущего базового значения даты, возвращается ошибка #ЧИСЛО!.

Если значение "тип" не входит в диапазон, указанный в таблице выше, возвращается ошибка #ЧИСЛО!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

При работе в программе Excel иногда ставится задача, чтобы после ввода конкретной даты в ячейку выводился день недели, который ей соответствует. Естественно, решить данную задачу посредством такого мощного табличного процессора, как Эксель, возможно, причем несколькими способами. Давайте посмотрим, какие варианты существуют для выполнения данной операции.

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

Способ 1: применение форматирования

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

В поле «Тип» окна форматирования вместо значения «ДДДД» можно также ввести выражение:

В этом случае на листе будет отображено сокращенное наименование дня недели.

Способ 2: использование функции ТЕКСТ

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

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

Способ 3: применение функции ДЕНЬНЕД

Имеется ещё один оператор, который может выводить день недели по заданной дате. Это функция ДЕНЬНЕД . Правда, она выводит не название дня недели, а его номер. При этом пользователь может устанавливать с какого дня (с воскресенья или с понедельника) нумерация будет отсчитываться.

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

Рис. 6.2. Панель функции "ДЕНЬНЕД"

После этих действий ячейка А2 будет содержать следующую формулу:

=ДЕНЬНЕД(А1)

Эта формула вернет результат, равный 1. Значит, интересующий нас день - воскресенье.

Данная статья содержит несколько полезных формул, возвращающих конкретную дату.

Определение дня в году

1 января - это первый день года, а 31 декабря - последний. А как насчет остальных дней, идущих между ними? Следующая формула возвращает день года для даты, хранящемся в ячейке A1: =A1-ДАТА(ГОД(A1);1;0) . Например, если ячейка A1 содержит дату 16 февраля 2010 года, формула возвращает 47, потому что эта дата является 47-м днем в году.

Следующая формула возвращает количество дней, оставшихся в году с момента определенной даты (предполагается, что она содержится в ячейке A1): =ДАТА(ГОД(A1);12;31) .

Определение дня недели

Если вам необходимо определить день недели для даты, функция ДЕНЬНЕД справится с этой задачей. Функция принимает в качестве аргумента дату и возвращает целое число от 1 до 7, соответствующее дню недели. Следующая формула, например, возвращает 6, потому что первый день 2010 года приходится на пятницу: =ДЕНЬНЕД(ДАТА(2010;1;1)) .

Функция ДЕНЬНЕД использует еще и необязательный второй аргумент, обозначающий систему нумерации дней для результата. Если вы укажете 2 в качестве второго аргумента, то функция вернет 1 для понедельника, 2 — для вторника и т. д. Если же вы укажете 3 в качестве второго аргумента, то функция вернет 0 для понедельника, 1 - для вторника и т. д.

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

Определение даты последнего воскресенья

Формула в этом разделе возвращает последний указанный день. Вы можете использовать следующую формулу для получения даты прошлого воскресенья. Если текущий день - воскресенье, то формула возвращает текущую дату. Результатом будет серийный номер даты (вам нужно отформатировать ячейку для отображения читабельной даты): =СЕГОДНЯ()-ОСТАТ(СЕГОДНЯ()-1;7) .

Чтобы изменить эту формулу для нахождения даты другого дня, а не воскресенья, поменяйте 1 на другое число в диапазоне от 2 (понедельник) до 7 (суббота).

Определение дня недели после даты

Следующая формула возвращает указанный день недели, который наступает после определенной даты. Например, вы можете применять эту формулу для определения даты первой пятницы после 4 июля 2010 года. Формула предполагает, что ячейка А1 содержит дату, а ячейка А2 - число от 1 до 7 (1 соответствует воскресенью, 2 - понедельнику и т. д.): =A1+A2-ДЕНЬНЕД(A1)+(А2

Если ячейка А1 содержит 4 июля. 2010, а ячейка А2 содержит б (что обозначает пятницу), то формула возвращает 9 июля, 2010. Это первая пятница после 4 июля 2010 года (дня, который приходится на воскресенье).

Нахождение n-го определенного дня недели в месяце

Вам может понадобиться формула для нахождения даты определенного по счету дня недели. Предположим, что день выплаты зарплаты в вашей компании приходится на вторую пятницу каждого месяца и вам нужно определить эти
дни выплат для каждого месяца года. Следующая формула выполнит требуемый расчет:
=ДАТА(А1;А2;1)+А3-ДЕНЬНЕД(ДАТА(А1;А2;1))+(А4-(А3>=ДЕНЬНЕД(ДАТА(А1;А2;1))))*7

Эта формула предполагает, что:

• ячейка А1 содержит год;
• ячейка А2 содержит месяц;
• ячейка A3 содержит номер дня (1 - воскресенье, 2 - понедельник и т. д.);
• ячейка А4 содержит число - например 2, указывающее второе появление дня недели, заданного в ячейке A3.

При использовании этой формулы для определения даты второй пятницы в июне 2010 года результатом будет 11 июня, 2010.

Определение последнего дня месяца

Чтобы определить дату, которой соответствует последний день месяца, вы можете использовать функцию ДАТА . Однако вам нужно увеличивать месяц на 1 и указывать в качестве значения дня 0. Другими словами, «0-й» день следующего месяца - это последний день текущего месяца.

Следующая формула предполагает, что дата хранится в ячейке А1. Формула возвращает дату, которой соответствует последний день месяца: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0) .

Вы можете модифицировать эту формулу, чтобы определить, сколько дней включает в себя указанный месяц. Следующая формула возвращает целое число, которое соответствует количеству дней в месяце для даты из ячейки А1 (убедитесь, что вы отформатировали ячейку как число, а не как дату): =ДЕНЬ(ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0))

Определение квартала даты

Для финансовых отчетов может оказаться полезным представление информации по кварталам. Следующая формула возвращает целое число от 1 до 4, которое соответствует календарному кварталу для даты в ячейке А1: =ОКРУГЛ ВВЕРХ(МЕСЯЦ(A1)/3;0) . Эта формула делит номер месяца на 3, а затем округляет результат.