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

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

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

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

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

Задача	Дата начала	Дата окончания	Длительность
Создание плана проекта	08.06.2011	20.09.2011
Этап 1.	21.09.2011	02.06.2012
Этап 2.	03.06.2012	10.12.2014
Этап 3.	11.12.2014	20.04.2017

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

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

Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность».
Введите =C2-B2 и нажмите клавишу RETURN. В Excel возвращается результат в виде количества дней между двумя датами (104).
Выберите ячейку D2.
Перетащите маркер заполнения

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

Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность». Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
Выполните одно из указанных ниже действий.
В Excel для Mac 2011 на вкладке «Формулы» в области «Функция»щелкните построитель формул.

Параметр

«Вставить функцию».

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

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

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

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

Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность». Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
В ячейке D2 введите =МЕСЯЦ(C2)-МЕСЯЦ(B2), а затем нажмите клавишу RETURN. Результат — 3.

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

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

Выберите ячейку D3. Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
В ячейке D3 введите =(ГОД(C3)-ГОД(B3))*12+МЕСЯЦ(C3)-МЕСЯЦ(B3), а затем нажмите клавишу RETURN. Результат — 9.

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

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

Выберите ячейку D4. Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
В ячейке D4 введите =ГОД(C4)-ГОД(B4), а затем нажмите клавишу RETURN. Результат — 2.

Сложить или вычесть даты в Excel для Mac

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

Добавление и вычитание дней из даты

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

Откройте новый лист в книге.
В ячейке A1 введите 08.02.2012.
В ячейке B1 введите =A1-15 и нажмите клавишу RETURN. Эта формула вычитает 15 дней из даты в ячейке A1.
В ячейке C1 введите =A1+30 и нажмите клавишу RETURN. Эта формула добавляет 30 дней к дате в ячейке A1.
В ячейке D1 введите =C1-15 и нажмите клавишу RETURN. Эта формула вычитает 15 дней из даты в ячейке C1. В ячейках A1 и C1 даты вычета счетов за февраль и март (08.02.2012 и 09.03.2012). Ячейки B1 и D1 показывают даты (24.01.2012 и 23.02.12), на которые следует перевести средства, чтобы они поступают за 15 календарных дней до срока.

Добавление и вычитание месяцев из даты

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

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

Значение начальной даты можно указать с помощью ссылки на ячейку, содержащую дату, или ввести дату в кавычках, например «15.02.2012».

Предположим, нужно добавить 16 месяцев к дате 16 октября 2012 г.

В ячейке A5 введите 16.10.2012.
В ячейке B5 введите =ДАТАМЕС(A5;16) и нажмите клавишу RETURN. Функция использует значение из ячейки A5 в качестве начальной даты.
В ячейке C5 введите =ДАТАМЕС(«16.10.2012»;16) и нажмите клавишу RETURN. В этом случае функция использует введенное значение даты («16.10.2012»). В обеих ячейках B5 и C5 отображается дата 16.02.2014. Почему в результатах отображаются цифры, а не даты? В зависимости от формата ячеек, содержащих формулы, которые вы ввели, результаты Excel отображаться как порядковые номера. В этом случае 16.02.14 может отображаться как 41686. Если результаты отображаются в виде серийных номеров, измените формат с помощью указанных здесь действий.
1. Выделите ячейки B5 и C5.
2. На вкладке Главная в группе Формат выберите элемент Формат ячеек, а затем — элемент Дата. Числовые значения в ячейках должны отобразиться в виде дат.
Добавление и вычитание лет из даты

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

Количество прибавляемых или вычитаемых лет

Вычисление разности двух дат

Используйте функцию РАЗНДАТ, если нужно вычислить разницу двух дат. Сначала поместите дату начала в одну ячейку, а дату окончания — в другую. Затем введите формулу, например одну из следующих.

Предупреждение: Если значение нач_дата больше значения кон_дата, возникнет ошибка #ЧИСЛО!

Разница в днях

В этом примере дата начала находится в ячейке D9, а дата окончания — в ячейке E9. Формула находится в ячейке F9. Параметр «д» возвращает количество полных дней между двумя датами.

Разница в неделях

В этом примере дата начала находится в ячейке D13, а дата окончания — в ячейке E13. Параметр «д» возвращает количество дней. Но обратите внимание на /7 в конце. Это делит количество дней на 7, так как в неделе содержится 7 дней. Обратите внимание, что этот результат также должен быть представлен в числовом формате. Нажмите клавиши CTRL+1. Затем щелкните Числовой > Число десятичных знаков: 2.

Разница в месяцах

В этом примере дата начала находится в ячейке D5, а дата окончания — в ячейке E5. В формуле «м» возвращает количество полных месяцев между двумя днями.

Разница в годах

В этом примере дата начала находится в ячейке D2, а дата окончания — в ячейке E2. Параметр «г» возвращает количество полных лет между двумя днями.

Расчет возраста в накопленных годах, месяцах и днях

Вы также можете вычислить возраст или время работы другого человека. Результат может выглядеть так: «2 года, 4 месяца, 5 дней».

1. Используйте функцию РАЗНДАТ, чтобы найти общее количество лет.

В этом примере дата начала находится в ячейке D17, а дата окончания — в ячейке E17. В формуле параметр «г» возвращает количество полных лет между двумя днями.

2. Снова используйте функцию РАЗНДАТ с «гм», чтобы найти месяцы.

В другой ячейке используйте функцию РАЗНДАТ с параметром «гм». Параметр «гм» возвращает количество оставшихся месяцев с последнего полного года.

3. Используйте другую формулу для поиска дней.

Теперь нужно найти количество оставшихся дней. Для этого мы напишем формулу другого типа, показанную выше. Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.

Предупреждение: Не рекомендуется использовать аргумент «мд» функции РАЗНДАТ, так как он может вычислять неточные результаты.

4. Необязательно: объединение трех формул в одну.

Все три вычисления можно поместить в одну ячейку, как в этом примере. Используйте амперсанды, кавычки и текст. Эту формулу дольше вводить, но она содержит в себе все вычисления. Совет. Нажмите клавиши ALT+ВВОД, чтобы ввести разрывы строк в формулу. Это упрощает чтение. Кроме того, если вы не видите всю формулу, нажмите клавиши CTRL+SHIFT+U.

Скачивание примеров

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

Другие вычисления даты и времени

Вычисление разницы между сегодняшней и другой датой

Как показано выше, функция РАЗНДАТ вычисляет разницу между датой начала и датой окончания. Однако вместо ввода определенных дат в формуле можно также использовать функцию СЕГОДНЯ(). При использовании функции СЕГОДНЯ() Excel в качестве даты использует текущую дату компьютера. Имейте в виду, что эта переменная будет меняться при повторном открыть файле в будущем.

Обратите внимание, эта статья была написана 6 октября 2016 г.

Вычисление рабочих дней с праздниками или без них

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

Прежде чем начать. Решите, нужно ли исключить даты праздников. При исключении введите список дат праздников в отдельной области или на отдельном листе. Поместите каждую дату праздника в собственную ячейку. Затем выделите эти ячейки и нажмите Формулы > Задать имя. Назовите диапазон МоиПраздники и нажмите ОК. Затем создайте формулу с помощью указанных ниже действий.

1. Введите дату начала и окончания.

В этом примере дата начала находится в ячейке D53, а дата окончания — в ячейке E53.

2. В другой ячейке введите формулу следующего вида.

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

Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако там есть функция ЧИСТРАБДНИ. Указанный выше пример будет выглядеть в Excel 2007 следующим образом: =ЧИСТРАБДНИ(D53;E53). Не нужно указывать цифру 1, так как функция ЧИСТРАБДНИ предполагает, что выходными являются суббота и воскресенье.

3. При необходимости измените цифру 1.

Если суббота и воскресенье не являются выходными днями, измените 1 на другое числовое значение из списка IntelliSense. Например, значение 2 устанавливает воскресенья и понедельники в качестве выходных дней.

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ в Excel 2007 всегда предполагает, что выходными являются суббота и воскресенье.

4. Введите имя диапазона праздников.

Если вы создали имя диапазона праздников в разделе «Прежде чем начать» выше, введите его в конце следующим образом. Если у вас нет праздников, вы можете не использовать точку с запятой и МоиПраздники. Если вы используете Excel 2007, указанный выше пример будет выглядеть так: =ЧИСТРАБДНИ(D53;E53;MyHolidays).

Совет. Если вы не хотите указывать имя диапазона праздников, вместо этого вы можете ввести диапазон, например D35:E39. Или можно ввести в формулу каждый праздник. Например, если ваши праздники приходились на 1 и 2 января 2016 г., введите их следующим образом: =ЧИСТРАБДНИ.МЕЖД(D53;E53;1;). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53;E53;)

Расчет затраченного времени

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

1. Введите время начала и время окончания.

В этом примере время начала находится в ячейке D80, а время окончания — в ячейке E80. Введите час, минуты и пробел перед AM или PM.

2. Установите формат «ч:мм AM/PM».

Выберите обе даты и нажмите клавиши CTRL+1 (или

+1 на компьютере Mac). Выберите вариант (все форматы) > ч:мм AM/PM, если он еще не установлен.

3. Вычтите два времени.

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

4. Установите формат «ч:мм».

Нажмите клавиши CTRL+1 (или

+1 на Mac). Выберите (все форматы) > ч:мм, чтобы результат не содержал AM и PM.

Расчет затраченного времени между двумя датами со временем

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

1. Введите две полные даты со временем.

В одной ячейке введите полную дату и время начала. А в другой ячейке введите полную дату и время окончания. Каждая ячейка должна содержать месяц, день, год, час, минуты, и пробел перед AM или PM.

2. Установите формат «14.03.12 1:30 PM».

Выберите обе ячейки и нажмите клавиши CTRL+1 (или

+1 на компьютере Mac). Затем выберите Дата > 14.03.12 1:30 PM. Это не дата, которую вы установили, а просто пример того, как будет выглядеть формат. Обратите внимание, что в версиях до Excel 2016 этот формат может использовать другой пример даты, например 14.03.01 1:30 PM.

3. Вычтите два значения.

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

4. Установите формат «[ч]:мм».

Нажмите клавиши CTRL+1 (или

+1 на Mac). Выберите пункт (все форматы). В поле Тип введите [ч]:мм.

Excel: арифметические действия с датами

Если в работе вам необходимо проводить операции с датами, возможности Excel помогут упростить вашу работу. С датами можно выполнять различные операции. Можно менять форматы дат (с помощью вкладки Число диалогового окна Формат ячейки), сортировать их в порядке возрастания или убывания. С датами можно выполнять арифметические действия. Например, чтобы получить какую-нибудь дату в будущем, можно прибавить к текущей дате заданное число дней. Или, если из одной даты вычесть другую, можно определить число дней, прошедших между ними.

Пример 1

1. Чтобы выяснить, сколько проработал в компании тот или иной служащий, нужно из текущей даты вычесть дату поступления его на работу. Если в рабочей таблице содержатся даты приема на работу, в нее целесообразно добавить сегодняшнее число. (Самый простой способ введения текущей даты – с помощью комбинации клавиш CTRL SHIFT; ).

2. Активизируйте ячейку, в которую будет заноситься трудовой стаж данного служащего.

3. Теперь из текущей даты нужно вычесть дату приема на работу. В рассматриваемом примере сначала можно попробовать воспользоваться формулой =$В$2-C5. Обратите внимание на результат! Получилось такое большое число, потому что формула вычисляет количество дней (а не лет) работы на предприятии.

4. Разделив результаты на 365, получим ответ в годах. В нашем случае формула должна иметь вид =($В$2-C5)/365. Теперь легко заметить, что первый сотрудник проработал на предприятии более 22 лет.

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

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

Пример 2

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

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