Определение дня недели дате excel. Установка дня недели по дате в Microsoft 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 .
Функция ДЕНЬ возвращает день, который соответствует определенной дате. Результатом работы функции является число из диапазона от 1 до31.
Функция ДЕНЬНЕД возвращает день недели, который соответствует определенной дате. Результат работы данной функции – это число из диапазона от 1 до 7 (единица соответствует воскресенью, а семь – субботе).
Функция ДНИ возвращает числовое значение, характеризующее разницу дней между двумя заданными датами.
Особенности синтаксической записи функции ДЕНЬ, ДЕНЬНЕД и ДНИ в Excel
Функция ДЕНЬ имеет следующий синтаксис:
ДЕНЬ(дата)
Принимает на вход единственный параметр дата, который соответствует дате, день которой требуется определить.
Примечания:
- В качестве входного параметра обычно используется ячейка из таблицы Excel, содержащая данные в формате дата.
- Также на вход принимаются результаты вычислений функции ДАТА и прочих функций, возвращающих значения в формате Дата.
- При непосредственном вводе данных между скобками функции (например, =ДЕНЬ(“24.06.2018”)) необходимо использовать кавычки, указывая тем самым на то, что передаваемые на вход данные имеют тип Текст. Далее Excel производит автоматическое преобразование данных в формат Дата.
- Ошибка #ЗНАЧ! Будет сгенерирована в том случае, если на вход функции ДЕНЬ были переданы данные неподдерживаемого Excel типа, например: ДЕНЬ(“24-июня-2018”) или ДЕНЬ(“24 06 2018”).
- Параметр дата может быть представлен в виде числа в коде времени Excel.
Функция ДЕНЬНЕД имеет следующую синтаксическую запись:
ДЕНЬНЕД(дата;[тип])
Описание аргументов функции:
- Дата – обязательный параметр, соответствующей дате, день недели которой требуется определить. Данный параметр имеет особенности, описанные в примечаниях к предыдущей функции.
- Тип – число в диапазоне от 1 до 3, указывающее на то, какой день недели считается первым (в некоторых странах первый день недели – воскресенье, в нашей – понедельник). Если тип=1, первым днем недели будет считаться воскресенье, тип=2 – понедельник. Если тип=3, результатом работы функции ДЕНЬНЕД будет число из диапазона от 0 до 6, где 0 соответствует понедельнику.
Примечение: в некоторых случаях вместо чисел 1,2…7 требуется вывод названия дня недели (понедельник, вторник… воскресенье). Для этого можно составить такую таблицу:
И затем выводить текстовое значение, соответствующее номеру дня недели:
![](https://i0.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-2.png)
Однако для данной задачи можно использовать следующую функцию: =ТЕКСТ(A2;"dddd").
![](https://i1.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-3.png)
Результатом выполнения данной функции является текст «воскресенье». Если требуется краткая запись дня недели (Сб, Пн, Ср), в качестве второго параметра следует указать «dddd».
Функция ДНИ используется для расчета количества дней между двумя указанными датами и имеет следующий синтаксис: =ДНИ(кон_дата;нач_дата).
Описание аргументов данной функции:
- Кон_дата – обязательный параметр, характеризующий дату окончания какого-либо события.
- Нач_дата – обязательный параметр, характеризующий дату начала определенного события для выполнения расчета.
Примечания:
- При вычислении количества дней между датами выполняется преобразование данных в числа в коде времени Excel. Это означает, что записи «=ДНИ(“24.06.2018”;”13.06.2018”)» и «=ДАТАЗНАЧ(“24.06.2018”)-ДАТАЗНАЧ(“13.06.2018”)» вернут одинаковые значения.
- Если один из параметров функции представляет дату, записанную как текст, то он будет обработан функцией ДАТАЗНАЧ, которая вернет целочисленную дату.
- Если параметрами функции являются числа, значения которых находятся за пределами допустимых в коде времени Excel, будет сгенерирована ошибка #ЗНАЧ!.
- Если в функции ДНИ использованы параметры в виде данных, которые не могут быть представлены в виде дат в Excel, будет возвращена ошибка #ЗНАЧ!.
Примеры расчетов в Excel с использованием функций ДЕНЬ, ДЕНЬНЕД и ДНИ
В таблице содержится несколько дат, записанных в формате дат Excel, в которые сотрудникам некоторого предприятия должна быть выплачена заработная плата. В связи с принятыми изменениями в законодательстве начисление зарплат сотрудникам должно производиться в первых числах каждого месяца. Необходимо исправить даты с 15/07/2018 на 01/07/2018. Часть таблицы выглядит следующим образом:
![](https://i0.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-4.png)
Для решения используем следующую формулу: =B3-ДЕНЬ(B3)+1.
Аналогично заполним остальные колонки. В результате получим новый столбец с правильными датами выдачи зарплаты:
![](https://i2.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-5.png)
Определение рабочих и выходных дней по формуле ДЕНЬНЕД
Пример 2. Программный продукт считывает данные из таблицы Excel и использует их в своих расчетах. Одна из ячеек содержит данные в формате Дата, соответствующие дате получения посылки. Необходимо определить, соответствует ли указанная дата рабочему дню недели (с Пн по Пт) и вернуть логическое значение “true” или “false” (если выходной день) для дальнейшей обработки программой.
Исходная таблица имеет следующий вид:
![](https://i2.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-6.png)
Для определения дня недели будем использовать ДЕНЬНЕД, первым аргументом которой является данные из ячейки «Дата прибытия», а вторым – 2 (отсчет дней недели начинается с единицы, которая соответствует понедельнику). Для решения используем следующую функцию:
Как видно, все даты являются рабочими днями недели.
Зачем нужна функция ДНИ в Excel?
Пример 3. Общеизвестно, что ВОВ была начата 22 июня 1941 года и закончилась 9 мая 1945 года. Необходимо определить, сколько дней проходили военные действия.
Внесем исходные данные в таблицу:
![](https://i0.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-8.png)
Для расчета используем следующую формулу: =ДНИ(B3;A3).
![](https://i0.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-9.png)
Получаем следующий результат:
![](https://i0.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-10.png)
То есть, военные действия длились на протяжении 1417 дней.
Обратите внимание! Любая дата в Excel – это число дней, прошедших начиная от даты 01.01.1900г. Подробнее описано в статье: Как вычислить дату в Excel ? Поэтому можно свободно использовать такую простую формулу без функций: =B3-A3.
![](https://i2.wp.com/exceltable.com/funkcii-excel/images/funkcii-excel77-11.png)
Как видно на рисунке результат аналогичен. Фактически функция ДНИ в 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, а затем округляет результат.
Доброго времени суток друзья!
Эту статью я посвятил теме, как определить в Excel день недели по дате. Очень часто с этим вопросом сталкиваются по заработной плате и специалисты отдела кадров, хотя и решений вопросов, когда надо знать припадает ли данное число на рабочий день недели или нет, достаточно много. К этим задачам можно отнести расчёт больничного листа, определения праздничных дней, определения отпускных дней, да и, впрочем, еще много задач где нужно определить в Excel день недели по дате.
Определить день неделиможно несколькими способами и в этой статье мы рассмотрим 3 варианта:
С помощью пользовательского формата
Очень простой способ определить день недели по дате, делается это следующим образом:
- выделяете , в которых хотите внести изменения в отображении даты;
- вызываете контекстное меню правой кнопкой мыши и выбираете пункт «Формат ячеек» ;
- в появившемся окне выбираете вкладку «Число» , в разделе «Числовые форматы» вам нужно выбрать пункт «(все форматы)» и в поле тип внести нужный вам формат даты.
Сами форматы данных можно разделить на 2 типа: полный
и сокращенный
.
- Полный формат обозначаеться полным названием дня недели или месяца (к примеру, среда, четверг, и т.д.), полный формат указывается «ДДДД» — для дней недели, «ММММ» — для месяцев.
- Сокращенный формат отображается только двумя буквами (к примеру, Ср, Чт, Пт, и т.д.) и для этого указывается формат «ДДД» — для дней недели, «МММ» — для месяцев.
С помощью функции ТЕКСТ
В этом случае мы будем использовать встроенную , для того чтобы определить в Excel день недели по дате. Эта функция предназначена, чтобы форматировать число в ячейке и преобразовать его в текст. Используя эту функцию, вы первым значением указываете источник данных, а потом указываете формат преобразования данных. Формат вы указываете сокращенный
или полный
, как я описал выше в первом пункте.
Орфография формулы выглядит так:
=ТЕКСТ (А1;«дддд») , формула возвращает текстовое значение дня недели.
С помощью функции ДЕНЬНЕД
Третий вариант узнать день недели по дате, это с помощью функции ДЕНЬНЕД, которая относится к группе «Дата и время»
, там вы ее и найдете. Эта функция возвращает число от 1 до 7, что соответствуют дню недели. Формула выглядит так:
=ДЕНЬНЕД(А1;2) , где:
- «А1 » – это адрес ячейки где дата в числовом формате,
- «2 » – это число которое определяет тип возвращаемого значения (существует 3 типа: 1 или отсутствует – возвращает число от 1 (воскресенье) до 7 (суббота), 2 – от 1 (понедельник) до 7 (воскресенье) и 3 – от 0 (понедельник) до 6 (воскресенье), нам привычней всего 2 тип).
Думаю вам станет интересной статья « » в ней вы найдете полезные советы и примеры при работе с датами.
А на этом у меня всё! Я очень надеюсь, что
что тема как определить в Excel день недели по дате, была раскрыта и стала вам полезной. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!Для построения диаграмм или другого анализа, в табличном редакторе ногда требуется отображать день недели в Excel в ячейке с датой. Сделать это можно несколькими способами. Каждый из который опишем отдельно.
В этом случае при помощи встроенных в Excel функций введённые данные в формате даты преобразуются в день недели. В начале нужно ввести информацию в ячейку: . По клику правой кнопки мыши на ячейке отобразится контекстное меню, в котором нужно выбрать пункт «Формат». В появившемся окне на вкладке ЧИСЛО нужно выбрать из предложенных вариантов строку «все форматы».
Далее в правом окне понадобится ввести значение ДДДД . В поле «Образец» автоматически отобразится день недели, соответствующий введённой ранее дате. В редакторе содержимое строки изменилось, но, если навести на него курсор и нажать левую кнопку мыши, в строке формул отобразится только дата. За тип форматирования также можно принять строку ДДД , которая преобразует содержимое в сокращённое название дня недели (вторник – Вт, среда – Ср и т.д.)
Вариант №2: Применение функции ТЕКСТ, чтобы получить день недели в Excel
Редактор Excel в комбинации с необходимыми для форматирования функциями представляет из себя универсальный инструмент по работе c таблицами. Для преобразования даты следует ввести её в выбранную ячейку и выделить любую свободную область на листе. Левее поля «Строки формул» находится кнопка «Вставить функцию». После нажатия на неё левой кнопкой мыши появится окно настроек:
- Нужно выбрать из списка категорий значение «Текстовые».
- В перечне доступных функций выбрать вариант под названием ТЕКСТ и нажать OK, чтобы перейти к вводу аргументов.
- В параметрах отмечены два поля для установки: значение и формат. В качестве первого вводится адрес ячейки с датой. Это можно сделать вручную или поставить в нужное место курсор и клацнуть левой кнопкой мыши. После этого адрес должен автоматически отобразиться в строке «Значение».
- В поле «Формат» понадобится ввести выражения ДДДД или ДДД (для стандартного сокращения текста) и согласиться с аргументами.
Таким образом на листе появляются два значения одного типа, но в разном формате: дата и наименование дня недели, соответствующее ей. Этот метод позволяет использовать данные в совместимом виде. При изменении даты, преобразуется и значение дня недели. В строке формул можно вручную задавать функции, чтобы ускорить процесс редактирования: =ТЕКСТ(A1;«ДДДД »).
Вариант №3: Вставка функции ДЕНЬНЕД
Эта функция аналогична предыдущей, но преобразует текстовое значение в числовое. В этом случае у пользователя есть расширенные возможности.: установка старта отсчёта на один из двух выбранных дней – понедельника и воскресенья. Для ввода нужно пройти следующую процедуру:
- Выделить ячейку, в которую будет помещено преобразованное значение.
- Войти в настройки мастера функций и из каталога категорий выбрать строку «Дата и время».
- Из представленного списка понадобится функция ДЕНЬНЕД , аргументами которой являются «дата в числовом формате» и тип.
- В качестве первого нужно ввести необходимую дату ДД.ММ.ГГГГ . Для удобства можно указать адрес ячейки с соответствующим содержимым.
- Аргумент ТИП принимает три значения: 1, 2, 3. При указании единички, дни недели будут отсчитываться с воскресенья. Двоечка – это понедельник. Тройка – отсчёт начинается с понедельника, который будет приниматься счётчиком как «0» (то есть вторник при этом будет отображён в виде «1»).
Для ручного ввода в строке формул нужно написать: =ДЕНЬНЕД(A3;2). Не обязательно указывать параметр ТИП. В случае его отсутствия, редактор устанавливает значение по-умолчанию «1». Это актуально для Великобритании и других англоязычных государств, где неделя начинается именно с воскресенья. В странах СНГ понадобится самостоятельно вводить параметр при каждой активации функции.
Применение операторов обусловлено широкими функциональными возможностями, так как при изменении значения даты, содержимое ячейки с функцией также конвертируется. При работе с табличными базами данных это самый оптимальный метод преобразования форматов для получения значения день недели в Excel.
Поделитесь нашей статьей в ваших соцсетях: