Определение по дате дня недели. Примеры использования функций день с деньнед и дни в excel Функция excel определение дня недели

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

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

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

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, а затем округляет результат.

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

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

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 день недели по дате.

Определить день неделиможно несколькими способами и в этой статье мы рассмотрим 3 варианта:

С помощью пользовательского формата

Очень простой способ определить день недели по дате, делается это следующим образом:

• выделяете , в которых хотите внести изменения в отображении даты;
• вызываете контекстное меню правой кнопкой мыши и выбираете пункт «Формат ячеек» ;
• в появившемся окне выбираете вкладку «Число» , в разделе «Числовые форматы» вам нужно выбрать пункт «(все форматы)» и в поле тип внести нужный вам формат даты.

Сами форматы данных можно разделить на 2 типа: полный и сокращенный .

• Полный формат обозначаеться полным названием дня недели или месяца (к примеру, среда, четверг, и т.д.), полный формат указывается «ДДДД» — для дней недели, «ММММ» — для месяцев.
• Сокращенный формат отображается только двумя буквами (к примеру, Ср, Чт, Пт, и т.д.) и для этого указывается формат «ДДД» — для дней недели, «МММ» — для месяцев.

С помощью функции ТЕКСТ

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

Орфография формулы выглядит так:

=ТЕКСТ (А1;«дддд») , формула возвращает текстовое значение дня недели.

С помощью функции ДЕНЬНЕД

Третий вариант узнать день недели по дате, это с помощью функции ДЕНЬНЕД, которая относится к группе «Дата и время» , там вы ее и найдете. Эта функция возвращает число от 1 до 7, что соответствуют дню недели.
Формула выглядит так:

=ДЕНЬНЕД(А1;2) , где:

• «А1 » – это адрес ячейки где дата в числовом формате,
• «2 » – это число которое определяет тип возвращаемого значения (существует 3 типа: 1 или отсутствует – возвращает число от 1 (воскресенье) до 7 (суббота), 2 – от 1 (понедельник) до 7 (воскресенье) и 3 – от 0 (понедельник) до 6 (воскресенье), нам привычней всего 2 тип).

Думаю вам станет интересной статья « » в ней вы найдете полезные советы и примеры при работе с датами.

А на этом у меня всё! Я очень надеюсь, что что тема как определить в 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 .