В excel нет функции разндат

Формула для сравнения 2 дат

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

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

Формат

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

DATEDIF(начальная_дата; конечная_дата; «единица»)

Формат дат:

• дата пишется строго в порядке день-месяц-год;
• день и месяц можно писать как с ведущим нулем, так и без него, например, 01.01.2000, 15.10.2000, 1.1.2000;
• год можно писать как в 4 цифры (01.01.2015), так и в 2 (01.01.15) (год из двух цифр интерпретируется как год текущего века — 08.12.93 = 08.12.2093, 08.12.00 = 08.12.2000);
• разделителем в дате может быть любой символ (кроме цифры). Если разделитель — точка (15.10.2020), дату не нужно брать в кавычки, а если или — (15/10/2020 или 15-10-2020) — то только в кавычках.

Единицы:

d — разница в днях.

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

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

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

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

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

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

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

Пример 4: начальная_дата 31.03.2007, конечная_дата 01.05.2007 Результат: 1 (месяц)

Пример 5: начальная_дата 01.04.2007, конечная_дата 01.05.2007 Результат: 1 (месяц)

Пример 6: начальная_дата 31.03.2007, конечная_дата 30.04.2007 Результат: 0

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

Пример 1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 2 (года)

Пример 2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Результат: 1 (год)

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

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

Пример 2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Результат: 11 (месяцев) — сравниваются конечная дата 01.03.2009 и модифицированная начальная дата 01.04. 2008 (год начальной даты заменяется годом конечной даты за вычетом 1 года , т.к. 01.04 больше чем 01.03).

md — разница в днях без учета месяцев и лет.

Начальная дата выравнивается под конечную дату для сравнения с ней.

Пример 1: начальная_дата 31.08.2007, конечная_дата 01.05.2008 — начальная дата преобразуется в 31.04.2008. Но в апреле 30 дней, поэтому 31-ое апреля подравняется и станет 30-м апреля. Так же работает и с февралем, включая високосные годы (т.е. не будет несуществующих чисел февраля, вместо этого они заменятся последним числом февраля для года конечной даты).

yd — разница в днях без учета лет.

Пример 1: начальная_дата 01.01.2007, конечная_дата 31.12.2007 Результат: 364 (дня)

Пример 2: начальная_дата 01.01.2008, конечная_дата 31.12.2008 Результат: 365 (дней), т.к. 2008 год — високосный

В эксель разность дат

Как подсчитать разницу даты и времени в excel?

Если вычислять разницу 2018-01-09 15:24:05 и 2018-01-08 20:06:25, то пишет только время, а если отнимать 2018-01-10 15:24:05 и 2018-01-08 20:06:25 то пишет 1 дней и время.

Я так понимаю, такое внутренними средствами Excel это не сделать? Пробовал через

Если разница только во времени, то все как положено считает и выводит только разницу во времени, а если будет разница превышающая 24 часа или один месяц?

Если я правильно Вас понял — то может что-то типа такого?

Дата в Excel — число дней от 01.01.1900г., время — дробная часть числа. Например, 10.01.2018 10:15:20 в числовом выражении будет 43110,4273148148 (выражение даты в десятичном формате можно посмотреть, задав формат ячейки Общий).

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

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

Значение дата/время сохранено в числовом формате. Недостаток: формат отобразит количество дней не более 31

Но дата/время текстом также применяется (например: в конечных документах, для отображения значения в составе другого текста):

РАЗНДАТ (функция РАЗНДАТ)

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

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

Дата, представляющая собой первую или начальную дату заданного периода. Даты можно вводить в виде текстовых строк в кавычках (например, «30.1.2001»), в виде порядковых номеров (например, 36921 представляет 30 января 2001 г., если используется система дат 1900) или как результаты вычисления других формул или функций (например ДАТАЗНАЧ(«30.1.2001»)).

Дата окончания периода.

Тип возвращаемых данных, где:

Количество полных лет в периоде.

Количество полных месяцев в периоде.

Количество дней в периоде.

Разница в днях между начальной и конечной датой. Месяцы и годы дат не учитываются.

Важно: Не рекомендуется использовать аргумент «MD», так как с ним связаны определенные ограничения. См

раздел «Известные проблемы» ниже.

Разница в месяцах между начальной и конечной датой. Дни и годы дат не учитываются.

Разница в днях между начальной и конечной датой. Годы дат не учитываются.

Даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней.

Функция РАЗНДАТ полезна в формулах расчета возраста.

Tart_date sбольше, чемnd_datee, результат будет #NUM!.

Два полных года в периоде (2).

440 дней между 1 июня 2001 г. и 15 августа 2002 г. (440)

75 дней между 1 июня и 15 августа без учета годов дат (75)

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

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

1. Используйте РАЗНДАТ для поиска общего числа лет.

В этом примере Дата начала находится в ячейке D17, а Дата окончания — в E17. В формуле «y» возвращает число полных лет между двумя днями.

2. для поиска месяцев используйте РАЗНДАТ еще раз, указав «ГМ».

В другой ячейке используйте формулу РАЗНДАТ с параметром «ГМ» . «ГМ» возвращает число оставшихся месяцев после последнего полного года.

3. Используйте другую формулу для поиска дней.

Теперь нужно найти количество оставшихся дней. Это можно сделать, написав формулу другого типа, показанную выше. Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.

Предупреждение: Мы не рекомендуем использовать аргумент РАЗНДАТ «MD», так как он может вычислять неверные результаты.

4. необязательно: Объедините три формулы в одну.

Вы можете разместить все три вычисления в одной ячейке, как показано в этом примере. Использование амперсандов, кавычек и текста. Это более длинная формула для ввода, но по крайней мере все это в одной из них. Совет. Нажмите клавиши ALT + ВВОД, чтобы разместить разрывы строк в формуле. Это упрощает чтение. Кроме того, если вы не видите формулу целиком, нажмите клавиши CTRL + SHIFT + U.

Известные проблемы

При использовании аргумента “MD” результатом может быть отрицательное число, нуль или неверное значение. Если вы пытаетесь вычислить оставшиеся дни после последнего полного месяца, вот как можно это сделать:

Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.

Расчет разницы между двумя датами в Excel

Подскажите каким образом расчитать разницу между двумя датами в Excel. Что бы результат отражался в формате: ** лет, ** месяцев, **дней.

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

А вообще есть встроенная функция

Или на VBA ее нет?

Lida_K, Не надо никакого VBA ведь всё решается очень просто :

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

=РАЗНДАТ(A1;A2;»Y»)&» лет, «&РАЗНДАТ(A1;A2;»M»)-РАЗНДАТ(A1;A2;»Y»)*12&» месяцев, «&РАЗНДАТ(A1;A2;»MD») &» дней»

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

А, а я думал пишите свой возрастник — есть такие жаба скрипты, которые вычисляют «разндат» между введеной и текущей и сообщают, типа: «Нам уже 1 год, 3 месяца, 11 дней». На родительских форумах такое повсеместно распространено.

Чтобы учесть всю календарные девиации надо использовать календарь.

Эта формула, естественно, будет возвращать текст, если сие нежелательно, то просто используйте =A2-A1 не забыв установить формат ячейки с формулой как :мм или :мм:сс (если секунды всё-таки могут быть важны)

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

Я скачал две проги (одна бесплатная «Расчет стажа 1.41», другая shareware «Расчетчик стажа 6.1.1.0»), которые самостоятельно считают стаж по исходным парам дат.

Исходя из возможностей этих прог, ищу возможность автоматизировать процесс расчета стажа в MS Office Excel.

Сказано же в писании, предварительно, т.е. допустим в ячейках «A1:10» находятся даты рождения, а в ячейках «B1:B10» даты упокоения, в таком случае

=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);»Y») =РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);»YM») =РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);»MD»)

Вы получите данные — довольно близкие к общему возврасту (стажу)

Меня смущает понятие «довольно близкие»! Не уж то батюшка Билл не предусмотрел возможность использования его продукта (MS Office Excel) сотрудниками «Human Resource Manager».

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

Как я уже указывал, я скачал две проги (одна бесплатная «Расчет стажа 1.41», другая shareware «Расчетчик стажа 6.1.1.0»), которые самостоятельно считают стаж по исходным парам дат. Парадокс: я получил ЧЕТЫРЕ! разных результата — два результата мне дали указанные программы, один результат Excel и ещё один я просчитал на старой доброй бумаге.

Как в таблицах Excel использовать функцию РАЗНДАТ

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

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

Старые программы Lotus 1-2-3 представили функцию РАЗНДАТ, и Excel, вероятно, включил ее в целях совместимости. Однако почему-то компания Microsoft не хочет признавать ее существования. Возможно, юристы каким-то образом вовлечены в эту тайну.

UPD: на сайте поддержки MS Office все-таки нашлась страничка с описанием функции РАЗНДАТ.

В любом случае в этой статье я опишу функцию РАЗНДАТ и расскажу вам то, о чем Microsoft старательно молчит. Синтаксис функции РАЗНДАТ следующий: =РАЗНДАТ(Дата1; Дата2;Интервал) .

Дата1 и Дата2 — стандартные даты (или ссылки на ячейки, содержащие даты). Дата1 должна быть более ранней, чем Дата2 (или равной ей). Третий аргумент — Интервал — представляет собой текстовую строку, определяющую единицу времени, которая будет возвращена.

Корректные коды интервала представлены в этом списке:

• m — количество полных месяцев, прошедших от Дата1 до Дата2;
• d — количество дней между Дата1 и Дата2;
• е — количество полных лет, прошедших от Дата1 до Дата2;
• уш — количество месяцев между Дата1 и Дата2; интервал исключает года, поэтому работает так же, как будто две даты идут в одном и том же году;
• yd — количество дней между Дата1 и Дата2; интервал также исключает года и работает так, как будто Дата1 и Дата2 идут в одном и том же году;
• md — количество дней между Дата1 и Дата2; интервал исключает и месяцы, и года, поэтому работает, как будто Дата1 и Дата2 идут в одном и том же месяце и году.

На рис. 100.1 показано несколько примеров использования функции РАЗНДАТ с каждым из ее возможных аргументов интервала. Формула в D2 , которая была скопирована в ячейки, расположенные ниже, будет следующей: =РАЗНДАТ(A2;B2;C2) .

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

Сколько вам будет лет в определенный год?

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

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

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

Поскольку точный срок медицинского осмотра не определен, вы используете функцию ДАТА с произвольным аргументом дня и месяца, например, DATА(C3; 1; 1).

Функция ДАТА извлекает номер года из ячейки С2, создает полную дату, используя введенные вами номера месяца и дня (в этом примере 1 января), и передает ее в РАЗНДАТ. В результате вы получаете сумму прожитых пациентом лет по состоянию на 1 января определенного года.

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

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

1. Выделяем пространство листа, на котором вы планируете производить вычисления. Кликаем правой кнопкой мыши по выделению. Активируется контекстное меню. В нём выбираем пункт «Формат ячейки…». Как вариант, можно набрать на клавиатуре сочетание клавиш Ctrl+1.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Записываем формулу в выбранную ячейку, согласно её синтаксису, описанному выше, и первичным данным в виде начальной и конечной даты.

Мастер даты и времени — простой способ создавать формулы разницы дат в Excel

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

Надстройка Ultimate Suite  радикально меняет это положение, поскольку там имеется мастер даты и времени, который может мгновенно создать практически любую формулу разницы дат. Если вы не уверены, какую формулу использовать, позвольте мастеру даты и времени вычислить количество дней между двумя датами за вас Вот каким образом это можно сделать:

1. Выберите ячейку, в которую вы хотите вставить формулу.
2. Перейдите на вкладку Ablebits Tools  и нажмите кнопку Мастер даты и времени (Date & Time Wizard).
3. Появится диалоговое окно мастера даты и времени, где вы переходите на вкладку «Разница (Difference)» и вводите необходимые данные для расчёта:
   • Щелкните поле Дата 1  и выберите ячейку, содержащую первую дату.
   • Щелкните поле Дата 2 и укажите ячейку со второй датой.
   • Выберите желаемую единицу или комбинацию единиц времени из раскрывающегося меню «Разница в (Difference in)» . При этом программа позволяет предварительно просмотреть результат в поле и формулу в ячейке.

Инструмент может рассчитать разницу между двумя датами в 15 форматах:

1. Годы
2. Месяцы
3. Недели
4. Дни
5. Годы + месяцы
6. Годы + недели
7. Годы + дни
8. Месяцы + недели
9. Месяцы + дни
10. Недели + дни
11. Годы + месяцы + недели
12. Годы + месяцы + дни
13. Годы + недели + дни
14. Месяцы + недели + дни
15. Годы + месяцы + недели + дни

1. Если вас устраивает то, что вы увидели, нажмите кнопку «Вставить формулу (Insert Formula)», в противном случае попробуйте другие единицы измерения.

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

К сожалению, метки даты и времени вставляются по-английски. Однако, поскольку надстройка формирует для вас не текст, а формулу расчета (что вы и видите на скриншоте), то откорректировать формулу и пользоваться в дальнейшем ее русской версией не слишком сложно.

Да и вряд ли вы самостоятельно напишете такую сложную конструкцию 

Для наиболее удобного представления результатов доступно еще несколько дополнительных опций:

• Исключить годы и / или месяцы из расчетов. (Годы и / или месяцы просто будут отброшены).
• Показывать или не показывать текстовые метки времени, такие как дни, месяцы, недели и годы .
• Показывать или не показывать нулевые единицы (0 месяцев, например) .
• Возвращает результаты как отрицательные значения, если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания). Думаю, вы сталкивались с ситуацией, когда попытка вычесть из более ранней даты более позднюю приводила к ошибке. Здесь вы просто получите отрицательную разность.

Более подробная информация доступна здесь.

Преимущества использования мастера формул даты и времени

Помимо скорости и простоты, Мастер даты и времени предоставляет еще несколько преимуществ:

В отличие от обычной формулы РАЗНДАТ, для формулы, созданной мастером, не важно, какая из двух дат меньше, а какая больше. Разница всегда рассчитывается идеально, даже если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания).
Мастер поддерживает все возможные единицы измерения (дни, недели, месяцы и годы) и позволяет вам выбирать подходящий вариант из 11 различных комбинаций этих единиц.
Формулы, которые мастер создает для вас, являются обычными формулами Excel, поэтому вы можете редактировать, копировать или перемещать их как обычно. Вы также можете поделиться своими таблицами с другими людьми, и все формулы останутся на своих местах, даже если у кого-то нет Ultimate Suite в их Excel.

Также рекомендуем статьи о работе с датами:

Другие расчеты даты и времени

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

Обратите внимание на то, что на момент написания статьи день – 6 октября 2016 г. Используйте ЧИСТРАБДНИ

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

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

Прежде чем начать, выполните указанные ниже действия.Решите, нужно ли исключить даты праздников. Если это так, введите список дат праздников в отдельную область или на лист. Каждый день праздников помещается в отдельную ячейку. Затем выделите эти ячейки, а затем выберите формулы _Гт_ присвоить имя. Назовите диапазон михолидайси нажмите кнопку ОК. Затем создайте формулу, выполнив указанные ниже действия.

В этом примере Дата начала находится в ячейке D53, а Дата окончания — в ячейке E53.

2. в другой ячейке введите формулу, например:

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

Примечание. в Excel 2007 нет ЧИСТРАБДНИ. МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример будет выглядеть следующим образом в Excel 2007: = ЧИСТРАБДНИ (D53, E53). Вы не укажете 1, так как ЧИСТРАБДНИ предполагает, что выходные дни — суббота и воскресенье.

3. при необходимости измените значение 1.

Если Суббота и воскресенье не являются выходными днями, измените значение 1 на другой в списке IntelliSense. Например, 2 устанавливает воскресенье и понедельник в выходные дни.

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ в Excel 2007 всегда предполагает, что выходные дни — суббота и воскресенье.

4. Введите имя диапазона праздников.

Если вы создали имя диапазона праздников в приведенном выше разделе «Начало работы», введите его в конце, как показано ниже. Если у тебя нет праздников, вы можете покинуть запятую и Михолидайс. Если вы используете Excel 2007, вышеприведенный пример будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, михолидайс).

ПероЕсли вы не хотите ссылаться на имя диапазона праздников, вы также можете ввести диапазон, например D35: E:39. Кроме того, вы можете ввести каждый праздник в формуле. Например, если праздничные дни – 1 января и 2 из 2016, введите их следующим образом: = ЧИСТРАБДНИ. Межд (D53, E53, 1, ). В Excel 2007 оно будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, . 2016″>)

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

1. Введите время начала и время окончания.

В этом примере время начала находится в ячейке D80, а время окончания — в E80. Убедитесь, что вводите часы, минуты и пробелы перед символами AM и PM.

2. Установите формат ч/PM.

Выберите обе даты и нажмите клавиши CTRL + 1 (или + 1 на компьютере Mac). Убедитесь, что выбран параметр пользовательские _гт_ ч/PM, если он еще не установлен.

3. вычитание двух значений.

В другой ячейке вычитаете начальную ячейку из ячейки «время окончания».

4. Задайте формат ч.

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите » пользовательские _гт_», чтобы исключить из него результаты «AM» и «PM».

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

1. Введите два полных значения даты и времени.

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

2. Задайте формат 3/14/12 1:30 PM.

Выберите обе ячейки, а затем нажмите клавиши CTRL + 1 (или + 1 на компьютере Mac). Затем выберите Дата _ГТ_ 3/14/12 1:30 PM. Это не Дата, которую вы настроили, а вот только пример того, как будет выглядеть формат

Обратите внимание, что в версиях до Excel 2016 этот формат может иметь другой образец даты, например 3/14/01 1:30 PM

3. вычитание двух значений.

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

Как рассчитать прошедшее время / дни / месяцы / годы между двумя датами в Excel?

Здесь у вас есть два списка времени: один — время начала, другой — время окончания. Можете ли вы быстро вычислить время, прошедшее между двумя временами в одной строке, как показано на скриншоте ниже? В этом руководстве представлены приемы расчета прошедшего времени, дней, месяцев или лет между двумя датами в Excel.

Рассчитайте прошедшее время / дни / месяцы / годы по формуле

Рассчитать прошедшее время

1. Выберите пустую ячейку, введите эту формулу. = ЕСЛИ (B2 <A2; 1 + B2 — A2; B2- A2), A2 — время начала, B2 — время окончания, и нажмите Enter Затем перетащите маркер автозаполнения, чтобы заполнить формулы в нужные ячейки.

2. Не снимая выделения с ячеек формулы, щелкните правой кнопкой мыши, чтобы открыть контекстное меню, и выберите Формат ячеекИ в Формат ячеек диалога под Число вкладка, выберите Время на левой панели, а затем выберите тип времени, который вы хотите отображать в правом разделе. Смотрите скриншот:

3. Нажмите OK. Показано прошедшее время.

Рассчитать прошедшие дни / месяц / годы

Чтобы рассчитать прошедшие дни так просто, вам просто нужно применить эту формулу = B2-A2, A2 — дата начала, B2 — дата окончания.

Чаевые: Для расчета прошедшего месяца вы можете использовать эту формулу = РАЗНДАТ (A2; B2; «м»), A2 — дата начала, B2 — дата окончания. Для прошедших лет применять = РАЗНДАТ (A2; B2; «м») / 12, а затем отформатируйте ячейки как числа.

Рассчитать прошедший год, месяц и дни

Например, у вас есть две даты, и вы хотите знать точную разницу между этими двумя датами и отобразить прошедший период как x лет, x месяц, x дни (например, 2 года, 6 месяцев, 19 дней), вы можете применить формулу ниже.

Выберите пустую ячейку, в которую будет помещен вычисленный результат, введите эту формулу = РАЗНДАТ (A2; B2; «Y») & «Годы» & РАЗНДАТ (A2; B2; «ГМ») & «Месяцы» & РАЗНДАТ (A2; B2; MD) & «Дни», Нажмите Enter ключ для получения результата.

Предположим, у вас есть данные о формате даты и времени в ячейке, и теперь вам нужно добавить к этой дате количество дней, лет, месяцев, часов, минут или секунд. Обычно использование формул является первым методом для всех пользователей Excel, но запомнить все формулы сложно. С участием Kutools for ExcelАвтора вы можете легко добавить дни, годы, месяцы или часы, минуты или секунды к дате и времени, кроме того, вы можете вычислить разницу дат или возраст на основе данного дня рождения, вообще не запоминая формулу. Нажмите, чтобы получить полнофункциональную бесплатную пробную версию через 30 дней!

Kutools for Excel: с более чем 300 удобными надстройками Excel, вы можете попробовать бесплатно без ограничений в течение 30 дней.

Расчет разности дат

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

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

1. Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
2. Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Формат ячеек“. Аналогичного результата можно добиться, используя комбинацию клавиш Ctrl+1.
3. Перед нами окно настроек формата ячеек:
   • по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
   • в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
   • в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
   • нажимаем OK.
4. Теперь все значения, вводимые в ячейки выделенной области будут восприниматься программой как данные в формате “Дата“.

Сравнение 2 столбцов на предмет совпадения

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

4

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

Сама последовательность действий следующая:

1. Переходим на вкладку «Формулы». Там нужно найти кнопку «Присвоит имя». Она находится в самом верху панели инструментов непосредственно под заголовком самой вкладки.
2. Откроется окно, в котором нам нужно задать имя для каждого из диапазонов. Допустим, нам нужно назвать первый диапазон «Таблица 1».
3. После этого выбираем диапазон, который нам нужно так назвать. Для этого нажимаем на кнопку, расположенную справа от поля, подписанного как «Диапазон», выделить нужный набор ячеек и нажать на кнопку «ОК».
4. Действия со вторым списком аналогичны. Единственное исключение – нужно дать ему другое имя. Ну и естественно, задать правильные адреса этого диапазона. 

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

5

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

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

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

8

Нами была применена функция СЧЕТЕСЛИ, которая осуществляет проверку списка на предмет соответствия определенному критерию, после чего выдает значение «ИСТИНА» или «ЛОЖЬ». Если была возвращена «ИСТИНА», то соответствующие ячейки окрашиваются в цвета, заданные пользователем до использования условного форматирования.

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

Вычисление разности двух дат

В этом курсе:

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

Предупреждение: Если значение нач_дата больше значения кон_дата, возникнет ошибка #ЧИСЛО!