В этом руководстве вы изучите различные полезные формулы для добавления и вычитания дат в Excel. Например, вы узнаете, как вычесть из даты другую, как добавить несколько дней, месяцев или лет к дате и так далее.
Если вы уже прошли уроки работы с датами в Excel (наши или любые другие), то вам следует знать формулы для расчета единиц времени, таких как дни, недели, месяцы, годы.
При синтаксическом анализе дат в какие-либо данные часто необходимо выполнять арифметические операции с этими датами. В этой статье будут объяснены некоторые полезные формулы для сложения и вычитания дат.
Предположим, у вас есть даты в ячейках A2 и B2, и вам нужно вычесть одну дату из другой, чтобы узнать, сколько дней между ними. Как это часто бывает в Excel, этого можно добиться несколькими способами.
Думаю, вы знаете, что Excel хранит даты как целые числа, начиная с 1, что соответствует 1 января 1900 года. Поэтому вы можете просто арифметически вычесть одно число из другого:
= SI2-LA2
Если приведенная выше формула кажется вам слишком простой, тот же результат можно получить более сложным способом, используя функцию РАЗНДАТ).
= ДАННЫЕ.DIFF (A2; B2; «d»)
= ДАННЫЕ.DIFF (A2; B2; «d»)
На следующем рисунке показано, что обе формулы возвращают один и тот же результат, за исключением строки 4, где DATEDIF возвращает ошибку #NUM !. (# ЧИСЛО!). Посмотрим, почему это происходит.
При вычитании более поздней даты (6 мая 2015 г.) из более ранней даты (1 мая 2015 г.) операция вычитания возвращает отрицательное число. Однако синтаксис функции РАЗНДАТ не позволяет дате начала быть больше даты окончания и, естественно, возвращает ошибку.
Чтобы вычесть конкретную дату из текущей даты, вы можете использовать любую из формул, описанных выше. Просто используйте функцию СЕГОДНЯ вместо сегодняшней даты):
= СЕГОДНЯ () — LA2
= СЕГОДНЯ () — LA2
или
= ДАТА ДАТА (A2; СЕГОДНЯ (); «d»)
= ДАННЫЕ.DIFF (A2; СЕГОДНЯ (); «d»)
Как и в предыдущем примере, формулы работают правильно, когда текущая дата больше даты, которую нужно вычесть. В противном случае функция РАЗНДАТ возвращает ошибку.
Если вы предпочитаете вставлять даты непосредственно в формулу, укажите их с помощью функции ДАТА, а затем вычтите одну дату из другой.
Функция ДАТА имеет следующий синтаксис: ДАТА (год; месяц; день).
Например, следующая формула вычитает 15 мая 2015 г из 20 мая 2015 г и возвращает разницу в 5 дней.
= ДАТА (2015; 5; 20) -ДАТА (2015; 5; 15)
= ДАТА (2015,5,20) -ДАТА (2015,5,15)
Подведем итоги. Если вам нужно вычесть даты в Excel и вы хотите определить количество дней между двумя датами, имеет смысл сделать это самым простым и очевидным способом: напрямую вычесть одну дату из другой.
Если вам нужно посчитать количество месяцев или лет между двумя датами, функция РАЗНДАТ — единственное возможное решение. В оставшейся части статьи вы найдете несколько примеров формул, подробно описывающих эту функцию.
Теперь, когда вы знаете, как вычесть одну дату из другой, давайте посмотрим, как можно добавить или вычесть определенное количество дней, месяцев или лет из даты. Для этого есть несколько функций Excel. Что именно выбрать, зависит от того, какие единицы времени вы хотите добавить или вычесть.
Если у вас есть дата в ячейке или список дат в столбце, вы можете добавить (или вычесть) определенное количество дней, используя соответствующую арифметическую операцию.
Общая формула добавления определенного количества дней к дате выглядит так:
= Дата + N дней
Дату можно установить несколькими способами:
= LA2 + 10
= ДАТА (2015; 5; 6) +10
= ДАТА (2015,5,6) +10
= СЕГОДНЯ () + 10
= СЕГОДНЯ () + 10
На следующем рисунке показано действие этих формул. На момент написания этой статьи текущая дата — 6 мая 2015 года.
Примечание. Результатом этих формул является целое число, представляющее дату. Чтобы отобразить его как дату, вам нужно выбрать ячейку (или ячейки) и нажать Ctrl + 1. Появится диалоговое окно «Формат ячеек». На вкладке Число в списке числовых форматов выберите Дата, а затем выберите нужный формат. Более подробное описание можно найти в статье Как изменить формат даты в Excel.
Чтобы вычесть определенное количество дней из даты, вам нужно снова использовать обычные арифметические операции. Единственное отличие от предыдущего примера — меньше вместо большего
= Дата — N дней
Вот несколько примеров формул:
= LA2-10
= ДАТА (2015; 5; 6) -10
= СЕГОДНЯ () — 10
Когда вам нужно прибавить (вычесть) несколько недель к определенной дате, вы можете использовать те же формулы, что и раньше. Вам просто нужно умножить количество недель на 7:
= A2 + N недель * 7
Например, чтобы добавить 3 недели к дате в ячейке A2, используйте следующую формулу:
= A2 + 3 * 7
= A2 — N недель * 7
Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:
= СЕГОДНЯ () — 2 * 7
= СЕГОДНЯ () — 2 * 7
Чтобы добавить (или вычесть) определенное количество месяцев к дате, используйте функцию ДАТА или ДАТА, как показано ниже.
Если список дат находится, например, в столбце A, укажите количество месяцев, которое вы хотите добавить (положительное число) или вычесть (отрицательное число) в ячейке, например C2.
Введите приведенную ниже формулу в ячейку B2, щелкните выделенный угол ячейки и перетащите его вниз в столбце B до последней заполненной ячейки в столбце A. Формула из ячейки B2 будет скопирована во все ячейки в столбце B.
= ДАТА (ГОД (A2); МЕСЯЦ (A2) + $ C $ 2, ДЕНЬ (A2))
= ДАТА (ГОД (A2); МЕСЯЦ (A2) + $ C $ 2, ДЕНЬ (A2))
Посмотрим, что делает эта формула. Логика формулы ясна и очевидна. Функция ДАТА (год; месяц; день) принимает следующие аргументы:
это так просто! Если вы введете отрицательное число в C2, формула будет вычитать месяцы, а не добавлять.
Конечно, ничто не мешает ввести в формулу вычитания месяцев знак минус:
= ДАТА (ГОД (A2); МЕСЯЦ (A2) — $ C $ 2, ДЕНЬ (A2))
= ДАТА (ГОД (A2); МЕСЯЦ (A2) — $ C $ 2, ДЕНЬ (A2))
И, конечно же, вы можете указать количество месяцев для добавления или вычитания непосредственно в формуле без ссылки на ячейку. Готовые формулы будут выглядеть так:
= ДАТА (ГОД (A2); МЕСЯЦ (A2) +2; ДЕНЬ (A2))
= ДАТА (ГОД (A2); МЕСЯЦ (A2) + 2; ДЕНЬ (A2))
= ДАТА (ГОД (A2); МЕСЯЦ (A2) -2; ДЕНЬ (A2))
= ДАТА (ГОД (A2); МЕСЯЦ (A2) -2; ДЕНЬ (A2))
Excel предоставляет специальную функцию, которая возвращает дату, которая на определенное количество месяцев назад или вперед от заданной даты — это функция ДАТАФИКАЦИЯ. Он доступен в последних версиях Excel 2007, 2010, 2013 и в новом Excel 2016.
При использовании EDATE предоставляются следующие два аргумента:
Эти формулы дадут тот же результат, что и формулы DATE в предыдущем примере:
При использовании функции EDATE дату начала и количество месяцев можно указать непосредственно в формуле. Даты могут быть установлены с помощью функции ДАТА или в результате других формул. Например:
= ДАТА (ДАТА (2015; 5; 7); 10)
= ДАТА (ДАТА (2015,5,7); 10)
= ДАТА EEC (СЕГОДНЯ (); — 10)
= ДАТА (СЕГОДНЯ (); — 10)
Примечание. Функция EDATE возвращает только целое число. Чтобы представить его как дату, вам необходимо применить к ячейке формат даты. Как это сделать, указано в статье Как изменить формат даты в Excel.
Добавление лет к датам в Excel аналогично добавлению месяцев. Вам нужно снова использовать функцию ДАТА, но на этот раз вам нужно указать количество лет, которое вы хотите добавить:
= ДАТА (ГОД (дата) + N лет, МЕСЯЦ (дата), ДЕНЬ (дата))
= ДАТА (ГОД (дата) + N лет, МЕСЯЦ (дата), ДЕНЬ (дата))
На листе Excel формулы могут выглядеть так:
= ДАТА (ГОД (A2) +5; МЕСЯЦ (A2), ДЕНЬ (A2))
= ДАТА (ГОД (A2) + 5; МЕСЯЦ (A2), ДЕНЬ (A2))
= ДАТА (ГОД (A2) -5; МЕСЯЦ (A2), ДЕНЬ (A2))
= ДАТА (ГОД (A2) -5; МЕСЯЦ (A2), ДЕНЬ (A2))
Чтобы получить универсальную формулу, вы можете ввести количество лет в ячейку, а затем обратиться к этой ячейке в формуле. Положительное число прибавит годы к дате, а отрицательное число вычтет его.
Если вы внимательно прочитали два предыдущих примера, я думаю, вы догадались, как добавить (или вычесть) годы, месяцы и дни к дате в формуле одновременно. Да, со старой доброй функцией ДАННЫЕ)!
= ДАТА (ГОД (дата) + X лет; МЕСЯЦ (дата) + Y месяцев; ДЕНЬ (дата) + Z дней)
= ДАТА (ГОД (дата) + X лет, МЕСЯЦ (дата) + Y месяцев, ДЕНЬ (дата) + Z дней)
= ДАТА (ГОД (дата) — X лет; МЕСЯЦ (дата) — Y месяцев; ДЕНЬ (дата) — Z дней)
= ДАТА (ГОД (дата) — X лет, МЕСЯЦ (дата) — Y месяцев, ДЕНЬ (дата) — Z дней)
Например, следующая формула добавляет 2 года и 3 месяца и вычитает 15 дней из даты в ячейке A2:
= ДАТА (ГОД (A2) +2; МЕСЯЦ (A2) +3, ДЕНЬ (A2) -15)
= ДАТА (ГОД (A2) + 2; МЕСЯЦ (A2) + 3; ДЕНЬ (A2) -15)
Для нашего столбца даты формула выглядит так:
= ДАТА (ГОД (A2) + $ C $ 2, МЕСЯЦ (A2) + $ D $ 2, ДЕНЬ (A2) + $ E $ 2)
= ДАТА (ГОД (A2) + $ C $ 2, МЕСЯЦ (A2) + $ D $ 2, ДЕНЬ (A2) + $ E $ 2)
В Microsoft Excel вы можете складывать и вычитать время с помощью функции ВРЕМЯ. Используется для обработки единиц времени (часов, минут и секунд) как лет, месяцев и дней в функции ДАТА).
= A2 + ВРЕМЯ (часы; минуты; секунды)
= A2 + ВРЕМЯ (часы, минуты, секунды)
= A2 — ВРЕМЯ (часы; минуты; секунды)
= A2 — ВРЕМЯ (часы, минуты, секунды)
где A2 — ячейка с изменяемым временем.
Например, чтобы добавить 2 часа 30 минут и 15 секунд в час в ячейку A2, вы должны использовать следующую формулу:
= A2 + ВРЕМЯ (2; 30; 15)
= A2 + TEMPO (2,30,15)
Если вы хотите складывать и вычитать единицы времени в формуле, просто добавьте знак минус «-» к нужным значениям. Следующая формула добавляет 2 часа 30 минут ко времени в ячейке A2 и вычитает 15 секунд:
= A2 + TEMPO (2; 30; -15)
= A2 + TEMPO (2.30; -15)
Вы также можете ввести желаемые значения в ячейки рабочего листа и сослаться на них в формуле:
= A2 + ВРЕМЯ ($ C $ 2, $ D $ 2, $ E $ 2)
= A2 + ВРЕМЯ ($ C $ 2, $ D $ 2, $ E $ 2)
Если исходная ячейка содержит дату и время, приведенная выше формула также отлично работает:
Вот как вы можете складывать и вычитать даты в Excel. Надеюсь, вы узнали пару полезных функций сегодня. В любом случае спасибо за внимание и надеюсь увидеть вас снова!
