В этом уроке Вы познакомитесь с различными полезными формулами сложения и вычитания дат в Excel. Например, Вы узнаете, как вычесть из одной даты другую, как прибавить к дате несколько дней, месяцев или лет, и т.д.
Если Вы уже проходили уроки по работе с датами в Excel (наши или любые другие уроки), то должны знать формулы для вычисления единиц времени, таких как дни, недели, месяцы, года.
Анализируя даты в каких-либо данных, часто нужно выполнять над этими датами арифметические действия. Эта статья объяснит некоторые формулы сложения и вычитания дат, которые могут оказаться для Вас полезными.
Как вычитать даты в Excel
Предположим, что у Вас в ячейках A2 и B2 содержатся даты, и нужно вычесть одну дату из другой, чтобы узнать, сколько между ними дней. Как часто бывает в Excel, этот результат можно получить несколькими способами.
Пример 1. Непосредственно вычитаем одну дату из другой
Думаю, Вы знаете, что Excel хранит даты в виде целых чисел, начиная с 1, что соответствует 1 января 1900 г. Поэтому Вы можете просто арифметически вычесть одно число из другого:
Пример 2. Вычитание дат с помощью функции РАЗНДАТ
Если предыдущая формула кажется Вам слишком простой, тот же результат можно получить более изощрённым способом с помощью функции РАЗНДАТ (DATEDIF).
РАЗНДАТ(A2;B2;"d")
=DATEDIF(A2,B2,"d")
Следующий рисунок показывает, что обе формулы возвращают одинаковый результат, за исключением ряда 4, где функция РАЗНДАТ (DATEDIF) возвращает ошибку #ЧИСЛО! (#NUM!). Посмотрим, почему это происходит.
Когда Вы вычитаете более позднюю дату (6 мая 2015) из более ранней (1 мая 2015), операция вычитания возвращает отрицательное число. Однако синтаксис функции РАЗНДАТ (DATEDIF) не позволяет, чтобы начальная дата была больше конечной даты и, естественно, возвращает ошибку.
Пример 3. Вычитаем дату из текущей даты
Чтобы вычесть определенную дату из текущей даты, Вы можете использовать любую из ранее описанных формул. Просто вместо сегодняшней даты используйте функцию СЕГОДНЯ (TODAY):
СЕГОДНЯ()-A2
=TODAY()-A2
РАЗНДАТ(A2;СЕГОДНЯ();"d")
=DATEDIF(A2,TODAY(),"d")
Как и в предыдущем примере, формулы отлично работают, когда текущая дата больше, чем вычитаемая. В противном случае функция РАЗНДАТ (DATEDIF) возвращает ошибку.
Пример 4. Вычитание дат с помощью функции ДАТА
Если Вы предпочитаете вводить даты непосредственно в формулу, указывайте их с помощью функции ДАТА (DATE), а затем вычитайте одну дату из другой.
Функция ДАТА имеет следующий синтаксис: ДАТА(год ; месяц ; день ) .
Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разность – 5 дней.
ДАТА(2015;5;20)-ДАТА(2015;5;15)
=DATE(2015,5,20)-DATE(2015,5,15)
Если нужно подсчитать количество месяцев или лет между двумя датами , тогда функция РАЗНДАТ (DATEDIF) – единственное возможное решение. В продолжении статьи Вы найдёте несколько примеров формул, подробно раскрывающих эту функцию.
Теперь, когда Вы знаете, как вычитать одну дату из другой, посмотрим, как можно прибавить или вычесть из даты определённое количество дней, месяцев или лет. Для этого существует несколько функций Excel. Что именно выбрать зависит от того, какие единицы времени требуется прибавить или вычесть.
Как прибавить (вычесть) дни к дате в Excel
Если у Вас есть дата в ячейке или список дат в столбце, Вы можете прибавить к ним (или отнять) определённое количество дней, используя соответствующую арифметическую операцию.
Пример 1. Прибавление дней к дате в Excel
Общая формула добавления определённого количества дней к дате выглядит так:
= Дата + N дней
Дату можно задать несколькими способами:
- Cсылкой на ячейку:
- Обращением к функции ДАТА
(DATE):
ДАТА(2015;5;6)+10
=DATE(2015,5,6)+10 - Обращением к другой функции. Например, чтобы прибавить несколько дней к текущей дате, используйте функцию СЕГОДНЯ
(TODAY):
СЕГОДНЯ()+10
=TODAY()+10
Следующий рисунок показывает действие этих формул. На момент написания текста текущей датой было 6 мая 2015 г.
Примечание: Результатом выполнения этих формул является целое число, представляющее дату. Чтобы показать его как дату, необходимо выбрать ячейку (или ячейки) и нажать Ctrl+1 . Откроется диалоговое окно Формат ячеек (Format Cells). На вкладке Число (Number) в списке числовых форматов выберите Дата (Date) и затем укажите нужный вам формат. Более подробное описание Вы найдёте в статье .
Пример 2. Вычитание дней из даты в Excel
Чтобы вычесть определённое количество дней из даты, Вам снова нужно использовать обычную арифметическую операцию. Единственное отличие от предыдущего примера – минус вместо плюса
= Дата - N дней
Вот несколько примеров формул:
A2-10
=ДАТА(2015;5;6)-10
=СЕГОДНЯ()-10
Как прибавить (вычесть) несколько недель к дате
Когда требуется прибавить (вычесть) несколько недель к определенной дате, Вы можете воспользоваться теми же формулами, что и раньше. Просто нужно умножить количество недель на 7:
- Прибавляем N недель
к дате в Excel:
A2 + N недель * 7
Например, чтобы прибавить 3 недели к дате в ячейке А2 , используйте следующую формулу:
- Вычитаем N недель
из даты в Excel:
А2 - N недель * 7
Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:
СЕГОДНЯ()-2*7
=TODAY()-2*7
Как прибавить (вычесть) несколько месяцев к дате в Excel
Чтобы прибавить (или отнять) определённое количество месяцев к дате, нужно использовать функцию ДАТА (DATE) или ДАТАМЕС (EDATE), как показано ниже.
Пример 1. Прибавляем несколько месяцев к дате с помощью функции ДАТА
Если список дат находится, например, в столбце A , укажите количество месяцев, которые Вы хотите добавить (положительное число) или отнять (отрицательное число) в какой-то ячейке, скажем, в C2 .
Введите в ячейку B2 указанную ниже формулу, щёлкните по выделенному углу ячейки и протащите его мышкой вниз по столбцу B до последней заполненной ячейки в столбце A . Формула из ячейки B2 будет скопирована во все ячейки столбца B .
ДАТА(ГОД(A2);МЕСЯЦ(A2)+$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))
Посмотрим, что делает эта формула. Логика формулы ясна и очевидна. Функция ДАТА(год ; месяц ; день ) получает следующие аргументы:
- Год из даты в ячейке A2 ;
- Месяц из даты в ячейке A2 + количество месяцев, указанное в ячейке C2 ;
- День из даты в ячейке A2 ;
Всё просто! Если Вы введёте в C2 отрицательное число, формула вычтет месяцы, а не прибавит.
Естественно, ничто не мешает Вам ввести минус прямо в формуле, чтобы вычесть месяцы:
ДАТА(ГОД(A2);МЕСЯЦ(A2)-$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))
И, конечно, вы можете указать число прибавляемых или вычитаемых месяцев непосредственно в формуле без ссылки на ячейку. Готовые формулы будут выглядеть примерно так:
- Прибавить месяцы
к дате:
ДАТА(ГОД(A2);МЕСЯЦ(A2)+2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+2,DAY(A2)) - Вычесть месяцы
из даты:
ДАТА(ГОД(A2);МЕСЯЦ(A2)-2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))
Пример 2. Прибавляем или вычитаем месяцы из даты с помощью функции ДАТАМЕС
В Excel предусмотрена специальная функция, которая возвращает дату, отстоящую от заданной на определенное количество месяцев назад или вперёд, – это функция ДАТАМЕС (EDATE). Она доступна в последних версиях Excel 2007, 2010, 2013 и новой Excel 2016.
При использовании ДАТАМЕС (EDATE) Вы указываете два следующих аргумента:
- Начальная дата – дата, от которой отсчитывается количество месяцев.
- Месяцы – количество месяцев, которые нужно прибавить (положительное число) или отнять (отрицательное число).
Эти формулы дадут тот же самый результат, что и формулы с функцией ДАТА (DATE) в предыдущем примере:
При использовании функции ДАТАМЕС (EDATE) начальную дату и количество месяцев можно указывать непосредственно в формуле. Даты можно задавать с помощью функции ДАТА (DATE) или как результат выполнения других формул. Например:
- Эта формула прибавляет 10 месяцев к 7 мая 2015 года
ДАТАМЕС(ДАТА(2015;5;7);10)
=EDATE(DATE(2015,5,7),10) - Эта формула отнимает 10 месяцев от сегодняшней даты
ДАТАМЕС(СЕГОДНЯ();-10)
=EDATE(TODAY(),-10)
Примечание: Функция ДАТАМЕС (EDATE) возвращает просто целое число. Чтобы представить его как дату, необходимо применить к ячейке формат даты. Как это сделать, указано в статье Как изменить формат даты в Excel .
Как прибавить (отнять) годы к дате в Excel
Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:
ДАТА(ГОД(дата
) + N лет
; МЕСЯЦ(дата
); ДЕНЬ(дата
))
= DATE(YEAR(дата
) + N лет
, MONTH(дата
), DAY(дата
))
На листе Excel, формулы могут выглядеть следующим образом:
- Прибавляем 5 лет
к дате, указанной в ячейке A2
:
ДАТА(ГОД(A2)+5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2)) - Вычитаем 5 лет
из даты, указанной в ячейке A2
:
ДАТА(ГОД(A2)-5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))
Чтобы получить универсальную формулу, Вы можете ввести количество лет в ячейку, а затем в формуле обратиться к этой ячейке. Положительное число позволит прибавить годы к дате, а отрицательное – вычесть.
Прибавление (вычитание) дней, месяцев и лет к дате
Если Вы внимательно читали два предыдущих примера, то, думаю, догадались, как добавить (или вычесть) сразу годы, месяцы и дни к дате в одной формуле. Да, с помощью старой доброй функции ДАТА (DATA)!
- Для прибавления
X лет, Y месяцев и Z дней:
ДАТА(ГОД(дата ) + X лет ; МЕСЯЦ(дата ) + Y месяцев ; ДЕНЬ(дата ) + Z дней )
= DATE(YEAR(дата ) + X лет , MONTH(дата ) + Y месяцев , DAY(дата ) + Z дней ) - Для вычитания
X лет, Y месяцев и Z дней:
ДАТА(ГОД(дата ) - X лет ; МЕСЯЦ(дата ) - Y месяцев ; ДЕНЬ(дата ) - Z дней )
= DATE(YEAR(дата ) - X лет , MONTH(дата ) - Y месяцев , DAY(дата ) - Z дней )
Например, следующая формула прибавляет 2 года и 3 месяца, и вычитает 15 дней из даты в ячейке A2 :
ДАТА(ГОД(A2)+2;МЕСЯЦ(A2)+3;ДЕНЬ(A2)-15)
=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)-15)
Применительно к нашему столбцу с датами, формула принимает следующий вид:
ДАТА(ГОД(A2)+$C$2;МЕСЯЦ(A2)+$D$2;ДЕНЬ(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)
Как складывать и вычитать время в Excel
В Microsoft Excel складывать и вычитать время можно с помощью функции ВРЕМЯ (TIME). Она позволяет Вам обращаться с единицами времени (часами, минутами и секундами) так же, как и с годами, месяцами и днями в функции ДАТА (DATE).
- Прибавить время
в Excel:
A2 + ВРЕМЯ(часы ; минуты ; секунды )
= A2 + TIME(часы , минуты , секунды ) - Вычесть время
в Excel:
A2 - ВРЕМЯ(часы ; минуты ; секунды )
= A2 - TIME(часы , минуты , секунды )где A2 – это ячейка с временем, которое нужно изменить.
Например, чтобы добавить 2 часа 30 минут и 15 секунд к времени в ячейке A2 надо использовать следующую формулу:
A2+ВРЕМЯ(2;30;15)
=A2+TIME(2,30,15)
A2+ВРЕМЯ(2;30;-15)
=A2+TIME(2,30,-15)
Также Вы можете ввести нужные значения в ячейки листа и ссылаться на них в формуле:
A2+ВРЕМЯ($C$2;$D$2;$E$2)
=A2+TIME($C$2,$D$2,$E$2)
Данная статья содержит несколько полезных формул, возвращающих конкретную дату.
Определение дня в году
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 до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 не нужна вообще.
День недели, а именно "понедельник", "вторник", "среда" и так далее, можно получить в Excel формулой по дате. То есть, когда есть дата и необходимо, чтобы рядом в ячейке появилось названия дня недели, можно это сделать формулой. Причем, в Интернете "гуляет" несколько формул для этого. Мы не будем рассматривать их, приведем только одну (по нашему мнению, наиболее оптимальную), итак
Формула день недели по дате
ТЕКСТ(B8;"ДДДД")
И небольшая модификация (если необходимо, чтобы день недели был написан с прописной буквы).
ПРОПНАЧ(ТЕКСТ(B8;"ДДДД"))
Другие формулы возвращающие день недели более громоздки, поэтому тут не рассматриваются, но есть одно "но". Приведенная формула не будет работать, если содержащий ее файл откроют в англоязычном (или любом другом, "не русском" Excel)
Мы кратко рассмотрели очередную формулу в рамках серии статей, которые мы назвали "1 формула в день". Если Вас интересует базовый уровень Excel, или Вам необходим "продвинутый", , чтобы получить помощь, обучение или консультацию. Сайт ExcelSkype – удаленное обучение Excel через Skype.
Для построения диаграмм или другого анализа, в табличном редакторе ногда требуется отображать день недели в Excel в ячейке с датой. Сделать это можно несколькими способами. Каждый из который опишем отдельно.
В этом случае при помощи встроенных в Excel функций введённые данные в формате даты преобразуются в день недели. В начале нужно ввести информацию в ячейку: . По клику правой кнопки мыши на ячейке отобразится контекстное меню, в котором нужно выбрать пункт «Формат». В появившемся окне на вкладке ЧИСЛО нужно выбрать из предложенных вариантов строку «все форматы».
Далее в правом окне понадобится ввести значение ДДДД . В поле «Образец» автоматически отобразится день недели, соответствующий введённой ранее дате. В редакторе содержимое строки изменилось, но, если навести на него курсор и нажать левую кнопку мыши, в строке формул отобразится только дата. За тип форматирования также можно принять строку ДДД , которая преобразует содержимое в сокращённое название дня недели (вторник – Вт, среда – Ср и т.д.)
Вариант №2: Применение функции ТЕКСТ, чтобы получить день недели в Excel
Редактор Excel в комбинации с необходимыми для форматирования функциями представляет из себя универсальный инструмент по работе c таблицами. Для преобразования даты следует ввести её в выбранную ячейку и выделить любую свободную область на листе. Левее поля «Строки формул» находится кнопка «Вставить функцию». После нажатия на неё левой кнопкой мыши появится окно настроек:
- Нужно выбрать из списка категорий значение «Текстовые».
- В перечне доступных функций выбрать вариант под названием ТЕКСТ и нажать OK, чтобы перейти к вводу аргументов.
- В параметрах отмечены два поля для установки: значение и формат. В качестве первого вводится адрес ячейки с датой. Это можно сделать вручную или поставить в нужное место курсор и клацнуть левой кнопкой мыши. После этого адрес должен автоматически отобразиться в строке «Значение».
- В поле «Формат» понадобится ввести выражения ДДДД или ДДД (для стандартного сокращения текста) и согласиться с аргументами.
Таким образом на листе появляются два значения одного типа, но в разном формате: дата и наименование дня недели, соответствующее ей. Этот метод позволяет использовать данные в совместимом виде. При изменении даты, преобразуется и значение дня недели. В строке формул можно вручную задавать функции, чтобы ускорить процесс редактирования: =ТЕКСТ(A1;«ДДДД »).
Вариант №3: Вставка функции ДЕНЬНЕД
Эта функция аналогична предыдущей, но преобразует текстовое значение в числовое. В этом случае у пользователя есть расширенные возможности.: установка старта отсчёта на один из двух выбранных дней – понедельника и воскресенья. Для ввода нужно пройти следующую процедуру:
- Выделить ячейку, в которую будет помещено преобразованное значение.
- Войти в настройки мастера функций и из каталога категорий выбрать строку «Дата и время».
- Из представленного списка понадобится функция ДЕНЬНЕД , аргументами которой являются «дата в числовом формате» и тип.
- В качестве первого нужно ввести необходимую дату ДД.ММ.ГГГГ . Для удобства можно указать адрес ячейки с соответствующим содержимым.
- Аргумент ТИП принимает три значения: 1, 2, 3. При указании единички, дни недели будут отсчитываться с воскресенья. Двоечка – это понедельник. Тройка – отсчёт начинается с понедельника, который будет приниматься счётчиком как «0» (то есть вторник при этом будет отображён в виде «1»).
Для ручного ввода в строке формул нужно написать: =ДЕНЬНЕД(A3;2). Не обязательно указывать параметр ТИП. В случае его отсутствия, редактор устанавливает значение по-умолчанию «1». Это актуально для Великобритании и других англоязычных государств, где неделя начинается именно с воскресенья. В странах СНГ понадобится самостоятельно вводить параметр при каждой активации функции.
Применение операторов обусловлено широкими функциональными возможностями, так как при изменении значения даты, содержимое ячейки с функцией также конвертируется. При работе с табличными базами данных это самый оптимальный метод преобразования форматов для получения значения день недели в Excel.
Поделитесь нашей статьей в ваших соцсетях: