Вычитание дат
Вы можете легко найти разницу двух дат, определить формат результата и произвести вычисление на основе списка дат. Например, можно быстро вычесть одну дату из другой или вычислить длительность задач в расписании проекта.
Чтобы научиться вычислять даты в расписании проекта, используйте предоставленные образцы данных и описанные ниже процедуры.
Копирование примера данных
-
Скопируйте данные из примера ниже в ячейку A1 пустого листа.
| Задача | Дата начала | Дата окончания | Длительность |
| Создание плана проекта | 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.
- Перетащите маркер заполнения , чтобы скопировать формулу в оставшиеся строки. 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. Результат — 3.
Вычисление количества месяцев между двумя датами разных лет
Чтобы определить количество месяцев между датами, которые приходятся на разные года, можно использовать формулу, которая определяет число лет между двумя датами, преобразует его в число месяцев и учитывает дополнительные месяцы, чтобы получить точный результат. Для вычисления используются функции МЕСЯЦ и ГОД.
- Выберите ячейку D3. Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
- В ячейке D3 введите =(ГОД(C3)-ГОД(B3))*12+МЕСЯЦ(C3)-МЕСЯЦ(B3), а затем нажмите клавишу RETURN. Результат — 9.
Вычисление количества лет между двумя датами
В Excel года представлены в виде значений. Найдите значение года для каждой из дат с помощью функции ГОД, а затем вычислите разницу между значениями.
- Выберите ячейку D4. Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
- В ячейке D4 введите =ГОД(C4)-ГОД(B4), а затем нажмите клавишу RETURN. Результат — 2.
ДНИ (функция ДНИ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для Mac 2011 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ДНИ в Microsoft Excel. Чтобы узнать больше о функции ДЕНЬ, см. статью функция ДЕНЬ.
Описание
Возвращает количество дней между двумя датами.
Синтаксис
Аргументы функции ДНИ указаны ниже.
- кон_дата Обязательный аргумент. Нач_дата и кон_дата — две даты, количество дней между которыми необходимо вычислить.
- Start_date Обязательно. Нач_дата и кон_дата — две даты, количество дней между которыми необходимо вычислить.
Примечание: В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. имеет порядковый номер 1, а 1 января 2008 г. — 39448, так как от 1 января 1900 г. это 39447 дней.
Замечания
- Если оба аргумента числовые, функция ДНИ вычитает из конечной даты начальную для получения числа дней между ними.
- Если один из аргументов текстовый, он обрабатывается как ДАТАЗНАЧ(дата_как_текст) и возвращает целочисленную дату вместо временного компонента.
- Если аргументы даты являются числными значениями, которые не находятся в диапазоне допустимой даты, то аргумент ДНИ возвращает значение #NUM! значение ошибки #ЗНАЧ!.
- Если аргументы даты — это строки, которые нельзя использовать как допустимые даты, то #VALUE! значение ошибки #ЗНАЧ!.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Находит количество дней между датой окончания (15-МАР-2021) и датой начала (1-ФЕВ-2021). При вводе данных непосредственно в функцию необходимо заключить их в кавычки. Результатом будет 42.
Определяет количество дней между конечной датой в ячейке A2 и начальной датой в ячейке A3 (364).
Расчет количества дней между датами в Excel
Итак, мы имеем несколько серьезных функций, для того чтобы рассчитать кол-во дней между двумя датами, причем с разными параметрами (такими как выходные дни, праздники и так далее).
В этой статье я продемонстрирую вам разные ситуации, где нужно посчитать кол-во дней между датами. Все они будут с разными «уклонами», в одной ситуации нужно посчитать без выходных, в другой количество понедельников и так далее.
Стандартный расчет количества дней
Итак, в Excel есть две функции для вычисления кол-ва дней.
С помощью функции ДНИ
Чтобы получить результат от функции нам понадобится дата начала и дата конца.
Итак, вы указываете эти две даты в аргументах функции и получаете количество дней между ними.
Хороший пример вы можете видеть на картинке ниже:
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Используем эту формулу:
=ДНИ(B2;B1) 
Также вы можете вручную прописать даты в функции (без указания ячеек), но тогда вам нужно заключить их в кавычки.
Эта функция вычислит количество дней между этими датами, но если вы хотите чтобы расчет велся «включительно» с датами начала и конца, то добавьте к результату + 1 (просто пропишите это в функции).
С помощью функции РАЗНДАТ
Эта функция аналогична, её отличие в том, что можно указать больше параметров. То есть больше «подстроить» под ситуацию.
Также можно вычислить кол-во месяцев или лет между датами.
Допустим, мы имеем то что указано на картинке ниже:
=РАЗНДАТ(B1;B2;"D") Эта функция использует три наших аргумента:
- Дата начала — B1
- Дата окончания — B2
- «D» — текстовая строка
Важный момент: этой функции не будет в подсказке при написании названия функции, т.е. если вы будете писать РАЗНДАТ, Excel будет вести себя так, как будто он не знает что это за функция, но это не так.
Функция РАЗНДАТ больше подходит для ситуаций, когда нужно вычислить кол-во лет или месяцев между датами, в других ситуациях удобнее применять ДНИ (функцию).
Вот формула, которая даст результат по месяцам, которые будут между датами:
=РАЗНДАТ(B1;B2;"M") А эта формула, тоже самое что и прошлая, только вычисляет кол-во лет:
=РАЗНДАТ(B1;B2;"Y") Расчет кол-ва рабочих дней
Итак, рабочие дни можно вычислить двумя способами:
- Функция Excel ЧИСТРАБДНИ — ее следует использовать, если выходные дни — суббота и воскресенье.
- Функция Excel ЧИСТРАБДНИ.МЕЖД — используйте ее, когда выходные дни отличаются от субботы и воскресенья.
Сначала быстро рассмотрим синтаксис и аргументы фунции ЧИСТРАБДНИ.
Функция Excel ЧИСТРАБДНИ
Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.
=ЧИСТРАБДНИ(нач_дата, конеч_дата, [праздники])
- нач_дата — это дата начала отсчета.
- end_date — это конечная дата отсчета.
- [праздники] — (Необязательно) Отдельная дата или диапазон дат, которые не будут учитываться в расчетах.
Посмотрим на стандартный пример, когда нужно посчитать кол-во рабочих дней.
Мы будем использовать эту формулу:
=ЧИСТРАБДНИ(B2;C2;$F$2:$F$6) 
Такая вариация этой функции даст то что вам нужно, если вам нужно посчитать рабочие дни без субботы и воскресенья.
Но бывают и другие ситуации, к примеру, в разных странах выходные на неделе строятся по-разному, это может быть пятница и так далее.
Именно для таких ситуаций и появилась функция ЧИСТРАБДНИ.МЕЖД.
Посмотрим что из себя представляет эта функция.
Функция Excel ЧИСТРАБДНИ.МЕЖД
=ЧИСТРАБДНИ.МЕЖД(нач_дата; конеч_дата; [выходные]; [праздники])
- нач_дата — значение даты, представляющее собой начальную дату.
- конеч_дата — значение даты, представляющее дату окончания.
- [нерабочие дни] — (Необязательно) в этом аргументе указываются исключительные дни(праздники и т.д.), если аргумента не будет, выходные останутся по-стандарту(суббота и воскресенье).
А сейчас, попробуем посчитать кол-во рабочих дней, если выходными будут пятница и суббота.
Допустим, таблица такая же как и в прошлом примере:
Будем использовать формулу:
=ЧИСТРАБДНИ.МЕЖД(B2;C2;7;$F$2:$F$6) 3 аргумент в функции сообщает Excel, что пятница и суббота выходные.
Также, мы можем использовать ЧИСТРАБДНИ.МЕЖД для расчета выходных между двумя датами.
Она конечно вычисляет кол-во рабочих дней, но мы можем адаптировать это «под себя».
Допустим, таблица все та же:
Формула, которая нам подойдет:
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Расчет количества дней между датами в Эксель
Довольно часто при работе датами в таблице Эксель возникает необходимость определения временного промежутка между ними, который измеряется в днях. В Excel существуют различные способы для выполнения данной задачи. Давайте рассмотрим их.
Расчет разности дат
Смотрите также: “Как сделать одинаковый размер ячеек в Эксель”
Для корректной работы с датами сперва нужно задать ячейкам соответствующий формат. В противном случае возможны проблемы с корректным отображением дат, что сделает невозможным их дальнейшую обработку.
На самом деле Эксель способен самостоятельно определять формат ячейки после ввода в нее информации. Но этот механизм не всегда срабатывает как надо, поэтому стоит заранее самостоятельно настроить формат ячеек или проверить, правильно ли его определила программа.
- Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
- Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Формат ячеек“. Аналогичного результата можно добиться, используя комбинацию клавиш Ctrl+1.
- Перед нами окно настроек формата ячеек:
- по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
- в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
- в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
- нажимаем OK.
- Теперь все значения, вводимые в ячейки выделенной области будут восприниматься программой как данные в формате “Дата“.
Метод 1: использование формулы вычитания
Смотрите также: “Как сделать первую букву заглавной (прописной) в Excel”
Начнем, пожалуй, с самого простого способа – вычитания двух дат, представленного в виде обычной формулы.
- Вносим две даты, между которыми будем определять разницу в днях, в две разные ячейки отформатированного ранее диапазона.
- Теперь выделим ячейку, в которой планируем отобразить результат нашего вычисления.
ВАЖНО: Количество дней обозначается простым целым числом, следовательно, ячейка для его вывода должна иметь отличный от “Дата” формат, а именно – “Общий“. В противном случае полученный результат будет отображаться в формате даты, что не позволит оценить корректность проведенных вычислений. Для определения текущего формата ячейки достаточно взглянуть на соответствующее поле, расположенное в разделе инструментов “Число” на ленте вкладки “Главная“. - В нашем случае установлено значение “Дата“. Для его изменения вновь вызываем окно формата ячеек и по описанному выше алгоритму устанавливаем числовой формат “Общий“. Для сохранения изменений жмем ОК.
- Теперь можно переходить непосредственно к вычислениям:
- в итоговую ячейку вводим знак “=“;
- кликаем по ячейке с конечной датой (более поздней);
- вводим с клавиатуры символ “–” (знак минус);
- кликаем по ячейке с начальной датой.
- Для получения результата жмем клавишу Enter. В итоге получаем целое число – количество дней между двумя датами.
Метод 2: функция РАЗНДАТ
Следующий способ – это определение разницы между двумя датами при помощи функции РАЗНДАТ.
Функция имеет следующий синтаксис: =РАЗНДАТ(начальная_дата;конечная_дата;единица).
Если с двумя первыми аргументами все понятно – их суть соответствует названию, то на третьем (“единица“) стоит остановиться подробнее, так как именно он позволяет варьировать результат использования функции, выбирая единицу расчета разницы между датами:
- “d” — количество дней между датами;
- “m” — количество полных месяцев между датами;
- “y” — количество полных лет между датами;
- “YM” — разница между датами в месяцах;
- “MD” — разница между датами в днях, без учета месяцев и лет;
- “YD” — разница между датами в днях, без учета лет.
В нашем случае покажем пример использование аргумента “YD”, так как целью расчета является определение количества дней между датами в пределах года.
Основное отличие от простого вычитания одной даты из другой – в порядке следования аргументов функции: в качестве первого аргумента выбирается начальная (более ранняя дата), а в качестве второго – конечная (более поздняя). Опишем ниже алгоритм вычислений.
- Выбираем целевую ячейку, проверяем ее формат по способу, описанному выше.
- Описываемая функция отсутствует в Мастере функций, поэтому вводим ее в ячейку вручную, строго придерживаясь синтаксиса.
- Жмем Enter и проверяем полученный результат. Как мы видим, он аналогичен тому, который мы получили, используя простую формулу вычитания. Значит все сделано верно.
Метод 3: функция ЧИСТРАБДНИ для определения количества рабочих дней
Третий описываемый метод позволяет вычислить количество дней с дополнительным условием – исключением из него нерабочих (выходных и праздничных) дней.
В данном методе используется функция ЧИСТРАБДНИ со следующим синтаксисом: =ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]).
Первые два аргумента аналогичны функции РАЗНДАТ. Последний аргумент является опциональным, то есть функция будет работать и без его указания. Однако именно он позволяет учесть в критериях вычисления нерабочие праздничные дни, так как по умолчанию из общего количества уберутся только субботы и воскресенья.
Приступаем к вычислениям:
- Так как описываемая функция присутствует в списке Мастера функций, воспользуемся им для большей наглядности: встаем на ячейку, куда будем выводить результат и щелкаем по кнопке “Вставить функцию” (fx), расположенной слева от строки формул.
- В открывшемся окне Мастера функций:
- выбираем категорию “Полный алфавитный перечень”;
- находим и кликаем в списке операторов по строке “ЧИСТРАБДНИ”;
- нажимаем ОК.
- Перед нами появится окно аргументов функции. Здесь можно ввести адреса ячеек вручную или, находясь в поле для заполнения значений аргумента, кликнуть по нужной ячейке на листе. Выбрав начальную и конечную дату, а также указав праздничный день (адрес ячейки), нажимаем ОК.
- Предположим, в этот раз мы заранее не задали формат итоговой ячейки как “Общий”. Тогда мы получим результат, отображающийся в виде даты.
- Следовательно, необходимо снова проверить формат по алгоритму, описанному выше и изменить его на “Общий”.
Заключение
Смотрите также: “Как вставить картинку в ячейку в Экселе”
С помощью описанных выше методов можно достаточно легко посчитать количество дней между двумя датами, как с наличием дополнительных критериев, так и без них. Формула вычитания и функция РАЗНДАТ прекрасно подойдут для простого подсчета дней, а функция ЧИСТРАБДНИ поможет с расчетом количества именно рабочих дней между датами. Окончательный выбор зависит от поставленной перед пользователем задачи.