Функция ДЕНЬ возвращает день, который соответствует определенной дате. Результатом работы функции является число из диапазона от 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 требуется вывод названия дня недели (понедельник, вторник… воскресенье). Для этого можно составить такую таблицу:
И затем выводить текстовое значение, соответствующее номеру дня недели:
Однако для данной задачи можно использовать следующую функцию: =ТЕКСТ(A2;"dddd").
Результатом выполнения данной функции является текст «воскресенье». Если требуется краткая запись дня недели (Сб, Пн, Ср), в качестве второго параметра следует указать «dddd».
Функция ДНИ используется для расчета количества дней между двумя указанными датами и имеет следующий синтаксис: =ДНИ(кон_дата;нач_дата).
Описание аргументов данной функции:
- Кон_дата – обязательный параметр, характеризующий дату окончания какого-либо события.
- Нач_дата – обязательный параметр, характеризующий дату начала определенного события для выполнения расчета.
Примечания:
- При вычислении количества дней между датами выполняется преобразование данных в числа в коде времени Excel. Это означает, что записи «=ДНИ(“24.06.2018”;”13.06.2018”)» и «=ДАТАЗНАЧ(“24.06.2018”)-ДАТАЗНАЧ(“13.06.2018”)» вернут одинаковые значения.
- Если один из параметров функции представляет дату, записанную как текст, то он будет обработан функцией ДАТАЗНАЧ, которая вернет целочисленную дату.
- Если параметрами функции являются числа, значения которых находятся за пределами допустимых в коде времени Excel, будет сгенерирована ошибка #ЗНАЧ!.
- Если в функции ДНИ использованы параметры в виде данных, которые не могут быть представлены в виде дат в Excel, будет возвращена ошибка #ЗНАЧ!.
Примеры расчетов в Excel с использованием функций ДЕНЬ, ДЕНЬНЕД и ДНИ
В таблице содержится несколько дат, записанных в формате дат Excel, в которые сотрудникам некоторого предприятия должна быть выплачена заработная плата. В связи с принятыми изменениями в законодательстве начисление зарплат сотрудникам должно производиться в первых числах каждого месяца. Необходимо исправить даты с 15/07/2018 на 01/07/2018. Часть таблицы выглядит следующим образом:
Для решения используем следующую формулу: =B3-ДЕНЬ(B3)+1.
Аналогично заполним остальные колонки. В результате получим новый столбец с правильными датами выдачи зарплаты:
Определение рабочих и выходных дней по формуле ДЕНЬНЕД
Пример 2. Программный продукт считывает данные из таблицы Excel и использует их в своих расчетах. Одна из ячеек содержит данные в формате Дата, соответствующие дате получения посылки. Необходимо определить, соответствует ли указанная дата рабочему дню недели (с Пн по Пт) и вернуть логическое значение “true” или “false” (если выходной день) для дальнейшей обработки программой.
Исходная таблица имеет следующий вид:
Для определения дня недели будем использовать ДЕНЬНЕД, первым аргументом которой является данные из ячейки «Дата прибытия», а вторым – 2 (отсчет дней недели начинается с единицы, которая соответствует понедельнику). Для решения используем следующую функцию:
Как видно, все даты являются рабочими днями недели.
Зачем нужна функция ДНИ в Excel?
Пример 3. Общеизвестно, что ВОВ была начата 22 июня 1941 года и закончилась 9 мая 1945 года. Необходимо определить, сколько дней проходили военные действия.
Внесем исходные данные в таблицу:
Для расчета используем следующую формулу: =ДНИ(B3;A3).
Получаем следующий результат:
То есть, военные действия длились на протяжении 1417 дней.
Обратите внимание! Любая дата в Excel – это число дней, прошедших начиная от даты 01.01.1900г. Подробнее описано в статье: Как вычислить дату в Excel ? Поэтому можно свободно использовать такую простую формулу без функций: =B3-A3.
Как видно на рисунке результат аналогичен. Фактически функция ДНИ в 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 иногда ставится задача, чтобы после ввода конкретной даты в ячейку выводился день недели, который ей соответствует. Естественно, решить данную задачу посредством такого мощного табличного процессора, как Эксель, возможно, причем несколькими способами. Давайте посмотрим, какие варианты существуют для выполнения данной операции.
Существуют несколько способов отобразить день недели по введенной дате, начиная от форматирования ячеек и заканчивая применением функций. Давайте взглянем на все существующие варианты выполнения указанной операции в Экселе, чтобы пользователь мог выбрать самый лучший из них для конкретной ситуации.
Способ 1: применение форматирования
Прежде всего, посмотрим, как при помощи форматирования ячеек можно отобразить день недели по введенной дате. Данный вариант подразумевает преобразование даты в указанное значение, а не сохранение отображения обоих этих видов данных на листе.
В поле «Тип» окна форматирования вместо значения «ДДДД» можно также ввести выражение:
В этом случае на листе будет отображено сокращенное наименование дня недели.
Способ 2: использование функции ТЕКСТ
Но способ, который был представлен выше, предусматривает преобразование даты в день недели. Существует ли вариант, чтобы оба эти значения выводились на листе? То есть, если в одной ячейке мы вводим дату, то в другой должен выводиться день недели. Да, такой вариант существует. Его можно осуществить при помощи формулы ТЕКСТ . В этом случае нужное нам значение будет выводиться в указанную ячейку в текстовом формате.
Более того, если в ячейке изменить значение даты, то соответственно автоматически изменится и день недели. Таким образом, меняя дату можно узнавать на какой день недели она будет приходиться.
Способ 3: применение функции ДЕНЬНЕД
Имеется ещё один оператор, который может выводить день недели по заданной дате. Это функция ДЕНЬНЕД . Правда, она выводит не название дня недели, а его номер. При этом пользователь может устанавливать с какого дня (с воскресенья или с понедельника) нумерация будет отсчитываться.
Как и с предыдущей функцией, при изменении даты автоматически изменяется номер дня недели в ячейке, в которой установлен оператор.
Как видим, в Экселе существуют три основных варианта представления даты в виде дня недели. Все они относительно просты и не требуют от пользователя наличия каких-то специфических умений. Один из них заключается в применении специальных форматов, а два других для достижения указанных целей используют встроенные функции. Учитывая, что механизм и способ отображения данных в каждом описанном случае существенно отличаются, пользователь должен сам выбрать, какой из указанных вариантов в конкретной ситуации ему подходит более всего.
Когда возникает вопрос о получение информации о дне недели для указанной даты, бывает довольно непросто его определить.
Перелистывание сотен страниц календаря в поисках числа может изрядно утомить. Решить такой вопрос может с легкостью программное обеспечение Excel – буквально за несколько секунд. Как? Давайте рассмотрим способы определения дня недели по дате в Excel.
Программное обеспечение дает возможность несколькими способами определить день недели – это может быть и использование нескольких функций и форматирование поля ввода – здесь, как говориться, дело вкуса.
1. Определение дня недели с помощью форматирования данных.
Найти день недели можно всего за несколько кликов с помощью форматирования ячейки, куда введены исходные данные. К примеру, введем дату 9 апреля 2017 года и посмотрим, какой же это был день недели.
Чтобы изменить формат ячейки необходимо указать ячейку, в которую мы ввели данные и вызвать контекстное меню – «Формат ячеек».
В появившемся диалоговом окне формата ячеек выберем вкладку «Число» - «Все форматы» и введем опцию форматирование «ДДДД», после чего можно применить операцию.
После этого мы можем увидеть, что в ячейке появился искомый день недели, а в строке формул осталась введенная дата.
Опцию форматирования можно изменить, к примеру, если ввести «ДДД», то вместо полного наименования дня недели будет лишь его сокращение.
2. Определение дня недели с помощью использования функции «ТЕКСТ».
Первым способом нельзя сделать так, чтобы данные выводились в определенную ячейку или диапазон данных. Для этого следует использовать функцию «ТЕКСТ».
Например, введем ту же дату 9 апреля 2017 года и в соседней ячейке вызовем «Мастер функций» чтобы вставить наш оператор.
В мастере функций определяем категорию – выбираем «Текстовые» и непосредственно сам оператор «ТЕКСТ».
В окне ввода аргументов функции есть возможность ввести лишь два аргумента – «значение» и «формат».
В «Значение» вводим адрес, где введены исходные данные, в «Формат» - соответственно формат дня недели – «дддд» для полного отображения названия дня и «ддд» для сокращенного, как и в предыдущем способе.
После чего может применять выполнение функции.
После применение оператора «ТЕКСТ» мы видим, что в выбранной ячейки корректно отобразился день недели, который определился согласно исходным данным.
Корректность выполнения функции можно проверить путем смены исходных данных, к примеру, если мы поменяем число, то увидим, что и день недели так же изменился.
3. Определение дня недели с помощью функции «ДЕНЬНЕД».
Помимо предыдущих способов определения дня недели, в Excel есть специальный оператор, который может с легкостью справиться с поставленной задачей.
Функция «ДЕНЬНЕД», в отличии от прошлых способов, может вывести порядковый номер дня недели для любой искомой даты, достаточно лишь указать порядок нумерации – с понедельника или же с воскресенья.
Посмотрим на практике. Укажем дату, для которой необходимо определить день недели и вставим функцию с помощью «Мастера функций».
В диалоговом окне необходимо перейти в категорию «Дата и время» и в ней найти искомый оператор «ДЕНЬНЕД». Его функция выглядит следующим образом: «=ДЕНЬНЕД(дата_в_числовом_формате;[тип])».
Заполним аргументы функции «ДЕНЬНЕД» - для этого вместо даты укажем конкретную дату или адрес ячейки, где находится дата, а в «тип» укажем число от «1» до «3» с учетом следующего:
«1» - нумерация начинается с воскресенья с порядковым номером «1»;
«2» - нумерация начинается с понедельника с порядковым номером «1»;
«3» - нумерация начинается с понедельника с порядковым номером «0».
Как видим, в указанную ячейку был выведен номер «3», что согласно введенному аргументу «тип» - «2» - соответствует среде.
При изменении исходных данных функция корректно работает и правильно отображает порядковый номер дня недели.
Как можем увидеть, определение дня недели по введенной дате не составляет особых усилий и требует лишь несколько секунд, а сам способ определения остается на выбор пользователя.