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