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