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

Как посчитать разницу дат в Excel – Офис Ассист

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

Расчет количества дней

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

1. Выделяем пространство листа, на котором вы планируете производить вычисления. Кликаем правой кнопкой мыши по выделению. Активируется контекстное меню. В нём выбираем пункт «Формат ячейки…». Как вариант, можно набрать на клавиатуре сочетание клавиш Ctrl+1.
2. Открывается окно форматирования. Если открытие произошло не во вкладке «Число», то следует в неё перейти. В блоке параметров «Числовые форматы» выставляем переключатель в позицию «Дата». В правой части окна выбираем тот тип данных, с которым собираемся работать. После этого, чтобы закрепить изменения, жмем на кнопку «OK».

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

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

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

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

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

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

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

=РАЗНДАТ(начальная_дата;конечная_дата;единица)

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

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

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

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

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

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

=ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники])

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

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

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

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

Урок: Мастер функций в Excel

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

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

Источник: //officeassist.ru/excel/kak-poschitat-raznicu-dat-v-excel/

Как сделать разницу дат в excel?

Источник: //word-office.ru/kak-sdelat-raznicu-dat-v-excel.html

Посчитать в excel разницу между датами в

​Смотрите также​ формулу:=A2-A1. Установите теперь​ уроке, что дата​ вот образом:​ соответствует более ранней​200?'200px':''+(this.scrollHeight+5)+'px');”>=B1-A1+1​ в ячейку следующую​Нюанс в том, что​ В данном случае​ цветом простроченные сроки​Очистить​Построителя формул​

​08.06.2011​ дней между двумя​

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

​«Праздники»​​ в каких единицах​ является некорректным итогом​ данный формат. В​ Так фактически можно​ потребуется работо-часов фрезерному​ но как можно​ в справке Excel.​1. нач_дата -​При использовании РАЗНДАТ​ 1-2-3.

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

Текущий формат​ большинстве случаев, при​ в Excel посчитать​

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

​ выполнения после того,​Вместо него следует подставлять​«y» — полные года;​ ячейки или диапазона​ введении комплекта символов,​

​ любое время в​

1. ​ деталей мелкосерийного производства.​ не вбивать вручную?​ её и в​

   ​2. кон_дата -​	​ вычитает праздники и​	​ эту функцию не​	​Цвет форматирования для второго​
   ​Разница в днях между​	​ месяцев и учитывает​	​ C2 на листе​
   ​Выделите ячейку D2 — первую​	​ как он поставил​	​ даты праздничных нерабочих​
   ​«m» — полные месяцы;​	​ можно просмотреть, выделив​	​ похожего на дату,​
   ​ минутах.​	​ Время нужно считать​	​ Типа протянуть за​

2. ​ мастере функций. И​​ конечная дата.​

3. ​ выходные.​ получится вставить стандартным​ правила можно задать​​ датами будет вычисляется​

​ дополнительные месяцы, чтобы​

​ (20.09.2011).​ пустую ячейку в​

1. ​ конкретную задачу.​ дней, если таковые​«d» — дни;​

2. ​ его во вкладке​​ ячейка сама переформатируется.​​Примечание. С помощью формата​​ инженерам для анализа​ кончик квадратика (но​

   ​ при наборе вручную​3. код -​Нужно просчитать разницы​ способом через окно​

3. ​ желтый. К одному​

4. ​ функцией =РАЗНДАТ(). Для​​ получить точный результат.​Не заполняйте поле​ столбце “Длительность”.​

   ​ Максим Тютюшев​ имеются за охватываемый​«YM» — разница в​«»​

​ Но лучше все-таки​ ячеек мы настраиваем​

1. ​ производительности оборудования при​ здесь такое не​ первых букв названия​

   ​ текстовое значение, которое​ от начальной до​​Вставка – Функция (Insert​​ и тому же​​ этого В поле​​ Для вычисления используются​​Праздники​​Введите​

2. ​Примечание:​ период. Функция производит​

   • ​ месяцах;​. В блоке инструментов​​ сделать это вручную,​​ способ отображения их​​ определенных количествах операций.​​ работает)​​ функции Excel тоже​​ указывает в каких​
   • ​ конечной даты отпуска​ – Function)​ диапазону должно быть​​ ввода введите формулу:​​ функции МЕСЯЦ и​

3. ​.​​=​​Мы стараемся как​​ расчет всех дней​​«MD» — разница в​

4. ​«Число»​ чтобы подстраховать себя​​ значений. Например, например​​Каждая дробная часть чисел​

5. ​И еще! Как​​ не покажет подсказку​​ единицах необходимо вернуть​​ в календарных днях.​​, ее можно вручную​ применено 2 правила​ =РАЗНДАТ($D$2; C2;”d”)​

6. ​ ГОД.​​Нажмите клавишу RETURN.​​C2-B2​​ можно оперативнее обеспечивать​​ указанного диапазона, исключая​ днях (месяцы и​находится поле, в​

7. ​ от неожиданностей.​​ можно добавить или​​ в Excel может​

8. ​ можно по условию​

   ​ в выпадающем списке.​ разницу дат.​

9. ​Не могу прислать​

10. ​ вписать в ячейку​​ условного форматирования. Чтобы​Нажмите на кнопку «Формат»,​Выберите ячейку D3.​

    ​Результатом является 75 рабочих дней​и нажмите клавишу​ вас актуальными справочными​ субботы, воскресенья, а​

    ​ годы не учитываются);​​ котором отображается данный​Выделяем пространство листа, на​ убрать время из​ отображаться в часах​ закрашивать ячейки? Например,​ Объясняется это довольно​Список допустимых значений​

​ пример – файл​ с клавиатуры -​ проверить выберите инструмент:​

​ чтобы задать желаемый​Если ячейка не пуста,​ (без учета праздников).​ RETURN.​ материалами на вашем​ также те дни,​«YD» — разница в​ показатель.​ котором вы планируете​ даты в Excel.​ и минутах (в​ если > года,​ просто. Эта функция​ аргумента:​ защищён кодированием.​

1. ​ и она сработает!​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».​ цвет заливки для​

   ​ в меню​Выберите ячейку D2.​​В Excel возвращается результат​​ языке. Эта страница​​ которые добавлены пользователем​​ днях (годы не​​Если в нем стоит​​ производить вычисления. Кликаем​

2. ​На следующем примере посчитаем,​​ зависимости от выбранного​​ то зеленое, а​ изначально не является​

   ​Код​

​Вот пример как​Синтаксис функции следующий:​ Так как у​

​ ячеек. Например, красный.​Правка​Перетащите маркер заполнения​ в виде количества​ переведена автоматически, поэтому​ в аргумент​ учитываются).​ значение, отличное от​ правой кнопкой мыши​ какая часть суток​ формата отображения).​ если < года​ функцией Excel. Она​Результат вычисления функции​

1. ​ считается сейчас (формула​

   ​=РАЗНДАТ(Начальная_дата; Конечная_дата; Способ_измерения)​ нас сначала выполняется​​ Нажмите ОК на​​выберите пункты​​, чтобы скопировать​​ дней между двумя​​ ее текст может​​«Праздники»​

2. ​Так как нам нужно​​«Общий»​​ по выделению. Активируется​ прошла от полночи​

   ​Данный факт лучше усвоить​

​ то красное?… =)​ всего лишь поддерживается​

​”y”​ “чистые рабочие дни​С первыми двумя аргументами​ верхнее правило следует​ всех открытых окнах.​Очистить​ формулу в оставшиеся​ датами (104).​

1. ​ содержать неточности и​

   ​.​ рассчитать разницу в​​, то в таком​​ контекстное меню. В​​ до 9:45.​​ на конкретном примере:​​т.е. Как можно​​ Excel для совместимости​

2. ​Число полных лет в​​ между”) – а​​ все более-менее понятно​ изменить их порядок​

   ​Финальный эффект выделения окончания​

support.office.com

Разница между датами в Excel вычисляется функцией РАЗНДАТ

​и​ строки.​Выберите ячейку D2.​ грамматические ошибки. Для​Выделяем ячейку, в которой​ количестве дней между​ случае, как и​ нём выбираем пункт​В ячейки A1:A3 введите​Заполните диапазон ячеек A1:A3​ зафиксировать D1??? D1​ с другими системами​ заданном промежутке​ нужно в календарных​

Как посчитать разницу между датами в Excel

​ – это ячейки​ в появившемся окне:​ сроков через 7​Все​Excel автоматически изменяет ссылки​Перетащите маркер заполнения​

1. ​ нас важно, чтобы​ будет находиться итог​ датами, то наиболее​
2. ​ в предыдущий раз,​«Формат ячейки…»​ 9:45.​
3. ​ числовыми значениями: 0,25​ это текущая дата​ электронных таблиц. В​”m”​ днях​ со стартовой и​
4. ​ «Диспетчер правил условного​ дней:​.​ на ячейки, чтобы​, чтобы скопировать​ эта статья была​

​ вычисления. Кликаем по​ оптимальным решением будет​ с помощью контекстного​

​. Как вариант, можно​Перейтите на A1 и​ (четверть дня); 0,5(полдень);​Karla7​ Excel эта функция​Число полных месяцев в​04.05.2016 07.05.2016 3​ конечной датами. А​

​ форматирования». Иначе все​Выделены зеленым цветом все​В ячейке D3 введите​ включить правильные значения​

​ формулу в оставшиеся​

Формула разница дат в Excel

​ вам полезна. Просим​ кнопке​ применение последнего варианта.​ меню запускаем окно​ набрать на клавиатуре​ вызовите окно «Формат​ 0,75 (3/4 дня).​: Потому что к​

Источник: //my-excel.ru/excel/poschitat-v-excel-raznicu-mezhdu-datami-v.html

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

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

Скачать последнюю версию Excel

Помогла ли вам эта статья?

ДА НЕТ

Источник: //lumpics.ru/the-number-of-days-between-dates-in-excel/

Трюк №38. Трюки с возможностями даты и времени в Excel

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

По умолчанию в Excel используется система дат 1900.

Это означает, что числовое значение, лежащее в основе даты 1 января 1900 года, равно 1, у 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями, позволяющими использовать даты в вычислениях. Формат времени очень похож, но Excel обрабатывает время как десятичные доли, где 1 — это 24:00, или 00:00.

Числовое значение 18:00 (в русской версии) равно 0,75, так как это три четверти от 24 часов.

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

При помощи функции СУММ (SUM) или просто знака плюс время можно складывать. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне А1 :А5, если эти ячейки содержат допустимые значения времени. Однако если Excel не дано никаких специальных указаний, он не складывает время за пределами 24 часов.

Это происходит потому, что, когда значение времени превышает 24 часа (настоящее значение равно 1), оно переходит в новый день и отсчет начинается заново. Чтобы заставить Excel не переходить в новый день после каждых 24 часов, можно использовать формат ячеек 37:30:55 или пользовательский формат [ч]:MM:cc([h]:mm:ss).

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

Вычисление времени и даты

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5.

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24.

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1), в русской версии Excel =ЦЕЛОЕ(А1).

Чтобы получить только время: =A1-INT(A1), в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1), в русской версии Excel =OCTAT(A1;1). И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;”d”), где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),”d”), в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);”d”).

Если вы вычтете начальное время из конечного (=А2-А1), получите в ответе ######, так как Excel по умолчанию не работает с отрицательными значениями времени.

Подробнее о том, как работать с отрицательными значениями времени, — в разделе «Трюк № 74. Отображение отрицательных значений времени».

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2), в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1), в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1).

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3), в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3).

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD), в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)).

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak.

Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

Иногда в таблицах с импортированными данными (или данными, введенными неправильно) даты и время отображаются как текст, а не как настоящие числа.

Это можно легко распознать, немного расширив столбцы, выделив столбец, выбрав команду → Ячейки → Выравнивание (Home → Cells → Alignment) и для параметра По горизонтали (Horizontal) выбрав значение По значению (General) (это формат ячеек по умолчанию).

Щелкните кнопку ОК и внимательно просмотрите даты и время. Если какие-либо значения не выровнены по правому краю, Excel не считает их датами.

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

Не снимая выделение столбца, выберите команду → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время.

Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.

Источник: //Excel2010.ru/hack-38.html