Excel как вычесть один рабочий день

Функция РАБДЕНЬ.

Она возвращает момент времени, который наступит через определенное число рабочих дней (или наступил ранее). Она не учитывает дни отдыха и любые праздники, которые вы отдельно укажете.

Она подходит для расчета производственных графиков и сроков исполнения на основе стандартного производственного календаря, причем суббота и воскресенье – это для нее всегда дни отдыха.

РАБДЕНЬ – это встроенная функция в Excel 2007, 2010, 2013 и 2016. Ее можно ввести руками либо при помощи мастера функций. В более ранних версиях вам нужно специально включать пакет инструментов анализа.

Вам необходимо использовать следующие аргументы:

РАБДЕНЬ(дата_начала; дни; [праздничные_дни])

Первые два обязательны, а последний – по желанию:

• Дата_начала – с нее начинается подсчет дней.
• Дни – рабочие дни, которые нужно добавить или вычесть из начальной точки отсчета. Положительное число возвращает будущий момент времени, отрицательное — возвращает в прошлое.
• Праздничные_дни – необязательный список дат, которые следует считать нерабочими днями. Это может быть либо диапазон ячеек, содержащих даты, которые вы хотите исключить из расчетов, либо константа массива.

Синтаксис

=WORKDAY(start_date, days, [holidays]) – английская версия

=РАБДЕНЬ(нач_дата;количество_дней;[праздники]) – русская версия

Аргументы функции

• start_date (нач_дата) – дата, которую мы берем за точку отсчета;
• days (количество_дней) – общее количество рабочих дней, не включая выходные. Вы можете использовать как положительные, так и отрицательные числа в аргументе;
• [holidays] ([праздники])– (не обязательно) Это диапазон дат, который необходимо исключить из расчетов. Например, это могут быть какие-либо национальные праздники. Также, это может быть ссылка на диапазон ячеек, который содержит даты, или может быть массивом порядковых номеров, которые содержат даты.

Что возвращает функция

Дату, наступающую по прошествии заданного количества рабочих дней.

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

Чтобы рассчитать рабочие дни в Эксель, следуйте этим простым правилам:

• Чтобы добавить будние дни, введите положительное число в качестве аргумента дни в формуле РАБДЕНЬ.
• Чтобы вычесть будние дни, используйте там же отрицательное число.

Предположим, у вас в ячейке A10 указан начальный момент времени, а список праздников — в ячейках А2: А7. Вы хотите узнать, какое число будет через 90 рабочих дней в будущем и прошлом.

Вы можете сделать это, используя следующие формулы:

Чтобы добавить столько же к начальной точке, но учитывая праздничные дни в А2: А7:

=РАБДЕНЬ($A$10;90;$A$2:$A$7)

Чтобы вычесть 90 рабочих дней, исключая праздники:

=РАБДЕНЬ($A$10;-90;$A$2:$A$7)

Чтобы рассчитать сроки на основе текущей даты, используйте функцию СЕГОДНЯ() в качестве точки начала:

Чтобы добавить 90 рабочих дней:

=РАБДЕНЬ(СЕГОДНЯ();90;$A$2:$A$7)

Чтобы переместиться на такое же время назад по отношению к сегодня:

=РАБДЕНЬ(СЕГОДНЯ();-90)

Чтобы указать время начала непосредственно в формуле, используйте формулу ДАТА():

=РАБДЕНЬ(ДАТА(2020;1;7);90;$A$2:$A$7)

На следующем скриншоте показаны результаты всех этих и еще нескольких формул РАБДЕНЬ:

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

=РАБДЕНЬ($A$10;С1;$A$2:$A$7)

Где A10 – начальная датировка, а C1 – число рабочих дней до (отрицательные числа) или после (положительные числа) нее, без учета праздников.

Настраиваем свой график выходных дней.

В случае, если у вас применяется какой-то особенный график нерабочих дней (не суббота и воскресенье), то вам в помощь – формула РАБДЕНЬ.МЕЖД (WORKDAY.INTL в английской версии).

Она появилась в Excel 2010, в более старых версиях программы ее нет.

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

Синтаксис ее выглядит следующим образом:

РАБДЕНЬ.МЕЖД (дата_начала, дни, [выходные], [праздничные_дни])

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

Дата_начала – начальный момент времени.

Дни – будние дней до (отрицательное значение) или после (положительное значение) начальной точки. Если этот аргумент в результате каких-то расчетов передается в виде десятичного числа, то дробная часть отбрасывается.

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

Выходные – указывает, что именно следует считать днями отдыха. Это может быть либо число, либо текст, как показано ниже.

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

• «0000010» — суббота – отдыхаем.
• «1000001» — понедельник и воскресенье – отдыхаем.

И вот как это может выглядеть на примере:

Я бы рекомендовал обратить внимание на конструкцию

=РАБДЕНЬ.МЕЖД($A$10;90;”1000001″;$A$2:$A$7)

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

Все остальное, думаю, достаточно хорошо понятно из скриншота.

Замечание. Функции Excel РАБДЕНЬ и РАБДЕНЬ.МЕЖД возвращают порядковые номера (числа), представляющие даты. Чтобы они отображались как нужно, выделите нужные ячейки и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Дата» и определите нужный формат.

Количество дней между двумя датами в Excel

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

Функция РАБДЕНЬ

1. Функция РАБДЕНЬ (WORKDAY) почти противоположна функции ЧИСТРАБДНИ (NETWORKDAYS). Она возвращает дату до или после указанного количества будних дней (выходные исключены).

   =WORKDAY(A1,B1)
=РАБДЕНЬ(A1;B1)

   

Примечание: Функция РАБДЕНЬ (WORKDAY) возвращает порядковый номер даты. Примените формат даты к ячейке для её отображения.

Календарь ниже поможет вам лучше понять функцию РАБДЕНЬ (WORKDAY).

Опять же, если вы подставите список праздников, функция РАБДЕНЬ (WORKDAY) вернёт дату до или после указанного количества рабочих дней (исключая выходные и праздничные дни).

Складывать и вычитать календарные дни

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

Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».

Как выглядит формат ячейки в Excel

Таким же образом можно посчитать и количество дней между двумя датами. Просто вычтите из более поздней даты более раннюю. Результат Excel покажет в виде числа, поэтому ячейку с итогом переведите в общий формат: вместо «Дата» выберите «Общий».

К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.

Сделать ячейку, в которой всегда стоит текущая дата

Введите в ячейку функцию СЕГОДНЯ, и в ней всегда будет актуальная дата. Чтобы функция работала исправно, учтите два условия. Во-первых, проверьте, верно ли стоит дата у вас на компьютере. Программа будет учитывать именно ее. Во-вторых, укажите в настройках Excel, чтобы формулы пересчитывались автоматически. Для этого в левом верхнем углу открытого листа Excel нажмите «Файл». В открывшемся меню выберите «Параметры», затем вкладку «Формулы» и проверьте, чтобы в колонке «Вычисления в книге» был выбран параметр «Автоматически».

В ячейке содержится Дата

В столбце А содержатся значения в формате дата, например в ячейке А6 – 13.08.2012.

Чтобы настроить правило Условного форматирования нужно:

• выделить диапазон А6:А14
• выбрать пункт меню
• выбрать тип правила Использовать формулу для определения форматируемых ячеек
• в поле Форматировать значения, для которых следующая формула является истинной ввести формулу =ДЕНЬНЕД(A6;2)>5
• задать формат, например красную заливку фона ячейки.

Даты в столбце А , у которых номер дня недели >5, будут выделены красным.

В ячейке с пользовательским форматом ДДД или ДДДД содержится Дата

Пусть в столбцах B и С также содержатся значения в формате дата, например в ячейке B6 введем формулу =A6 .

Для ячеек в диапазоне B6:B14 настроим пользовательский формат ДДД (для диапазона С6:С14 ДДДД).

Не смотря на то, что теперь в ячейках отображаются Пн и Понедельник, значения в ячейках продолжают оставаться в формате Дата. В этом можно убедиться скопировав значение ( CTRL+V ), например, из ячейки С6 , и вставив его Как значение ( ). В Строке формул будет отображаться значение 13.08.2012, а не слово понедельник . Поэтому настраивать правило Условного форматирования нужно так как показано в предыдущем разделе.

В ячейке содержится Число

Пусть в столбце D содержится номер дня недели, например в ячейке D6 введем формулу =ДЕНЬНЕД(A6;2) .

В этом случае правило Условного форматирования настраивается аналогично случаю когда в ячейке содержится Дата, только несколько упрощается формула =$D6>5

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

Делаем заголовки

В ячейку A1 вбиваем следующую формулу: =”Календарь на ” & ГОД(СЕГОДНЯ()) & ” год”. Функция СЕГОДНЯ() возвращает текущую дату (мы часто будем ей пользоваться). Соответственно связка функций ГОД(СЕГОДНЯ()) возвратит нам текущий год.

Теперь создадим заголовки месяцев. В ячейку где стоит Январь запишем следующую формулу =ДАТА(ГОД(СЕГОДНЯ());1;1). Эта функция возвращает первое января текущего года (01.01.2015 в моем случае). Применим к этой ячейке такой формат, чтобы отображалось только название месяца. Для этого откроем формат ячеек сочетанием клавиш Ctrl + 1 и выберем соответствующий формат (см. рисунок).

Для создания заголовков последующих месяцев будем использовать еще одну очень полезную функцию =ДАТАМЕС(B3;1). Эта функция возвращает дату отстоящую от указанной на заданное число месяцев (в нашем случае на один). Таким образом, у нас получается следующая картина (дополнительно я добавил немного форматирования):

Проставляем числа

Для автоматического проставления чисел в календаре мы будем использовать всего одну формулу – формулу массива. На вид звучит страшновато, но если разобраться – все легко и просто. Выделяем ячейки B5:H10 и вводим следующую формулу (не спешим жать Enter):
=ДАТА(ГОД(B3);МЕСЯЦ(B3);1-1)-(ДЕНЬНЕД(ДАТА(ГОД(B3);МЕСЯЦ(B3);1-1))-1)+{0:1:2:3:4:5:6}*7+{1;2;3;4;5;6;7}

Вместо Enter нажимаем сочетание клавиш Ctrl+Shift+Enter, вставляя таким образом формула массива. В результате в выделенном диапазоне получаем числа указанного в ячейке B3 месяца.

Аналогичную операцию проводим с другими месяцами или просто копируем диапазон дат января в следующие месяца. Получаем следующую картину:

Добавляем условного форматирования

Сейчас в январе календарь содержит даты 29, 30, 31 декабря, а также часть дат февраля. Давайте немного скроем эти числа. Для этого будем использовать условное форматирование. Выделяем ячейки B5:H10 и создаем правило условного форматирования. Форматироваться будут ячейки, для которых следующая формула будет являться ИСТИНОЙ:
=И(МЕСЯЦ(B5)<>1+3*(ЧАСТНОЕ(СТРОКА(B5)-5;9))+ЧАСТНОЕ(СТОЛБЕЦ(B5);9))

В качестве формата я выбрал серый текст шрифта и получил следующую картину:

Автоматически выделяем текущую дату в календаре

Для этого создаем еще одно правило условного форматирования. Ячейка будет форматироваться только в том случае, если ее значение будет равно текущей дате (=СЕГОДНЯ()):

Подготовка ячеек для работы с датами

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

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

После этого откроется окно, в котором нужно выбрать формат «Дата», тип формата, который будет использоваться, и нажать на кнопку «ОК».

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

Подсчет количества дней с помощью простой формулы

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

После этого нужно выбрать ячейку, в которой будет находиться результат вычислений, и присвоить ей формат «Общий». Формат «Дата» для ячейки с результатом не подходит, поскольку в этой ячейке будет находиться количество дней (число), а не конкретная дата.

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

После ввода формулы нужно нажать на клавишу Enter, и вы получите результат.

Как видим, в данном случае количество дней между двумя датами составляет 41.

Второй способ: определение часов через временной промежуток

Предположим, нам дано время 11 часов 45 минут, требуется узнать, сколько будет по истечении 2 часов 15 минут. Будем действовать по инструкции, чтобы получить результат.

1. Отформатируем лист, если в нем проводились предыдущие расчеты, выбираем пустое поле и вписываем в двух соседних ячейках наши часы. Затем кликаем на пустую ячейку рядом. В ней будут выполняться все расчеты.

1. Активируем пустую ячейку, переходим в строку формул, где устанавливаем знак «=» без кавычек. Нажимаем на ячейку с показателем часов на данный момент, ставим знак «+», добавляем соседнюю ячейку. Нажимаем кнопку «Enter». В итоге получится время, которое будет после определенного промежутка (в нашем случае по истечении 2 часов 15 минут).

Сложение и вычитание значений времени

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

Как вы видите в разделах ниже, Excel можно сложить или вычесть время.

Сложение значений времени

Допустим, вам нужно узнать, сколько часов и минут потребует выполнение двух задач. По вашей оценке на выполнение первой задачи потребуется 6 часов 45 минут, а на выполнение второй задачи — 9 часов 30 минут.

Вот один из способов настроить этот способ на этом.

Введите 6:45 в ячейку B2, а в ячейку B3 — 9:30.

В ячейке B4 введите =B2+B3 и нажмите ввод.

Результат — 16:15 –16 часов 15 минут для завершения двух задач.

Совет: Вы можете также складывать значения времени с помощью функции Автосумма для чисел. Выделите ячейку B4, а затем на вкладке Главная выберите Автосумма. Формула будет выглядеть следующим образом: =СУММ(B2:B3). Нажмите ввод, чтобы получить такой же результат (16 часов 15 минут).

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

Чтобы сложить более 24 часов:

В ячейку B2 введите значение 12:45, а в ячейку B3 — 15:30.

В ячейку B4 введите формулу =B2+B3 и нажмите клавишу ВВОД.

Результат — 4:15, что не следует ожидать. Это потому, что время задачи 2 составляет 24-часовой период. 15:30 — то же, что и 3:30.

Чтобы отобразить значение времени, превышающее 24 часа, выберите ячейку B4.

На вкладке Главная в группе Ячейки нажмите кнопку Формат и выберите пункт Формат ячеек.

В диалоговом окне Формат ячеек в списке Категория выберите пункт (все форматы).

В поле Тип в верхней части списка форматов введите [ч]:мм;@ и выберите ОК.

Обратите внимание на двоеточие после [ч] и двоеточие после мм.

В результате получится 28 часов 15 минут. Формат сохраняется в списке Тип для дальнейшего использования.

Вычитание значений времени

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

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

В ячейку B2 введите время начала и введите «a» для AM или » p» для PM. После этого нажмите клавишу ВВОД.

В ячейке C2 введите время окончания, в том числе » a» или » p» и нажмите ввод.

Введите другие значениям времени начала и окончания для своих друзей Сергея и Николая.

В ячейке D2 вычитайте время окончания из времени начала, введите формулу =C2-B2 и нажмите ввод.

В диалоговом окне Формат ячеек в списке Категория выберите пункт (все форматы).

Выберите в списке Тип пункт ч:мм (часы и минуты) и нажмите кнопку ОК.

Теперь видно, что Григорий работал 3 часа 45 минут.

Чтобы получить результаты для Сергея и Николая, скопируйте формулу, выделив ячейку D2 и перетащив ее на ячейку D4.

Параметры форматирования в ячейке D2 скопируются вместе с формулой.

Чтобы вычесть время, которое превышает 24 часа:

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

Выполните следующие действия:

В примере выше щелкните ячейку B1 и перетащите рамку выделения на ячейку B2, что даст вам возможность применить формат к двум ячейкам одновременно.

В диалоговом окне Формат ячеек в списке Категория выберите пункт (все форматы).

В поле Тип вверху списка форматов введите д.м.гггг ч:мм.

Обратите внимание на пустое пространство в конце yyyy и в конце мм.

В дальнейшем новый формат будет доступен в списке Тип.

В ячейке B1 введите дату начала, включая день, месяцигод.

Выполните те же действия для даты окончания в ячейке B2.

Введите формулу в ячейку B3: =(B2-B1)*24.

Результат — 31,5 часа.

Примечание: В этом формате можно складывать и вычитать больше 24 часов Excel в Интернете но нельзя применить пользовательский числовой формат.

Сложение значений времени

Допустим, нужно узнать, сколько часов и минут потребует выполнение двух задач. По вашей оценке на выполнение первой задачи потребуется 6 часов 45 минут, а на выполнение второй задачи — 9 часов 30 минут.

В ячейку B2 введите значение 6:45, а в ячейку B3 — 9:30.

В ячейку B4 введите формулу =B2+B3 и нажмите клавишу ВВОД.

На выполнение двух задач потребуется 16 часов 15 минут.

Совет: Вы также можете складывать время с помощью автоссылки. Щелкните ячейку B4. Затем щелкните Главная > автоссылке. Формула будет выглядеть следующим образом: =СУММ(B2:B3). Нажмите ввод, чтобы получить результат (16 часов 15 минут).

Вычитание значений времени

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

В ячейку B2 введите время начала, нажмите клавишу ПРОБЕЛ, а затем введите (для 12-часового формата времени) букву » a» для AM или » p» для PM и нажмите клавишу ВВОД. В ячейку C2 введите время окончания, включая соответствующую букву (для 12-часового формата времени) «a» или «p», и нажмите клавишу ВВОД. Введите другие значения времени начала и окончания для своих друзей Владимира и Николая.

В ячейку D2 введите формулу =C2-B2, чтобы вычесть значение времени окончания из значения времени начала, и нажмите клавишу ВВОД. Теперь мы видим, что Григорий проработал в рамках проекта 3 часа 45 минут.

Чтобы получить результаты для Владимира и Николая, скопируйте формулу, щелкнув ячейку D2 и перетащив рамку выделения на ячейку D4. Параметры форматирования в ячейке D2 скопируются вместе с формулой.

Источник

Сложение или вычитание дат

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

Сложить или вычесть дни из даты

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

Введите даты сходимой даты в столбце A.

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

В ячейке C2 введите =A2+B2и скопируйте его при необходимости.

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

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

Для функции ДАТАМЕСТ требуются два аргумента: дата начала и количество месяцев, которые нужно прибавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Например, =ВРЕМЯ(«15.09.19»;-5) возвращает 15.04.19.

В этом примере даты начала можно ввести в столбец A.

Введите количество месяцев, которые нужно добавить или вычесть, в столбце B. Чтобы указать, следует ли вычесть месяц, можно ввести знак «минус» (-) перед числом (например, -1).

Введите =ВРЕМЯ(A2;B2) в ячейку C2 и скопируйте ее при необходимости.

В зависимости от формата ячеек, содержащих введенную формулу, результаты могут отображаться в Excel как порядковые номера. Например, 8-фев-2019 может отображаться как «43504».

В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию порядковый номер 1 января 1900 г. — 1, а 1 января 2010 г. — 40179, так как он отс0 1 января 1900 г. находится через 40 178 дней.

Если результаты отображаются в числовом номере, выберите ячейки, о которые идет речь, и следуйте следующим шагам:

Нажмите CTRL+1, чтобы запустить диалоговое окно «Формат ячеек», и щелкните вкладку «Число».

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

Сложить или вычесть годы из даты

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

Как работает формула:

Функция ГОД ищет дату в ячейке A2 и возвращает 2019. Затем он прибавляет 3 года к ячейке B2, в результате чего результат будет 2022.

Функции МЕСЯЦ и ДЕНЬ возвращают только исходные значения из ячейки A2, но они требуются функцией ДАТА.

Наконец, функция ДАТА объединяет эти три значения в дату через 3 года в будущем — 08.02.22.

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

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

Как работает формула:

Функция ГОД ищет дату в ячейке A2 и возвращает 2019. Затем она добавляет 1 год из ячейки B2, в результате чего результатом будет 2020.

Функция МЕСЯЦ возвращает 6, а затем добавляет в нее 7 из ячейки C2. Это интересно, поскольку 6 + 7 = 13, т. е. 1-й год и 1 месяц. В этом случае формула распознает это и автоматически добавляет к результату еще один год, урезав его с 2020-го по 2021-й.

Функция ДЕНЬ возвращает 8 и добавляет к ней 15. Эта формула будет работать так же, как и часть формулы «МЕСЯЦ», если вы ука между числами дней в этом месяце.

Затем функция ДАТА объединяет эти три значения в дату, которая составляет 1 год, 7 месяцев и 15 дней в будущем— 23.01.21.

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

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

В ячейке A1 введите 08.02.2013.

В ячейке B1 введите =A1-15.

В ячейке C1 введите =A1+30.

В ячейке D1 введите =C1-15.

Добавление месяцев к дате

Мы будем использовать функцию ДАТАМЕСЯТ, и вам потребуется дата начала и количество месяцев, которые вы хотите добавить. Вот как добавить 16 месяцев к 24.10.13:

В ячейке A1 введите 24.10.2013.

В ячейке B1 введите =ВРЕМЯ(A1;16).

Чтобы отформатирование результатов в виде дат, выберите ячейку B1. Щелкните стрелку рядом с кнопкой «Числовойформат> даты.

Вычитание месяцев из даты

Для вычитания месяцев из даты будет применяться та же функция ДАТАМАЙТА.

Введите дату в ячейку A1 и в ячейку B1 и формулу =ДАТАМЕС(15.04.2013;-5).

Здесь указывается значение даты начала, которая заключена в кавычках.

Можно просто обратиться к ячейке со значением даты или использовать формулу =ДАТАМЕС(A1;-5) для того же результата.

Источник

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

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

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

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

Скопируйте данные из примера ниже в ячейку 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.

Источник