Plazer-don.ru

Сварочное оборудование
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

Звучит не очень? На самом деле, иногда возможность быстро и точно посчитать сколько же времени прошло с какого-то события, бывает очень полезна. Сколько месяцев прошло с дня вашего рождения, сколько времени вы уже просиживаете штаны на этом месте работы, или сколько дней вы сидите на диете – да мало ли применений этой полезной функции? А самое главное, подсчет можно автоматизировать и при каждом открытии книги MS Excel получать точные данные именно для сегодняшнего дня! Звучит интересно, неправда ли?

Функция РАЗНДАТ() принимает три аргумента:

  • Начальная дата – дата с которой ведется счет
  • Конечная дата – до которой ведется счет
  • Единица измерения – дни, месяцы, годы.

Записывается это так:

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

Единицы измерения записываются как:

  • “y” – разница дат в полных годах
  • “m” – разница дат в полных месяцах
  • “d” – разница дат в полных днях
  • “yd” – разница дат в днях с начала года без учета лет
  • “md” – разница дат в днях без учета месяцев и лет
  • “ym” – разница дат в полных месяцах без учета лет

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

=РАЗНДАТ(14.07.1984;22.03.2016;”y”)

Применение функции РАЗНДАТ() на практике

Применение функции РАЗНДАТ() на практике

Обратите внимание – последний аргумент всегда заключен в кавычки.

Если же я хочу получить точный возраст, то запишу усложненную формулу:

=РАЗНДАТ(F2;G2;”y”)&” год “&РАЗНДАТ(F2;G2;”ym”)&” месяцев”

Усложняем применение РАЗНДАТ()

Усложняем применение РАЗНДАТ()

В которой функция РАЗНДАТ() вызывается сразу два раза, с разными значения, а слова “год” и “месяцев” просто пристыковываются к результату. То есть настоящая мощь функции проявляется только тогда, когда её комбинируют с другими возможностями MS Excel.

Ещё один интересный вариант – добавить в функцию ежедневно сдвигающийся относительно сегодняшней даты счетчик. К примеру, если я решу писать формулу которая высчитывает количество дней до моего отпуска в стандартном виде, это будет выглядеть примерно так:

Простой калькулятор остатка рабочих дней

Простой калькулятор остатка рабочих дней

Читайте так же:
Какой штраф за использования магнита для счетчика

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

Чтобы избежать это досадной мелочи, в качестве первого аргумента (сегодняшнего числа), я подставлю не ссылку на значение хранящееся в ячейке, а другую функцию. Эта функция называется СЕГОДНЯ() и её основная и единственная задача – возвращать сегодняшнюю дату.

А это уже полноценный калькулятор в MS Excel, учитывающий сегодняшнюю дату

А это уже полноценный калькулятор в MS Excel, учитывающий сегодняшнюю дату

Раз, и проблема решена – отныне, когда бы я не открыл этот лист MS Excel, функция РАЗНДАТ() всегда будет показывать мне точное значение, рассчитанное с учетом сегодняшней даты.

История секундомера – о первом приборе

Сотни лет назад люди проводили измерения по огню, расположению солнца на небосводе, тени. Такие измерения были неточными, а прообраз современного секундомера появился в 1750 г., чуть ранее – наручные часы. Модели имели только одну кнопку, которая отвечала за старт или остановку отсчета.

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

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

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

Переход в формат ячеек в Microsoft Excel

Форматирование как дата в Microsoft Excel

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

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

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

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

Даты готовы для операции в Microsoft Excel

Указание формата в Microsoft Excel

Установка общего формата в Microsoft Excel

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

Результат вычисления разности дат в Microsoft Excel

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

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

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

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

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

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

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

Функция РАЗНДАТ в Microsoft Excel

Результат функции РАЗНДАТ в Microsoft Excel

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

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

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

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

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

Переход в Мастер функций в Microsoft Excel

Переход к аргументам функции ЧИСТОРАБДНИ в Microsoft Excel

Аргументы функции ЧИСТОРАБДНИ в Microsoft Excel

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

Результат функции ЧИСТОРАБДНИ в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

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

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

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Читайте так же:
Как накрутить счетчик liveinternet

Как рассчитать дни между датами в таблицах Google

Прежде чем начать, важно отметить, что эти методы работают только при использовании формата даты в США. Если вы не в США, вы можете получить доступ к Google Sheets и изменить свои региональные настройки и часовой пояс, если хотите использовать эти методы.

С учетом вышесказанного давайте посмотрим, как найти количество дней между двумя датами в Google Sheets.

Функция МЕНЬШЕ

В отличие от Excel, Электронные таблицы Google Он имеет функцию вычитания, которая очень полезна для вычисления простых разностей дат. LESS – это функция вычитания листа, и, благодаря тому, как даты хранятся внутри (например, целые числа, описывающие количество дней, прошедших с данной даты в прошлом), она хорошо работает для вывода одной даты из другой. То есть, пока даты в одном и том же формате. Синтаксис для MINUS: = МЕНЬШЕ (значение 1, значение 2),

Чтобы использовать LESS, откройте пустую электронную таблицу Google Sheets в своем браузере. Введите (в качестве примера) «4 / / 4/2017» и «5 / / 15/2017» в ячейки B3 и C3.

Теперь выберите ячейку D3, куда мы поместим функцию МИНУС. Щелкните внутри панели «fx», затем введите «= LESS (C3, B3)» и нажмите Enter. Ячейка D3 теперь вернет значение 40, как показано ниже.

Это означает, что между 4 / 5/2017 и 5 / / 15/2017 существует 40 дней. Вы также можете найти разницу между датами, просто введя ссылки на ячейки и не беспокоясь о функции МИНУС.

Например, щелкните ячейку E3 и введите «C3-B3» на функциональной панели, как показано на снимке экрана непосредственно ниже. Это также вернет 40. Несмотря на то, что вы непосредственно вычитаете даты БЕЗ МИНУС, значение в ячейке E, вероятно, будет отображаться в формате даты и будет выглядеть очень странно.

Вы можете преобразовать формат ячейки для отображения целочисленного значения, выбрав формат > номер и номер,

Вы также можете сначала ввести ссылки на ячейки с предыдущей датой. Если вы ввели «= B3-C3» в функциональной панели, ячейка будет содержать значение -40. Это подчеркивает, что 4 / 4/2017 опаздывает на 40 дней 5 / 15/2017.

Функция DATEDIF

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

Синтаксис для DATEDIF:

DATEDIF (дата начала, дата окончания, единица измерения), Единицей функции может быть D (дни), M (месяцы) или Y (годы).

Читайте так же:
Счетчик открытия файла excel

Чтобы найти разницу между 4 / 4/2017 и 5 / / 15/2017 с помощью DATEDIF, необходимо выбрать ячейку для добавления функции (в нашем случае F3) и ввести «DATEDIF» на панели «fx». Затем разверните функцию квадратными скобками, которые включают ссылки на ячейки даты начала и окончания даты B3 и C3.

Единичные дни, иначе «D», также должны быть в конце функции. Таким образом, полная функция ‘= DATEDIF (B3, C3," D ")", которая возвращает значение 40, как показано ниже.

google3 даты

DATEDIF также будет работать, если вы введете информацию о дате непосредственно в формулу. Нажмите на ячейку в электронной таблице, чтобы добавить DATEDIF, а затем введите ‘= DATEDIF (« 4 / / 5/2017 «,» 5 / / 15/2017 ″, «D») 'на панели эффектов.

Это вернет 40 в выбранной ячейке, как показано ниже.

google4 даты

Функция DAY360

Google Sheets включает DAY360, который рассчитывает разницу между датами для 360-дневного года. 360-дневный календарь в основном используется для финансовых таблиц, где могут потребоваться расчеты процентных ставок.

Синтаксис для DAYS360:

= DAYS360 (дата начала, дата окончания, (метод)), (Метод) – это необязательный индикатор, который можно включить в метод подсчета дней.

Чтобы использовать эту функцию в электронной таблице Google Sheets на даты 1/1/2016 и 1 / / 1/2017, введите «1 / / 1/2016» в ячейку B4 в качестве даты начала и затем введите «1 / / 1/2017» в C4 в качестве даты окончания функции.

Теперь выберите ячейку D4, введите функцию ‘= DAYS360 (B4, C4)’ в строке «fx» и нажмите Enter. Тогда ячейка D4 будет включать в себя 360 дней между выбранными датами. Обратите внимание: единственное реальное использование этой конкретной функции – это если вы работаете с процентными ставками.

google5 даты

Функция NETWORKDAYS

NETWORKDAYS также вычисляет количество дней между датами, но оно не полностью совпадает с другими. Эта функция считает только дни недели, поэтому она не учитывает выходные. (Пожалуйста, прочитайте как «рабочие дни сети» вместо «дни сети»).

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

NETWORKDAYS (дата начала, дата окончания, (праздничные дни)),

Вы можете добавить эту функцию в свою электронную таблицу, указав даты выборки 4 / 4/2017 и 5 / / 15/2017 в ячейках B3 и C3. Выберите ячейку, в которую нужно включить сумму за день, и нажмите кнопку «fx», чтобы вставить функцию.

Введите ‘= NETWORKDAYS (B3, C3)’ и нажмите клавишу Enter, чтобы добавить функцию в ячейку таблицы, которую вы выбрали. Ячейка NETWORKDAYS будет включать в себя 29 для количества дней между датами.

Читайте так же:
Счетчик расхода топлива для авто

Чтобы добавить дату отпуска в функцию, сначала введите «4/17/2017» в ячейку A3. Выберите ячейку NETWORKDAYS, щелкните на панели валют и измените функцию, добавив ссылку на ячейку A3. Таким образом, функция будет = NETWORKDAYS (B3, C3, A3), которая вернет 28 с дополнительным выходным днем, также вычтенным из общего количества дней.

google6 даты

Другие важные функции, связанные с датой

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

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

Как считать количество рабочих дней между датами?

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

Функция ЧИСТРАБДНИ. Реквизиты и пример

Начнем, как всегда, с состава функции и ее реквизитов.

Состав реквизитов можно увидеть на начальной картинке

нач_дата — дата, с которой начинаем расчет.

кон_дата — дата, до которой нужно произвести расчет.

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

Если вам нужно отсчитать количество рабочих дней с заданной даты, пользуйтесь РАБДЕНЬ.

Функция РАБДЕНЬ. Реквизиты и пример

Количество рабочих дней между датами

Нач_дата — дата, с которой начинаем расчет.

число_дней — количество дней, которые должны пройти с начальной даты

праздники — такой же реквизит как и ЧИСТРАБДНИ

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

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

Для обоих функций, как вы заметили, есть реквизит ‘Праздники’. В нем можно указать, какие даты будут считаться праздниками (соответственно, не учитывая выходные). Например так:

chistrabdni2

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector