Подарки и советы

Вычитание дат. Вычисление разности дат в Microsoft Excel. =разндат(исходная дата;конечная дата;единица измерения)

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

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

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

Метод 1: обычное вычисление

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

Метод 2: функция РАЗНДАТ

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

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

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

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

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

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

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

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

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

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

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

Функции РАЗНДАТ() нет в справке EXCEL2007 и в Мастере функций (SHIFT + F 3 ), но она работает, хотя и не без огрех.

Синтаксис функции:

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Аргумент начальная_дата обязана быть ранее аргумента конечная_дата .

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

разница в полных месяцах

разница в полных годах

разница в полных месяцах без учета лет

разница в деньках без учета месяцев и лет
ВНИМАНИЕ! Функция для неких версий EXCEL возвращает неверное значение, если денек исходной даты больше денька конечной даты (к примеру, в EXCEL 2007 при сопоставлении дат 28.02.2009 и 01.03.2009 итог будет 4 денька, а не 1 денек). Опасайтесь использования функции с сиим аргументом. Другая формула приведена ниже.

Интересно почитать: Как сохранить эксель как картинку

разница в деньках без учета лет
ВНИМАНИЕ! Функция для неких версий EXCEL возвращает неверное значение. Опасайтесь использования функции с сиим аргументом.

Ниже приведено подробное описание всех 6 значений аргумента способ_измерения , также других формул (функцию РАЗНДАТ() можно поменять иными формулами (правда довольно массивными). Это изготовлено в файле примера ).

В файле примера значение аргумента начальная_дата помещена в ячейке А2 , а значение аргумента конечная_дата – в ячейке В2 .

1. Разница в деньках («d»)

Формула =РАЗНДАТ(A2;B2;»d») возвратит ординарную разницу в деньках меж 2-мя датами.

Пример1: начальная_дата 25.02.2007, конечная_дата 26.02.2007
Итог: 1 (денек).

Этот пример показыват, что при подсчете стажа нужно употреблять функцию РАЗНДАТ() с осторожностью. Разумеется, что если сотрудник работал 25 и 26 февраля, то отработал он 2 денька, а не 1. То же относится и к расчету полных месяцев (см. ниже).

Пример2: начальная_дата 01.02.2007, конечная_дата 01.03.2007
Итог: 28 (дней)

Пример3: начальная_дата 28.02.2008, конечная_дата 01.03.2008
Итог: 2 (денька), т.к. 2008 год — високосный

Примечание : Если заинтересовывают лишь рабочие деньки, то к меж 2-мя датами можно посчитать по формуле =ЧИСТРАБДНИ(B2;A2)

2. Разница в полных месяцах («m»)

Формула =РАЗНДАТ(A2;B2;»m») возвратит количество полных месяцев меж 2-мя датами.

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2007
Итог: 1 (месяц)

Пример2: начальная_дата 01.03.2007, конечная_дата 31.03.2007
Итог: 0

При расчете стажа, считается, что сотрудник отработавший все деньки месяца — отработал 1 полный месяц. Функция РАЗНДАТ() так не считает!

Пример3: начальная_дата 01.02.2007, конечная_дата 01.03.2009
Итог: 25 месяцев

=12*(ГОД(B2)-ГОД(A2))-(МЕСЯЦ(A2)-МЕСЯЦ(B2))-(ДЕНЬ(B2) =ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2)) за вычетом 1 года , т.к. 01.04 больше чем 01.03)

Формула быть может заменена другим выражением:
=ОСТАТ(C7;12)
В ячейке С7 обязана содержаться разница в полных месяцах (см. п.2).

5. Разница в деньках без учета месяцев и лет («md»)

Формула =РАЗНДАТ(A2;B2;»md») возвратит количество дней меж 2-мя датами без учета месяцев и лет. Применять функцию РАЗНДАТ() с сиим аргументом не рекомендуется (см. примеры ниже).

Пример1: начальная_дата 01.02.2007, конечная_дата 06.03.2009
Результат1: 5 (дней), т.к. сравниваются конечная дата 06.03.2009 и измененная исходная дата 01.03 .2009 (год и месяц исходной даты заменяется годом и месяцем конечной даты, т.к. 01 меньше чем 06)

Пример2: начальная_дата 28.02.2007, конечная_дата 28.03.2009
Результат2: 0, т.к. сравниваются конечная дата 28.03.2009 и измененная исходная дата 28.03 .2009 (год и месяц исходной даты заменяется годом и месяцем конечной даты)

Пример3: начальная_дата 28.02.2009, конечная_дата 01.03.2009
Результат3: 4 (денька) — совсем непонятный и НЕПРАВИЛЬНЫЙ итог. Ответ должен быть =1. Наиболее того, итог вычисления зависит от версии EXCEL.

Версия EXCEL 2007 с SP3:

Итог – 143 денька! Больше чем дней в месяце!

Версия EXCEL 2007:

Разница меж 28.02.2009 и 01.03.2009 – 4 денька!

При этом в EXCEL 2003 с SP3 формула возвращает верный итог 1 денек. Для значений 31.12.2009 и 01.02.2010 итог совершенно отрицательный (-2 денька)!

Не советую употреблять формулу с вышеуказанным значением аргумента. Формула быть может заменена другим выражением:
=ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2);
ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2);
ДЕНЬ(B2)-ДЕНЬ(A2))

Данная формула только эквивалетное (почти всегда) выражение для РАЗНДАТ() с параметром md. О правильности данной для нас формуле читайте в разделе «Снова о кривизне РАЗНДАТ()» ниже.

6. Разница в деньках без учета лет («yd»)

Формула =РАЗНДАТ(A2;B2;»yd») возвратит количество дней меж 2-мя датами без учета лет. Применять ее не рекомендуется по причинам, изложенным в прошлом пт.

Итог, возвращаемый формулой =РАЗНДАТ(A2;B2;»yd») зависит от версии EXCEL.

Формула быть может заменена другим выражением:
=ЕСЛИ(ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))>B2;
B2-ДАТА(ГОД(B2)-1;МЕСЯЦ(A2);ДЕНЬ(A2));
B2-ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2)))

Снова о кривизне РАЗНДАТ()

Найдем разницу дат 16.03.2015 и 30.01.15. Функция РАЗНДАТ() с параметрами md и ym подсчитает, что разница составляет 1 месяц и 14 дней. Так ли это по сути?

Имея формулу, эквивалентную РАЗНДАТ() , можно осознать ход вычисления. Разумеется, что в нашем случае количество полных месяцев меж датами = 1, т.е. весь февраль. Для вычисления дней, функция находит количество дней в прошлом месяце относительно конечной даты, т.е. 28 (конечная дата принадлежит марту, предшествующий месяц — февраль, а в 2015г. в феврале было 28 дней). Опосля этого отбирает денек начала и добавляет денек конечной даты = ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B6;-1);0))-ДЕНЬ(A6)+ДЕНЬ(B6) , т.е. 28-30+16=14. На наш взор, меж датами все таки 1 полный месяц и все деньки марта, т.е 16 дней, а не 14! Эта ошибка проявляется, когда в прошлом месяце относительно конечной даты, дней меньше, чем дней исходной даты. Как выйти из данной для нас ситуации?

Интересно почитать: Как отсортировать данные в таблице excel по дате

Разница дат в Excel 2010

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

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

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

Финишный эффект выделения окончания сроков через 7 дней:

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

Нужный совет! В ячейке D2 можно употреблять функцию для получения нынешней даты: =СЕГОДНЯ().

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

Формула возвращает разницу дат меж нынешней и установленной планом в деньках. Для решения данной задачки употребляется функция РАЗНДАТ в Excel: где отыскать эту формулу?

Данную функцию вы не отыщите в мастере функций и даже на панели «ФОРМУЛЫ». Ее постоянно необходимо вводить вручную. Первым аргументом функции постоянно обязана быть новая дата, а вторым постоянно – старшая дата. 3-ий аргумент функции описывает единицу измерения количества, которое возвращает функция =РАЗНДАТ(). В данном случае это знак «d» – дней. Это означает, что функция возвращает количество дней. Далее следует оператор <7. Другими словами формула инспектирует, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во 2-м аргументе – относительная, потому что проверятся будут несколько ячеек в столбце C.

По мере необходимости можно к данному спектру добавить новое правило условного форматирования, которое будет предупреждать нас о окончании сроков за 2 недельки. Для этого необходимо добавить новое правило условного форматирования для спектра A2:A7 и всего только незначительно поменять формулу в новеньком правиле:

Цвет форматирования для второго правила можно задать желтоватый. К одному и тому же спектру обязано быть использовано 2 правила условного форматирования. Чтоб проверить изберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами». Потому что у нас поначалу производится верхнее правило следует поменять их порядок в показавшемся окне: «Диспетчер правил условного форматирования». По другому все выделенные задачки будут иметь желтоватую заливку ячеек. Просто выделите 1-ое правило и нажмите клавишу вниз (CTRL+стрелка вниз), как показано на рисунке:

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

Нужный совет! Если к одному и тому же спектру присвоено много правил – соблюдайте иерархию ценностей порядка их выполнения в диспетчере управления правилами. Чем выше правило, тем выше его ценность выполнения по отношению к остальным расположенных ниже под ним.

Дата и время в Excel – это числа сформатированные особым образом. Датой является целая часть числа, а время (часы и минутки) – это дробная часть.

По дефлоту число 1 соответствует дате 01 января 1900 года. Другими словами любая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально разглядим даты, а на последующих уроках – время.

Как в Excel посчитать деньки меж датами?

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

На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
В ячейке A2 введите промежный период в деньках, к примеру 127.
В ячейку A3 введите формулу:=A1+A2.

Интересно почитать: Эксель как вставить дату в

Направьте внимание, формат «Дата» автоматом присвоился для ячейки A3. Легко додуматься, чтоб посчитать разницу в датах в Excel необходимо от новой даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней меж этими 2-мя датами.

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

Сейчас мы научимся вычислять возраст по дате рождения:

На новейший лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
В ячейки B1:B3 проставьте текущею дату.
Сейчас необходимо пользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в спектр C1:C3 последующее значение: =РАЗНДАТ(A1;B1;»y»).

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

Деньки в года более буквально конвертирует функция:=РАЗНДАТ(). Британская версия =DATEDIF(). Вы не отыщите ее в перечне мастера функций (SHIFT+F3). Но если просто введете ее в строчку формул, то она сработает.

Функция РАЗНДАТ поддерживает несколько характеристик:

Параметр	Описание
«d»	Количество полных дней
«m»	Количество полных месяцев
«y»	Количество полных лет
«ym»	Количество полных месяцев без учета лет
«md»	Количество дней без учета месяцев и лет
«yd»	Количество дней без учета лет

Проиллюстрируем пример использования нескольких характеристик:

Внимание! Чтоб функция:=РАЗНДАТ() работала без ошибок, смотрите за тем, чтоб исходная дата была старше конечной даты.

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

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

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

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

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

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

Вычисление даты Excel

На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Сейчас меняем формат ячеек на «числовой» в выделенном спектре B1:B2. Для этого можно надавить комбинацию кнопок CTRL+SHIFT+1.

B1 сейчас содержит число 1, а B2 – 367. Другими словами прошел один високосный год (366 дней) и 1 денек.

Метод отображения даты можно задать при помощи диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» изберите в перечне «Числовые форматы» — «Дата». В разделе «Тип» показываются самые пользующиеся популярностью форматы для отображения дат.

Читайте также: Функции для работы с датами в Excel

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

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

Если Для вас требуется высчитать стаж (страховой) в годах, месяцах, деньках, то, пожалуйста, воспользуйтесь расчетами выполненными в статье Расчет страхового (трудового) стажа в MS EXCEL .

Функции РАЗНДАТ( ) нет в справке EXCEL2007 и в Мастере функций ( SHIFT + F 3 ), но она работает, хотя и не без огрех.

Синтаксис функции:

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Аргумент начальная_дата обязана быть ранее аргумента конечная_дата .

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