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

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

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

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

Точно так же, если у вас есть данные колл-центра, вы можете сгруппировать их по минутам или часам.

Посмотреть видео — Группировка дат в сводных таблицах (группировка по месяцам / годам)

Как группировать даты в сводных таблицах в Excel

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

В нем есть данные о продажах по дате, магазинам и регионам (восток, запад, север и юг). Данные охватывают более 300 строк и 4 столбца.

Вот простая сводка сводной таблицы, созданная с использованием этих данных:

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

Загрузите данные и следуйте инструкциям.

Группировка по годам в сводной таблице

В приведенном выше наборе данных указаны даты за два года (2014 и 2015 годы).

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

Группировка по кварталам в сводной таблице

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

Проблема с этой сводной таблицей заключается в том, что она объединяет квартальную стоимость продаж за 2014 и 2015 годы. Следовательно, для каждого квартала стоимость продаж представляет собой сумму значений продаж в 1 квартале 2014 и 2015 годов.

В реальной жизни вы, скорее всего, будете анализировать эти кварталы для каждого года отдельно. Сделать это:

Это суммирует данные по годам, а затем по годам по кварталам. Что-то вроде того, что показано ниже:

Примечание. Я использую макет табличной формы на снимке выше.

Когда вы группируете даты более чем по одной временной группе, происходит кое-что интересное. Если вы посмотрите на список полей, вы заметите, что новое поле было добавлено автоматически. В данном случае это годы.

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

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

Все, что вам нужно сделать, это перетащить поле «Год» из области «Строка» в область «Столбцы».

Группировка по месяцам в сводной таблице

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

Опять же, для группировки данных рекомендуется использовать и Год, и Месяц, а не использовать только месяцы (если у вас нет данных только за один год или меньше).

Это сгруппирует поле даты и суммирует данные, как показано ниже:

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

Группировка по неделям в сводной таблице

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

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

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

• Когда вы группируете даты с помощью этого метода, вы не можете сгруппировать их с помощью каких-либо других параметров (например, месяцев, кварталов или лет).
• Вычисляемое поле / элемент не будет работать при группировке с использованием дней.

Группировка по секундам / часам / минутам в сводной таблице

Если вы работаете с большими объемами данных (например, данными колл-центра), вы можете сгруппировать их по секундам, минутам или часам.

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

Предположим, у вас есть данные колл-центра, как показано ниже:

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

Это сгруппирует данные по часам, и вы получите что-то, как показано ниже:

Вы можете видеть, что метки строк здесь — 09, 10 и так далее… которые являются часами в сутках. 09 означает 9 утра, а 18 — 6 вечера. Используя эту сводную таблицу, вы можете легко определить, что большинство вызовов разрешается в течение 1-2 часов дня.

Точно так же вы можете сгруппировать даты по секундам и минутам.

Как разгруппировать даты в сводной таблице в Excel

Это мгновенно разгруппировало бы любую сделанную вами группировку.

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

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

Как получить месяц из даты в Excel

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

Дата и время

25.08.2013 18:03

14.05.2013 18:04

07.08.2013 18:02

11.09.2013 18:01

15.05.2013 10:32

Прибавить к дате количество месяцев excel

Примечание: Функция ДАТАМЕС() – это функция из надстройки «Пакет анализа», который по умолчанию включен в MS EXCEL 2007 (в отличие от более ранних версий EXCEL).

Конец месяца

Если необходимо к последнему дню месяца прибавить 1 месяц таким образом, чтобы новая дата была также последним днем месяца, то нужно использовать формулу =КОНМЕСЯЦА(A6;1)

Таблица с датами отстоящими на месяц (формулы)

Для составления таблицы, состоящей из последовательных дат отстоящих на 1 месяц можно воспользоваться формулой =ДАТАМЕС($B$18;СТРОКА()-СТРОКА($A$20)) и таблицей в формате Excel2007 (не обязательно), см. файл примера .

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

Таблица с датами отстоящими на месяц (Прогрессия)

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

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

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

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

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

В ячейке C2 введите = a2 + b2и скопируйте вниз по мере необходимости.

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

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

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

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

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

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

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

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

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

Нажмите клавиши CTRL + 1 , чтобы открыть диалоговое окно Формат ячеек , и откройте вкладку число .

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

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

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

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

Функции Month и Day возвращают только исходные значения из ячейки a2, но они необходимы для использования функции Дата.

Наконец, функция Дата объединяет эти три значения в дату, состоящие из 3 лет в будущем — 02/08/22.

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

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

Функция year проявляет дату в ячейке A2 и возвращает 2019. Затем он добавляет 1 год из ячейки B2, что приводит к 2024.

Функция Month возвращает 6, а затем добавляет к ней 7 из ячейки C2. Это будет интересно, поскольку 6 + 7 = 13, то есть 1 год и 1 месяц. В этом случае формула рассчитает, что и автоматически добавит еще один год к результату, что приведет к его отрезкой от 2024 до 2024.

Функция Day возвращает число 8, и к ней добавляется 15. Это будет работать аналогично части формулы, если вы выйдете на количество дней в заданном месяце.

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

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

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

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

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

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

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

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

Введите дату в ячейку a1 и ячейку B1 и введите формулу = ДАТАМЕС (4/15/2013,-5) .

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

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

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

Функция ДАТА объединяет три числа и делает из них дату. Аргументы у нее следующие: (год;месяц;день) . Поставим в А1 курсив и наберем формулу:

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

Указывать аргументы можно по-разному. Если выбрать 117 год, то Эксель это также поймет как 2017, поскольку отсчет начинается с 1900 года, а 1900+117=2017. День и месяц могут быть как положительными, так и отрицательными числами. Например, если с начала июля отнять 5 дней, то получится 25 июня.

А если отнять с начала 2017 года 2 месяца, то будет октябрь 2016.

Кроме того последние два аргумента не обязательно должны быть в пределах от 1 до 12 и от 1 до 31. Если значения будут больше, то получится вот что: 35-31=4 — это день, 13-12=1 и плюс 31 день с предыдущего расчета, выходит второй месяц. Ну и 12 месяцев — это год. Значит из 35.13.2017 будет 4 февраля 2018-го.

Использовать функцию ДАТА можно, если у Вас все данные находятся в разных ячейках и к ним применен общий формат. В этом случае, она объединит все числа. Поставьте равно, пропишите функцию и выделяйте по очереди, сначала год ( С3 ), потом месяц ( В3 ) и день ( А3 ). Теперь в D3 указан нужный день, и ячейка отформатирована должным образом.

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

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

Рассматриваемая функция поможет рассчитать и другую дату, на основе любой выбранной. Напишем в Е3 следующую формулу:

Она берет год из D3 и прибавляет к нему 2, к месяцу прибавится 1, а дню — 5. Потом полученное значение отображается в Е3 . Так можно рассчитывать день годовщины работы или окончание контракта.

Если Вам дали документ, а день, месяц и год в нем отображаются в виде набора цифр ( А7 ), тогда нашу функцию можно применить, чтобы преобразовать число в дату. Для этого необходимо использовать в качестве аргументов дополнительные функции: ПРАВСИМВ , ПСТР и ЛЕВСИМВ .

Если число имеет вид: 10072017 (ДДММГГГГ), то формула выглядит так:

Первый аргумент — год — это 4 правых символа из А7 (2017). Потом берем месяц — вытягиваем из А7 два числа, начиная с третьего (07). А день — это 2 цифры слева (10).

Когда число записано так: 20170710, то есть ГГГГММДД, нужно использовать вот такую формулу:

Сначала достаем из А8 4 символа слева, потом номер месяца и справа 2 цифры, обозначающие день.

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

Новый вариант календаря был принят в обращение 15 октября 1582 папой Григорием XIII и получил название Григорианский в честь его имени. Из-за неточностей предыдущего Юлианского календаря образовалось 10 лишних дней. Поэтому переход на новое летоисчисление перескочил этот лишний промежуток времени.

Прибавить к дате количество месяцев excel

• Когда вы группируете даты с помощью этого метода, вы не можете сгруппировать их с помощью каких-либо других параметров (например, месяцев, кварталов или лет).
• Вычисляемое поле / элемент не будет работать при группировке с использованием дней.

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

Как складывать и вычитать даты, дни, недели, месяцы и годы в 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 дней.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Год из даты в ячейке A2 ;
• Месяц из даты в ячейке A2 + количество месяцев, указанное в ячейке C2 ;
• День из даты в ячейке A2 ;

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

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

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

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

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

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

• Начальная дата – дата, от которой отсчитывается количество месяцев.
• Месяцы – количество месяцев, которые нужно прибавить (положительное число) или отнять (отрицательное число).

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

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

Эта формула прибавляет 10 месяцев к 7 мая 2015 года

Эта формула отнимает 10 месяцев от сегодняшней даты

Примечание: Функция ДАТАМЕС (EDATE) возвращает просто целое число. Чтобы представить его как дату, необходимо применить к ячейке формат даты. Как это сделать, указано в статье Как изменить формат даты в Excel .

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

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

ДАТА(ГОД(дата ) + N лет ; МЕСЯЦ(дата ); ДЕНЬ(дата ))
= DATE(YEAR(дата ) + N лет , MONTH(дата ), DAY(дата ))

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

Прибавляем 5 лет к дате, указанной в ячейке A2 :

Вычитаем 5 лет из даты, указанной в ячейке A2 :

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

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

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

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

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

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

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

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

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

A2 + ВРЕМЯ(часы ; минуты ; секунды )
= A2 + TIME(часы , минуты , секунды )

A2 — ВРЕМЯ(часы ; минуты ; секунды )
= A2 — TIME(часы , минуты , секунды )

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

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

Пример 1

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

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

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

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

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

В этой статье

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

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

Ячейки A1 и C1 представляют даты платежей (08.02.10 и 10.03.10) для февраля и марта, а ячейки B1 и D1 — даты (24.01.10 и 23.02.10), когда необходимо перевести средства для заданных дат платежей.

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

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

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

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

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

В этом случае функция использует значение даты, введенное непосредственно («16.10.2009»).

В ячейках B5 и C5 должна отображаться дата 16.02.11 .

Примечание: В зависимости от формата ячеек, содержащих введенные формулы, результаты могут отображаться в Excel как числа ; в этом случае дата 16.02.2011 может отображаться как 40 590.

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

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

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

В диалоговом окне Формат ячеек откройте вкладку Число .

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

Принцип работы формулы

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

Можно использовать похожую формулу для добавления месяцев к дате. Например, используя данные из предыдущего примера, можно добавить 9 месяцев к дате 09.06.09 с помощью формулы =ДАТА(ГОД(A2);МЕСЯЦ(A2)+9;ДЕНЬ(A2)) . Эта формула возвращает дату 09.03.10 .

Добавление к дате определенного количества дней, месяцев и лет

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

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

Принцип работы формулы

В каждой формуле указанное количество лет, месяцев и дней прибавляется к дате, содержащейся в ячейке A2.

Калькулятор дней считает количество дней, недель, месяцев, лет и даже часов, минут и секунд между двумя указанными датами.
По умолчанию Калькулятор дней считает дни между сегодняшней датой и 1 января 2000 года

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

Калькулятор дней может считать дни в диапазоне от 40 века Н.Э. до 40 века до Н.Э.
Выполненные расчеты можно сохранить в памяти калькулятора нажав на кнопку «Сохранить». Возможно сохранение до 30 значений.
Вы можете удалять или корректировать сохраненные результаты.
Сохраненные результаты можно подставить в основные расчеты, щелкнув мышью по нужной строке.

Часто задаваемые вопросы

Да, конечно, учитывает. Учитывается не просто количество дней в году, но и количество дней в каждом отдельном месяце. Это легко проверить немного поэкспериментировав с настройками Калькулятора дней.

Исторические факты

В связи с несовершенством Юлианского календаря итальянским астрономом Лилием Алоизием был создан новый вариант летоисчисления. Его отличие заключалось в том, что високосными годами в конце века были только те, которые кратны 400.

Новый вариант календаря был принят в обращение 15 октября 1582 папой Григорием XIII и получил название Григорианский в честь его имени. Из-за неточностей предыдущего Юлианского календаря образовалось 10 лишних дней. Поэтому переход на новое летоисчисление перескочил этот лишний промежуток времени.

= ДАТА(ГОД(дата) + X лет; МЕСЯЦ(дата) + Y месяцев; ДЕНЬ(дата) + Z дней)
= DATE(YEAR(дата) + X лет, MONTH(дата) + Y месяцев, DAY(дата) + Z дней)
Для вычитания X лет, Y месяцев и Z дней:

Дополнительные вычисления в сводных таблицах |

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