Вычисление разности дат в Microsoft Excel

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

«Формат ячейки…»

Ctrl+1

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

Способ 1: простое вычисление

Проще всего вычислить разность дней между датами с помощью обычной формулы.

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

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

Если в нем стоит значение, отличное от «Общий», то в таком случае, как и в предыдущий раз, с помощью контекстного меню запускаем окно форматирования. В нем во вкладке «Число» устанавливаем вид формата «Общий». Жмем на кнопку «OK».

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

Статья в тему: Как избавиться от остриц навсегда в домашних. Лечение остриц в домашних условиях — народные рецепты для взрослых и детей. Симптомы остриц у взрослых

Способ 2: функция РАЗНДАТ

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

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

«y» — полные года;
«m» — полные месяцы;
«d» — дни;
«YM» — разница в месяцах;
«MD» — разница в днях (месяцы и годы не учитываются);
«YD» — разница в днях (годы не учитываются).

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

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

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

Способ 3: вычисление количеств рабочих дней

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

Статья в тему: Сложное глагольное. Составное глагольное сказуемое

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

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

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

Открывается Мастер функций. В категории «Полный алфавитный перечень» или «Дата и время» ищем элемент «ЧИСТРАБДНИ». Выделяем его и жмем на кнопку «OK».

Открывается окно аргументов функции. Вводим в соответствующие поля дату начала и конца периода, а также даты праздничных дней, если таковые имеются. Жмем на кнопку «OK».

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

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

Статья в тему: Молочница у женщин причины лечение. Молочница у женщин — причины, симптомы, лечение. Помешает ли молочница забеременеть

Помимо этой статьи, на сайте еще 12408 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

Общая формула:

INT(end_datetime-start_datetime)&» days «&TEXT(end_datetime-start_datetime,»h»» hrs «»m»» mins «»s»» secs»»»)

аргументы

End_datetime, start_datetime: the two dates that you to calculate the difference between.

Ошибка

Если аргумент end_datetime меньше аргумента start_datetime, формула вернет значение ошибки #VALUE !.

Возвращаемое значение

Эта формула возвращает текстовое значение.

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

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

=INT(C3-B3)&» days «&TEXT(C3-B3,»h»» hrs «»m»» mins «»s»» secs»»»)

Нажмите Enter ключ для получения результата.

объяснение

Функция INT: Функция INT возвращает целую часть десятичного числа.

Поскольку дата хранится как серийное целое число в Excel, а время хранится как серийное десятичное число, которое меньше 1 в Excel, а datetime сохраняется как серийное десятичное число, например, datetime 12/1/2019 13:23:11 хранится в Excel как 43800.5577662037. Здесь функция INT извлекает целую числовую часть разницы между двумя датами, чтобы получить разницу в днях.

Статья в тему: Что делать если сырость в погребе. Как эффективно просушить погреб от влаги и конденсата? Сушка погреба с помощью свечи

ТЕКСТ функция: Эта функция используется для преобразования числа в текст в указанном формате.

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

Чаевые: Чтобы получить общее количество дней, часов, минут и секунд между набором начальной и конечной дат, вы можете адаптировать формулу с помощью функции СУММПРОИЗВ:

= INT (СУММПРОИЗВ (концы-начало)) & «дни» & ТЕКСТ (СУММПРОИЗВ (концы-начало), «ч» «часы» «м» «мин» «»)

Аргумент end — это диапазон дат окончания, start — это диапазон ячеек с датами начала, например, диапазон C3: C5 содержит даты окончания, B3: B5 содержит даты начала, используйте следующую формулу:

= INT (СУММПРОИЗВ (C3: C5-B3: B5)) & «дни» & ТЕКСТ (СУММПРОИЗВ (C3: C5-B3: B5), «ч» «часы» «м» «мин» «»)

Нажмите Enter ключ.

Относительные формулы

Рассчитать количество дней, оставшихся в месяце
В этом руководстве представлена формула для быстрого расчета количества дней, оставшихся в месяце в Excel.
Рассчитать количество дней, оставшихся от сегодняшнего дня
Иногда вам может потребоваться узнать, сколько дней осталось до истечения срока годности каждого продукта питания. В Excel есть формула, которая поможет вам быстро получить оставшиеся дни.
Расчет дней, оставшихся между двумя датами
Здесь в этом руководстве представлена формула для быстрого расчета оставшихся дней между двумя датами.
Расчет времени в сети с перерывом в расписании
Он предоставляет формулу, которая использует функцию MOD для расчета сетевого времени с перерывами в Excel.

Статья в тему: С какого возраста можно заниматься сексом - опасность раннего начала половой жизни. До какого возраста женщина сохраняет сексуальное влечение Можно ли заниматься интимной жизнью в 14

Относительные функции

Функция ЕСЛИ в Excel
Тест на указанные условия, затем возвращает соответствующие значения
Функция Excel VALUE
Преобразуйте текст в число.
Функция МЕСЯЦ в Excel
МЕСЯЦ используется для получения месяца в виде целого числа (от 1 до 12) от даты.
Функция ДЕНЬ в Excel
Функция ДЕНЬ получает день в виде числа (от 1 до 31) от даты
Функция ГОД в Excel
Функция ГОД возвращает год на основе заданной даты в формате 4-значного серийного номера.

Лучшие инструменты для работы в офисе

Kutools for Excel — поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.

Ввод даты в ячейку Excel

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

Статья в тему: Что указывается в сопроводительном письме. Как составить сопроводительное письмо к резюме. Примеры сопроводительных писем к резюме

Заполните таблицу датами, так как показано на рисунке:

Разные способы ввода дат. В столбце А – способ ввода, а в столбце B – результат отображения.

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

В ячейке B7 Excel сам присвоил текущий год (сейчас 2015-ый) по умолчанию. Это видно при отображении содержимого ячеек в строке формул. Обратите внимание, как изначально вводилось значение в A7.

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

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

Прибавляем N недель

= A2 + N недель * 7

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

Вычитаем N недель из даты в Excel:

= А2 — N недель * 7

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

Метод 1: использование формулы вычитания

Начнем, пожалуй, с самого простого способа – вычитания двух дат, представленного в виде обычной формулы.

Вносим две даты, между которыми будем определять разницу в днях, в две разные ячейки отформатированного ранее диапазона.
Теперь выделим ячейку, в которой планируем отобразить результат нашего вычисления.
ВАЖНО: Количество дней обозначается простым целым числом, следовательно, ячейка для его вывода должна иметь отличный от “Дата” формат, а именно – “Общий“. В противном случае полученный результат будет отображаться в формате даты, что не позволит оценить корректность проведенных вычислений. Для определения текущего формата ячейки достаточно взглянуть на соответствующее поле, расположенное в разделе инструментов “Число” на ленте вкладки “Главная“.

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

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

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

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

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

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

Примеры использования функции РАЗНДАТ в Excel

Пример №1. Подсчет количества лет между двумя датами

На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.

Пример №2. Подсчет количества месяцев между двумя датами

На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество полных месяцев между двумя датами – 19 июня 1989 года и 26 марта 2017 года.

Функция возвращает значение “333”, что является количеством полных месяцев между датами указанными выше.

Пример №3. Подсчет количества дней между двумя датами

На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество дней между двумя датами. Например, функция возвращает значение “10142” дня между двумя датами – 19 июня 1989 года и 26 марта 2017 года.

Если вы хотите посчитать количество дней между двумя датами, исключая количество лет, которые уже завершены, вам нужно использовать аргумент “YD” в качестве третьего (пример на рисунке ниже):

В приведенном выше примере он возвращает значение “280”, то есть число дней между днями, исключая 27 полных лет.

Если вы хотите посчитать количество дней между двумя датами, исключая количество полных лет и месяцев, используйте атрибут “MD” в качестве третьего аргумента (как показано на рис. ниже):

На примере выше функция возвращает значение “7” означающие количество дней между двумя днями “19” и “26” число, исключая значения лет и месяцев.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

голоса

Рейтинг статьи