Как отнять даты в excel

Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel

В этом уроке Вы познакомитесь с различными полезными формулами сложения и вычитания дат в Excel. Например, Вы узнаете, как вычесть из одной даты другую, как прибавить к дате несколько дней, месяцев или лет, и т.д.

Если Вы уже проходили уроки по работе с датами в Excel (наши или любые другие уроки), то должны знать формулы для вычисления единиц времени, таких как дни, недели, месяцы, года.

Анализируя даты в каких-либо данных, часто нужно выполнять над этими датами арифметические действия. Эта статья объяснит некоторые формулы сложения и вычитания дат, которые могут оказаться для Вас полезными.

Как вычитать даты в Excel

Предположим, что у Вас в ячейках A2 и B2 содержатся даты, и нужно вычесть одну дату из другой, чтобы узнать, сколько между ними дней. Как часто бывает в Excel, этот результат можно получить несколькими способами.

Пример 1. Непосредственно вычитаем одну дату из другой

Думаю, Вы знаете, что Excel хранит даты в виде целых чисел, начиная с 1, что соответствует 1 января 1900 г. Поэтому Вы можете просто арифметически вычесть одно число из другого:

Пример 2. Вычитание дат с помощью функции РАЗНДАТ

Если предыдущая формула кажется Вам слишком простой, тот же результат можно получить более изощрённым способом с помощью функции РАЗНДАТ (DATEDIF).

Следующий рисунок показывает, что обе формулы возвращают одинаковый результат, за исключением ряда 4, где функция РАЗНДАТ (DATEDIF) возвращает ошибку #ЧИСЛО! (#NUM!). Посмотрим, почему это происходит.

Когда Вы вычитаете более позднюю дату (6 мая 2015) из более ранней (1 мая 2015), операция вычитания возвращает отрицательное число. Однако синтаксис функции РАЗНДАТ (DATEDIF) не позволяет, чтобы начальная дата была больше конечной даты и, естественно, возвращает ошибку.

Пример 3. Вычитаем дату из текущей даты

Чтобы вычесть определенную дату из текущей даты, Вы можете использовать любую из ранее описанных формул. Просто вместо сегодняшней даты используйте функцию СЕГОДНЯ (TODAY):

Как и в предыдущем примере, формулы отлично работают, когда текущая дата больше, чем вычитаемая. В противном случае функция РАЗНДАТ (DATEDIF) возвращает ошибку.

Пример 4. Вычитание дат с помощью функции ДАТА

Если Вы предпочитаете вводить даты непосредственно в формулу, указывайте их с помощью функции ДАТА (DATE), а затем вычитайте одну дату из другой.

Функция ДАТА имеет следующий синтаксис: ДАТА(год; месяц; день).

Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разность – 5 дней.

Подводим итог. Если нужно вычитать даты в Excel, и Вы хотите определить количество дней между двумя датами, имеет смысл делать это самым простым и очевидным способом – напрямую вычитать одну дату из другой.

Если нужно подсчитать количество месяцев или лет между двумя датами, тогда функция РАЗНДАТ (DATEDIF) – единственное возможное решение. В продолжении статьи Вы найдёте несколько примеров формул, подробно раскрывающих эту функцию.

Теперь, когда Вы знаете, как вычитать одну дату из другой, посмотрим, как можно прибавить или вычесть из даты определённое количество дней, месяцев или лет. Для этого существует несколько функций Excel. Что именно выбрать зависит от того, какие единицы времени требуется прибавить или вычесть.

Как прибавить (вычесть) дни к дате в Excel

Если у Вас есть дата в ячейке или список дат в столбце, Вы можете прибавить к ним (или отнять) определённое количество дней, используя соответствующую арифметическую операцию.

Пример 1. Прибавление дней к дате в Excel

Общая формула добавления определённого количества дней к дате выглядит так:

Дату можно задать несколькими способами:

Следующий рисунок показывает действие этих формул. На момент написания текста текущей датой было 6 мая 2015 г.

Пример 2. Вычитание дней из даты в Excel

Чтобы вычесть определённое количество дней из даты, Вам снова нужно использовать обычную арифметическую операцию. Единственное отличие от предыдущего примера – минус вместо плюса 🙂

Вот несколько примеров формул:

=A2-10
=ДАТА(2015;5;6)-10
=СЕГОДНЯ()-10

Как прибавить (вычесть) несколько недель к дате

Когда требуется прибавить (вычесть) несколько недель к определенной дате, Вы можете воспользоваться теми же формулами, что и раньше. Просто нужно умножить количество недель на 7:

Например, чтобы прибавить 3 недели к дате в ячейке А2, используйте следующую формулу:

Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:

Как прибавить (вычесть) несколько месяцев к дате в Excel

Чтобы прибавить (или отнять) определённое количество месяцев к дате, нужно использовать функцию ДАТА (DATE) или ДАТАМЕС (EDATE), как показано ниже.

Пример 1. Прибавляем несколько месяцев к дате с помощью функции ДАТА

Если список дат находится, например, в столбце A, укажите количество месяцев, которые Вы хотите добавить (положительное число) или отнять (отрицательное число) в какой-то ячейке, скажем, в C2.

Введите в ячейку B2 указанную ниже формулу, щёлкните по выделенному углу ячейки и протащите его мышкой вниз по столбцу B до последней заполненной ячейки в столбце A. Формула из ячейки B2 будет скопирована во все ячейки столбца B.

Посмотрим, что делает эта формула. Логика формулы ясна и очевидна. Функция ДАТА(год; месяц; день) получает следующие аргументы:

Всё просто! Если Вы введёте в C2 отрицательное число, формула вычтет месяцы, а не прибавит.

Естественно, ничто не мешает Вам ввести минус прямо в формуле, чтобы вычесть месяцы:

И, конечно, вы можете указать число прибавляемых или вычитаемых месяцев непосредственно в формуле без ссылки на ячейку. Готовые формулы будут выглядеть примерно так:

Пример 2. Прибавляем или вычитаем месяцы из даты с помощью функции ДАТАМЕС

В Excel предусмотрена специальная функция, которая возвращает дату, отстоящую от заданной на определенное количество месяцев назад или вперёд, – это функция ДАТАМЕС (EDATE). Она доступна в последних версиях Excel 2007, 2010, 2013 и новой Excel 2016.

При использовании ДАТАМЕС (EDATE) Вы указываете два следующих аргумента:

Эти формулы дадут тот же самый результат, что и формулы с функцией ДАТА (DATE) в предыдущем примере:

При использовании функции ДАТАМЕС (EDATE) начальную дату и количество месяцев можно указывать непосредственно в формуле. Даты можно задавать с помощью функции ДАТА (DATE) или как результат выполнения других формул. Например:

Как прибавить (отнять) годы к дате в Excel

Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:

На листе Excel, формулы могут выглядеть следующим образом:

Чтобы получить универсальную формулу, Вы можете ввести количество лет в ячейку, а затем в формуле обратиться к этой ячейке. Положительное число позволит прибавить годы к дате, а отрицательное – вычесть.

Прибавление (вычитание) дней, месяцев и лет к дате

Если Вы внимательно читали два предыдущих примера, то, думаю, догадались, как добавить (или вычесть) сразу годы, месяцы и дни к дате в одной формуле. Да, с помощью старой доброй функции ДАТА (DATA)!

Например, следующая формула прибавляет 2 года и 3 месяца, и вычитает 15 дней из даты в ячейке A2:

Применительно к нашему столбцу с датами, формула принимает следующий вид:

Как складывать и вычитать время в Excel

В Microsoft Excel складывать и вычитать время можно с помощью функции ВРЕМЯ (TIME). Она позволяет Вам обращаться с единицами времени (часами, минутами и секундами) так же, как и с годами, месяцами и днями в функции ДАТА (DATE).

где A2 – это ячейка с временем, которое нужно изменить.

Например, чтобы добавить 2 часа 30 минут и 15 секунд к времени в ячейке A2 надо использовать следующую формулу:

Если вы хотите прибавлять и вычитать единицы времени в одной формуле, просто добавьте к нужным значениям знак минус «-«. Следующая формула прибавляет к времени в ячейке A2 2 часа 30 минут и вычитает 15 секунд:

Также Вы можете ввести нужные значения в ячейки листа и ссылаться на них в формуле:

Если исходная ячейка содержит и дату, и время, предыдущая формула также прекрасно работает:

Вот так в Excel можно складывать и вычитать даты. Надеюсь, что сегодня Вы узнали пару полезных функций. В любом случае благодарю Вас за внимание и надеюсь увидеться снова!

Источник

Сложение и вычитание дней, недель, месяцев и лет в Excel

Как отнимать одну дату от другой

Вариант 1. Просто вычтите одну дату из другой

Вариант 3. Вычтите из настоящей даты другую.

При подобных условиях мы можем еще больше упростить себе задачу. Нам следует воспользоваться вышеупомянутыми методиками, но только лишь добавив функцию TODAY () вместо даты 1. Вот как это будет выглядеть:

= DATEDIF (A2, TODAY (), «d»)

Давайте посмотрим на примере. Следующая формула вычитает 15.05.2015 из 20.05.2015 и показывает разницу — 5 дней. Все просто и понятно.

= DATE (2015, 5, 20) — DATE (2015, 5, 15)

Как отнимать или прибавлять дни

Предположим, вы ведете документацию по какому-либо проекту, и у вас в ячейке или столбце находятся данные с датами. Мы всегда можем автоматически прибавить или отнять определенное количество дней, а не прописывать новые данные вручную. Эти методики значительно сэкономят нам время и силы.

Вариант 1. Прибавить дни к определенной дате

Дата + количество дней

Есть несколько способов, как указать дату, а именно:

Давайте посмотрим на примеры на следующем снимке экрана. Вернемся немного в прошлое, и за текущий день будем считать 05.06.2015:

Комментарий : После сохранения числа формулы зажмите Ctrl + 1 — открывается окно « Формат ячеек ». Затем выберите « Дата » из списка « Категория » и определите форму даты.

Вариант 2. Вычтите дни из конкретной даты

Дата — количество дней

Как отнимать или прибавлять недели

При подобных условиях вы должны применить те же действия, которые выполняются в днях, только нужно умножить число недель на 7. Все предельно просто.

ячейка + число недель * 7

ячейка — число недель * 7

Как отнимать / прибавлять месяцы

Давайте посмотрим на пример. У нас есть столбец A со списком дат. Далее в каждой ячейке, к примеру, C2, укажите число месяцев со знаком плюс (когда мы прибавляем), со знаком минус (когда мы вычитаем).

Полученная формула записываем в ячейке B2. Перетащите угол ячейки в направлении вниз. В итоге формула будет показана во всех ячейках :

Теперь посмотрим, как эта функция работает. Тут все просто. Значения функции DATE :

Конечно, знак минус допускается записать и в формулу. Это очередной способ для тех, кто не любит заниматься копированием ссылок.

= DATE (ГОД (дата), МЕСЯЦ (дата) + число месяцев, ДЕНЬ (дата))

Два компонента прописываются в формулах EDATE (start_date, month):

Start_Date: исходная дата, с которой необходимо вести отсчет.

Мonth: количество месяцев, которые будут добавлены (плюсовое значение) или вычтены (минусовое значение).

В любом случае исход будет одинаковым:

И снова способ для любителей прописывать все данные в формуле. Давайте проанализируем варианты :

= EDATE (ДАТА (2015,5,7), 10)

10 месяцев добавляем к 07.05.2015.

10 месяцев отнимаем от текущей даты.

Как отнимать / прибавлять годы

В этом случае ситуация похожа на работу с месяцами. Снова воспользуемся функцией DATE, только теперь вам нужно задать количество необходимых лет:

Дата) + количество лет, МЕСЯЦ (дата), ДЕНЬ (дата))

= DATE (ГОД (A2) + 5, МЕСЯЦ (A2), ДЕНЬ (A2))

К данным А2 добавляется 5 лет.

= DATE (ГОД (A2) — 5, МЕСЯЦ (A2), ДЕНЬ (A2))

От данных A2 отнимется 5 лет.

Если мы хотим, чтобы наша формула была универсальна, мы должны указать число лет со знаком плюс для прибавления или число лет со знаком минус для вычитания в ячейке, далее эту ячейку указать в DATE:

Как отнимать либо прибавлять комбинации лет, месяцев и дней

И снова отдаем предпочтение нашей любимой функции DATE 🙂

DATE (ГОД (дата) + X лет, МЕСЯЦ (дата) + Y месяцев, ДЕНЬ (дата) + Z дней)

DATE (ГОД (дата) — X лет, МЕСЯЦ (дата) — Y месяцев, ДЕНЬ (дата) — Z дней)

Давайте возьмем конкретный пример. С

В следующей формуле добавляем 2 года, 3 месяца, а также вычитаем 15 дней с конкретной даты, указанной в ячейке A2:

= DATE (ГОД (A2) + 2, МЕСЯЦ (A2) + 3, ДЕНЬ (A2) — 15)

Что касается нашего столбца дат, формула выглядит следующим образом:

Как отнимать либо прибавлять время

Ячейки + TIME (часы, минуты, секунды)

Ячейка — TIME (часы, минуты, секунды)

А сейчас разберем пример со временем. Наша задача – провемти следующие математические операции: ко времени внутри A2 прибавить 2 часа, 30 мин. и 15 сек. Вот, что мы делаем:

Таким образом, с помощью незамысловатых действий нам удалось ко времени внутри А2 приплюсовать 2 часа и 30 мин, а вычесть 15 сек.

Мастер формул даты и времени – очень быстрый способ работы с арифметическими действиями

Затем вы должны перейти на вкладку Инструменты Ablebits и нажать кнопку « Мастер даты и времени »:

После открытия окна « Мастер даты и времени » перейдите на соответствующую вкладку, введите необходимые параметры в формулу и нажмите « Вставить формулу ».

Давайте посмотрим на пример того, как работает этот инструмент. Предположим, мы должны добавить несколько месяцев к дате, указанной в ячейке A2. Для этого перейдем на вкладку « Добавить », введем А2 в разделе « Введите дату » (или выберите ячейку непосредственно в электронной таблице Excel) и введите необходимое количество месяцев.

Этот инструмент создаст формулу и предварительный просмотр в ячейке. Также отобразиться рассчитанная в соответствии с формулой дата :

Когда вы будете полностью удовлетворены полученными данными, нажмите кнопку « Вставить формулу ». Когда формула добавлена, ее можно скопировать во все необходимые ячейки:

При нажатии кнопки « Вставить формулу » внутри A2 появляется следующее:

= DATE (ГОД (А2) — D2, МЕСЯЦ (А2) — E2, ДЕНЬ (А2) — G2 — F2 * 7)

Результат:

Источник

Как вычесть даты в Excel

Вычислить разницу между датами в Excel легко, но это может стать сложным в зависимости от того, как вы хотите рассчитать определенные значения. Например, если вы хотите узнать количество месяцев между 5/5/2016 и 15.01.2016, должен ли ответ быть 0 или 1? Некоторые люди могут сказать 0, потому что между датами не полный месяц, а другие могут сказать 1, потому что это другой месяц.

В этой статье я покажу вам, как рассчитать разницу между двумя датами, чтобы найти количество дней, месяцев и лет с различными формулами в зависимости от ваших предпочтений.

Дни между двумя датами

Самый простой расчет, который мы можем сделать, это получить количество дней между двумя датами. Преимущество расчета дней состоит в том, что на самом деле есть только один способ вычисления значения, поэтому вам не нужно беспокоиться о разных формулах, дающих разные ответы.

В моем примере выше у меня есть две даты, хранящиеся в ячейках A2 и A3. Справа вы видите разницу между этими двумя датами — 802 дня. В Excel всегда есть несколько способов вычислить одно и то же значение, и это то, что я сделал здесь. Давайте посмотрим на формулы:

Первая формула представляет собой простое вычитание двух дат, A3 — A2. Excel знает, что это дата, и просто вычисляет количество дней между этими двумя датами. Легко и очень просто. Кроме того, вы также можете использовать ДНИ функция.

Эта функция принимает два аргумента: дату окончания и дату начала. Если вы переключите даты в формуле, вы просто получите отрицательное число. Наконец, вы можете использовать функцию под названием DATEDIF, которая включена в Excel из Lotus 1-2-3 дня, но не является официально поддерживаемой формулой в Excel.

Когда вы наберете формулу, вы увидите, что Excel не дает никаких предложений для полей и т. Д. К счастью, вы можете увидеть синтаксис и все поддерживаемые аргументы для Функция DATEDIF Вот.

В качестве дополнительного бонуса, что если вы хотите рассчитать количество дней недели между двумя датами? Это достаточно просто, потому что у нас есть встроенная формула:

Подсчет количества дней и дней недели достаточно прост, поэтому давайте поговорим о месяцах.

Месяцы между двумя датами

Самым хитрым расчетом является количество месяцев из-за того, как вы можете округлять или увеличивать, в зависимости от того, является ли этот месяц полным или неполным. Теперь в Excel есть функция MONTHS, но она очень ограничена, поскольку при расчете разницы будет учитываться только месяц, а не год. Это означает, что это полезно только для расчета разницы между двумя месяцами в одном и том же году.

Поскольку это бессмысленно, давайте посмотрим на некоторые формулы, которые дадут нам правильный ответ. Microsoft предоставила эти Вот, но если вам лень переходить по ссылке, я предоставил и их ниже.

Теперь это две довольно длинные и сложные формулы, и вам действительно не нужно понимать, что происходит. Вот формулы в Excel:

Обратите внимание, что вы должны отредактировать формулу округления вниз в самой ячейке, потому что по какой-то странной причине вся формула не отображается на панели формул. Чтобы увидеть формулу в самой ячейке, нажмите на Формулы вкладка, а затем нажмите Показать формулы,

Итак, каков окончательный результат двух формул в нашем текущем примере? Давайте посмотрим ниже:

Округление дает мне 27 месяцев, а округление дает мне 26 месяцев, что точно, в зависимости от того, как вы хотите на это смотреть. Наконец, вы также можете использовать функцию DATEDIF, но она рассчитывает только полные месяцы, поэтому в нашем случае ответ будет 26.

Годы между двумя датами

Как и в месяцах, годы также могут быть рассчитаны в зависимости от того, хотите ли вы считать 1 год полными 365 днями или учитывается ли изменение года. Итак, давайте возьмем наш пример, где я использовал две разные формулы для расчета количества лет:

Одна формула использует DATEDIF, а другая — функцию YEAR. Поскольку разница в количестве дней составляет всего 802, DATEDIF показывает 2 года, тогда как функция YEAR показывает 3 года.

Опять же, это вопрос предпочтений и зависит от того, что именно вы пытаетесь рассчитать. Полезно знать оба метода, чтобы можно было решать различные задачи с помощью разных формул.

Пока вы тщательно следите за тем, какие формулы вы используете, довольно легко точно рассчитать, что вы ищете. Помимо перечисленных мною функций, имеется гораздо больше функций даты, поэтому вы можете проверить их также на Служба поддержки сайт. Если у вас есть какие-либо вопросы, не стесняйтесь комментировать. Наслаждайтесь!

Источник

Вычитание дат

Вы можете легко найти разницу двух дат, определить формат результата и произвести вычисление на основе списка дат. Например, можно быстро вычесть одну дату из другой или вычислить длительность задач в расписании проекта.

Чтобы научиться вычислять даты в расписании проекта, используйте предоставленные образцы данных и описанные ниже процедуры.

Копирование примера данных

Скопируйте данные из примера ниже в ячейку A1 пустого листа.

Создание плана проекта

Нажмите клавиши +C.

Выделите на листе ячейку A1, а затем нажмите клавиши +V.

Вычисление разницы в днях

Определение количества дней между двумя датами

Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность».

Введите = C2-B2 и нажмите клавишу RETURN.

В Excel возвращается результат в виде количества дней между двумя датами (104).

Выберите ячейку D2.

Перетащите маркер заполнения , чтобы скопировать формулу в оставшиеся строки.

Excel автоматически изменяет ссылки на ячейки, чтобы включить правильные значения для каждой строки.

Вычисление количества рабочих дней между двумя датами

Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность».

Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.

Выполните одно из указанных ниже действий.

В Excel для Mac 2011 на вкладке «Формулы» в области «Функция»щелкните построитель формул.

В Excel 2016 для Mac на вкладке «Формулы» нажмите кнопку «Вставить функцию».

В поле поиска построитель формул введите «NETWORKDAYS».

В появившемся списке дважды щелкните пункт ЧИСТРАБДНИ.

В разделе Аргументы щелкните поле Нач_дата и выберите ячейку B2 на листе (08.06.2011).

В разделе Аргументы щелкните поле Кон_дата и выберите ячейку C2 на листе (20.09.2011).

Не заполняйте поле Праздники.

Нажмите клавишу RETURN.

Результатом является 75 рабочих дней (без учета праздников).

Выберите ячейку D2.

Перетащите маркер заполнения , чтобы скопировать формулу в оставшиеся строки.

Excel автоматически изменяет ссылки на ячейки, чтобы включить правильные значения для каждой строки.

Совет: Подробнее об этих формулах, в том числе о том, как учитывать в них праздники, читайте в статьях Функция ЧИСТРАБДНИ и Функция ЧИСТРАБДНИ.МЕЖД.

Вычисление количества месяцев между двумя датами одного и того же года

В Excel месяцы представлены в виде значений от 1 до 12, что позволяет легко вычесть более раннюю дату из более поздней, если они приходятся на один год. Найдите значение месяца для каждой из дат с помощью функции МЕСЯЦ, а затем вычислите разницу между значениями.

Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность».

Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.

В ячейке D2 введите =МЕСЯЦ(C2)-МЕСЯЦ(B2), а затем нажмите клавишу RETURN.

Вычисление количества месяцев между двумя датами разных лет

Чтобы определить количество месяцев между датами, которые приходятся на разные года, можно использовать формулу, которая определяет число лет между двумя датами, преобразует его в число месяцев и учитывает дополнительные месяцы, чтобы получить точный результат. Для вычисления используются функции МЕСЯЦ и ГОД.

Выберите ячейку D3.

Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.

В ячейке D3 введите =(ГОД(C3)-ГОД(B3))*12+МЕСЯЦ(C3)-МЕСЯЦ(B3), а затем нажмите клавишу RETURN.

Вычисление количества лет между двумя датами

В Excel года представлены в виде значений. Найдите значение года для каждой из дат с помощью функции ГОД, а затем вычислите разницу между значениями.

Выберите ячейку D4.

Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.

В ячейке D4 введите =ГОД(C4)-ГОД(B4), а затем нажмите клавишу RETURN.

Источник