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

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

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

• Как отнимать одну дату от другой.
• Как отнимать либо прибавлять дни к определенной дате.
• Как отнимать либо прибавлять недели к определенной дате.
• Как прибавлять месяцы к сегодняшней дате.
• Как прибавлять годы к сегодняшней дате.
• Как отнимать либо складывать комбинации лет, месяцев и дней.
• Как отнимать либо складывать время.
• Мастер формул даты и времени – быстрый способ работы с арифметическими операциями.

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

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

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

Данные дат программы сохраняются в формате неповторяющихся чисел, отсчет идет с 01.01.1900. Соответственно вы работаете с числами, т.е. это обычные математические действия, которые работают без проблем:

= B2-A2

Вариант 2. DATEDIF

DATEDIF, который дает точно такой же результат:

= DATEDIF (A2, B2, «d»)

В любой ситуации можно разными способами прийти к одной позиции. Работа в Excel — также не исключение. Здесь тоже возможно различными путями прийти к одному конечному результату. Так, например, на следующем снимке экрана продемонстрирован одинаковый результат, не считая строку 4 — потому что DATEDIF отображает ошибку #NUM. Итак, с чем же это связано? Для получения ответа на этот вопрос, давайте обратимся к следующему примеру.

Если от  06.05.2015 отнять 01.05.2015, т.е. от меньшей даты отнять большую, то мы получим отрицательное число (-5). Функция DATEDIF, следовательно, выдает ошибку, так не предусматривает отрицательного исхода.

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

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

= TODAY () — А2

либо

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

Все мы помним задачки начальной школы наподобие: У Пети было 5 яблок, 3 яблока он дал Маше. Сколько у Пети осталось яблок? Очевидно, что результат положителен.  Такого же принципа следует придерживаться при работе с функцией DATEDIF. Самое главное — принимать во внимание большие и меньшие показатели дат, чтобы результат, как и в элементарной задачке, был положительным, иначе DATEDIF выдаст ошибку:

Вариант 4. DATE 

Многим не нравится постоянно использовать ссылки и куда-то их вставлять. Следующий метод как раз для вас. Можно прописать данные напрямую в формуле, применив функцию DATE .

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

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

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

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

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

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

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

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

Формула, подходящая к этой операции, выглядит следующим образом:

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

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

• Ссылка на ячейку = A2 + 10
• Функция DATE = DATE (2015, 5, 6) + 10
• Функция TODAY (), в случае сложения с текущим днем: = TODAY () + 10

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

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

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

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

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

Варианты:

= A2-10

= DATE (2015, 5, 6) -10

= TODAY () – 10

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

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

Способ сложения:

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

Предположим, ваша цель — к А2 добавить 3 недели. Для этого подходит: = A2 + 3 * 7. 

Способ вычитания:

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

Приведем пример. Если исходной датой является сегодняшний день, мы прописываем = TODAY () — 2 * 7.

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

Здесь у нас также есть выбор. Мы можем воспользоваться функцией DATE либо EDATE.  

Вариант 1: DATE 

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

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

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

• год в A2;
• месяцы в A2 + количество месяцев в C2
• день в А2.

При введении числа со знаком минус, месяцы будут отниматься вместо того, чтобы добавляться:

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

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

Кроме того, вместо указания ссылки, число месяцев можно написать в самой формуле:  

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

Варианты: 

• • Прибавление месяцев: = DATE (ГОД (A2), МЕСЯЦ (A2) + 2, ДЕНЬ (A2))

 

• Вычитание месяцев: = DATE (ГОД (A2), МЕСЯЦ (A2) — 2, ДЕНЬ (A2))

 

Вариант 2. EDATE.

EDATE встречается только в Excel 2007, а также более поздних версиях.

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

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

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

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

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

Складываем:

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

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

Вычитаем:

= EDATE (TODAY (), -10)

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

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

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

В этом случае ситуация похожа на работу с месяцами. Снова воспользуемся функцией 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)

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

= DATE (ГОД (A2) + $ C $ 2, МЕСЯЦ (A2) + $ D $ 2, ДЕНЬ (A2) + $ E $ 2)

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

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

Добавить: 

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

Вычесть:

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

Внутри A2 указывается время, которое изменится. 

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

= А2 + TIME (2, 30, 15)

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

= А2 — TIME (2, 30, -15)

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

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

= TIME А2 ($ C $ 2, $ D $ 2, $ E $ 2)

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

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

Прочитав все разделы, вы узнали много разных формул, которые можно использовать в конкретной ситуации. Но есть ли более быстрый способ? Можно ли объединить все эти формулы в одно простое действие? Ответ – да, можно. Существует отличное дополнение к программе Microsoft Excel под названием Ultimate Suite. В этом приложении есть инструмент «Мастер даты и времени», который делает работу с датами еще проще. Он работает так:

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

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

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

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

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

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

Согласитесь, что было очень просто. Давайте дадим нашему волшебному инструменту более сложную работу. Например, вычтем сразу все параметры. Для этого мы переходим на вкладку «Вычитать» и вводим данные в нужные разделы. Нам необходимо ввести единицы измерения в отдельные ячейки, далее сохранить ссылки на эти ячейки, как продемонстрированно на снимке экрана:

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

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

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

= DATE (ГОД (А2) — $ D $ 2, МЕСЯЦ (А2) — $ E $ 2, ДЕНЬ (А2) — $ G $ 2- $ F $ 2 * 7)

Результат:

А также можно щелкнуть ссылку Показать поля времени и прибавить либо отнять единицы даты и времени с использованием только одной формулы.

Итак, вот вы овладели еще несколькими знаниями и навыками. Желаем вам успешно применять их на практике. Встретимся в следующей статье, где мы разберем еще много всего интересного, связанного с этой полезной программой Microsoft Excel.