Evolcom.ru

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

Как подсчитать количество дней, недель, месяцев или лет между двумя датами?

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

Для расчета и отображения времени между датами в днях, месяцах и годах можно использовать формулу, основанную на функции RAINDATE.

Получить дни, месяцы и годы между датами

В показанном примере формула для D5 :

Где даты начала указаны в столбце B, а даты окончания — в столбце C.

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

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

Чтобы получить все месяцы, мы используем:

И мы вычисляем дни с помощью

Остальная часть формулы — это просто необходимый текст, объединенный с оператором конкатенации (&).

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

Чтобы избежать лишних запятых в конечном результате, запятые заменяются пробелами. Использование оригинальной формулы RAZNDAT в качестве «логического теста» в IF работает, потому что IF рассматривает любой ненулевой результат как истинный.

Подсчитайте дни недели между датами

Для подсчета количества понедельников, пятниц, воскресений и т.д. между двумя датами можно использовать формулу массива, которая использует несколько функций: SUMPROIZE, DAYNED, STRING и DIVSYL.

Подсчитайте дни недели между датами

В приведенном примере формула в ячейке E5 имеет следующий вид:

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

D AYNED возвращает число от 1 до 7, соответствующее дню недели, когда указана дата. По умолчанию 1 соответствует воскресенью, а 7 — субботе. Таким образом, 2 = понедельник, 6 = пятница и так далее.

Трюк этой формулы заключается в понимании того, что даты в Excel — это только серийные номера, которые начинаются 1 января 1900 года. Например, 1 января 2016 года — это серийный номер 42370, а 8 января — 42377. Даты в Excel выглядят только как даты, когда применяется формат числа даты.

Итак, возникает вопрос — как вы можете построить массив дат, который вы можете использовать в функции ДЕНЬНЕД, чтобы узнать соответствующие дни недели?

Ответ: Используйте STRIKE с DWSSYL следующим образом:

Связанные даты «42370: 42377-9» можно интерпретировать как номера строк при использовании D VSSL. STRING возвращает массив, подобный этому:

Читайте так же:
Как очистить стиль форматирования таблицы без потери данных таблицы в Excel?

Функция DAYNED использует эти числа в качестве дат и возвращает следующий массив:

Который проверяется для заданного дня недели (в данном случае 6, начиная с D6). После преобразования результатов теста в 1 и 0 с помощью двойных линий этот массив обрабатывается программой SUMPROVE:

Который возвращает 2.

Подсчитывать праздники между двумя датами

= SUMMARY((праздники> = начало) * (праздники<= конец))

Функция СУММА ПРАЗДНИКОВ может использоваться для расчета праздников, которые приходятся на период между двумя датами.

Подсчитывать праздники между двумя датами

В показанном примере формула находится в поле F7:

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

Первое выражение проверяет каждую праздничную дату на то, что она больше или равна дате начала F5 :

Эта функция возвращает массив значений True/False, например, :

Каждая дата отпуска проверяется на то, что она меньше или равна дате окончания в F6, используя следующее выражение:

Эта функция возвращает массив значений true/false в следующем виде:

Умножение этих двух массивов автоматически присваивает единицам и нулям значения TRUE/false, в результате чего получаются массивы, выглядящие следующим образом

Умножение дает только один массив:

И наконец, команда SUMMPROIZE суммирует элементы массива и возвращает 4.

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

Где диап — диапазон, содержащий даты отпуска.

Создание диапазона дат с двух дат

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

Создание диапазона дат с двух дат

В приведенном примере формула размещена в ячейке E5:

Функция ТЕКСТ принимает числовые значения и преобразует их в текстовые значения, используя заданный вами формат. В этом примере мы используем формат «ммм д» для обеих функций TEКСT в E5. Результаты объединяются дефисом, используя простую конкатенацию.

Примечание: В других примерах в колонке Е используется другое форматирование текста.

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

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

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

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

Эта формула просто возвращает пустую строку («»), когда дата1 недоступна.

Читайте так же:
Как подсчитать ячейки между двумя точками в Excel?

Получать рабочие часы между датами

= ЧЕТРАБДНЕЙ (начало; окончание; праздники) * часы

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

Получать рабочие часы между датами

В приведенном примере формула для D5 :

Эта формула использует функцию ЧИСЛО для определения общего количества рабочих дней между двумя датами с учетом выходных и (необязательных) праздников.

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

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

C LEARRABDNEY включает как начальную, так и конечную дату, и исключает субботы и воскресенья.

Получать рабочие часы между датами собственного расписания

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

Получать рабочие часы между датами собственного расписания

В приведенном примере формула находится в F5 :

Который возвращает 32 часа, основанный на пользовательском расписании, где 8 часов работают в Пн, Ср и Пт, а 4 часа — в Пт и Вт.

Предупреждение. Это формула массива, которую необходимо ввести с помощью Control + Shift + Enter.

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

DAYNED возвращает число от 1 до 7, соответствующее определенному дню недели. При настройке по умолчанию: 1 = воскресенье и 7 = суббота, поэтому 2 = понедельник, 6 = пятница и так далее.

Хитрость этой формулы заключается в создании массива дат, который можно использовать в функции DAYNED. Это делается с помощью STRING с DVSSYL следующим образом:

ДВССЫЛ позволяет связанным датам «» 42464: 42470 «» интерпретироваться как номера строк. Затем функция СТРОКА возвращает массив вроде этого:

Функция DAYNED оценивает эти числа как даты и возвращает массив:

Числа соответствуют дню недели каждой даты.

Эта таблица передается в функцию SELECT в качестве «индексного номера», который был установлен на 7 значений:

Значение 1 — Рабочие дни Воскресенье

Значение 2 — рабочие часы в понедельник

Значение 5 — рабочие часы в четверг

Значение 6 — рабочие часы в пятницу

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

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

Наконец, чтобы получить общее значение, нам нужно просто поместить SELECT в SUM и подставить формулу массива.

Читайте так же:
Как подсчитать уникальные значения или числа в столбце в Excel?

В данном примере конечный результат — 32 часа.

Примечание: вы можете подумать, что можно использовать SUMMPROIZV вместо SUMM, чтобы избежать синтаксиса массива, но это не работает; вам все равно придется использовать CSE. DVSSYL с STRIKE используется для построения массива индексных значений, но я не уверен, почему для этого требуется CSE.

Получать рабочие дни между датами

= ЧИСЛО(дата начала; дата окончания; государственные праздники)

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

Получать рабочие дни между датами

Если вы введете 4 января 2016 года (понедельник) в ячейку B4 и 11 января 2016 года (также понедельник) в ячейку C4, эта формула вернет значение 6:

Обратите внимание, что ЧИСЛО включает в расчет как начальную, так и конечную даты, если они относятся к рабочим дням.

ЧИСЛО может также исключать настраиваемый список праздников. Например, если у вас есть праздничные даты в H1:H10, вы можете сказать ЧИСЛУ не включать эти даты в качестве рабочих дней, добавив этот диапазон в качестве третьего аргумента в формулу:

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

Список праздников между двумя датами

Вы можете использовать формулы на основе функций ОБЪЕКТ и ЕСЛИ для отображения праздников, которые приходятся на период между двумя датами.

В примере формула находится в поле F8:

Это формула массива, которую необходимо ввести с помощью комбинации клавиш Control+Shift+Enter.

На базовом уровне эта формула использует вложенную функцию IF, чтобы вернуть массив праздников между двумя датами. Затем этот массив обрабатывается функцией ОБЪЕКТ, которая преобразует массив в текст, используя в качестве разделителя запятую.

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

Если даты в B4:B12 больше или равны начальной дате в F5 и меньше или равны конечной дате в F6, то IF возвращает массив праздников. Вот как выглядит список в примере:

Этот массив затем доставляется в функцию ОБЪЕДИНИТЬ в качестве аргумента текст1, где разделитель имеет значение «,», а игнорировать_пустые — ИСТИНА. Функция ОБЪЕДИНИТЬ обрабатывает элементы в массиве и возвращает строку, где каждый непустой элемент разделяется запятой и пробелом.

Читайте так же:
Как преобразовать дату в порядковый формат даты в Excel?

Как вычесть даты в Excel

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

Вычислить разницу между датами в Excel просто, но это может стать сложной задачей в зависимости от того, как вы хотите вычислить определенные значения. Например, если вы хотите узнать количество месяцев между 5/5/2016 и 15.01.2016, ответ должен быть 0 или 1? Некоторые люди могут сказать 0, потому что между двумя датами нет полного месяца, а другие могут сказать 1, потому что это другой месяц.

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

Дни между двумя датами

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

дни между датами

В моем примере выше у меня есть две даты, хранящиеся в ячейках A2 и A3. Справа вы видите разницу между этими двумя датами — 802 дня. В Excel всегда есть несколько способов вычислить одно и то же значение, и это то, что я сделал здесь. Давайте посмотрим на формулы:

рассчитывать дни превосходят

Первая формула представляет собой простое вычитание двух дат, A3 — A2. Excel знает, что это дата, и просто вычисляет количество дней между этими двумя датами. Легко и очень просто. Кроме того, вы также можете использовать ДНИ функция.

Эта функция принимает два аргумента: дату окончания и дату начала. Если вы измените даты в формуле, вы получите только отрицательное число. Наконец, вы можете использовать функцию DATEDIF, которая была включена в Excel со времен Lotus 1-2-3, но не является официально поддерживаемой формулой в Excel.

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

Что если вам также нужно преобразовать количество дней недели между двумя датами? Это легко сделать, поскольку существует встроенная формула:

Подсчет дней и будней достаточно прост, давайте поговорим о месяцах.

Читайте так же:
Как поменять местами имя и фамилию в ячейках в листе Google?

Месяцы между двумя датами

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

Поскольку это бессмысленно, мы рассмотрим формулы, которые дают нам правильный ответ. Компания Microsoft предоставила их, но если вам лень переходить по этой ссылке, я также приведу их ниже.

Это две довольно длинные и сложные формулы, и вам не нужно понимать, что происходит. Вот формулы в Excel:

месячная разница форумла

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

показать формулы

В результате, каков результат двух формул в нашем примере? Давайте посмотрим ниже:

разница месяцев

Округление дает 27 месяцев, а округление вниз — 26 месяцев, что достаточно точно, в зависимости от того, как на это посмотреть. Наконец, вы также можете использовать функцию DATEDIF, но она вычисляет только полные месяцы, поэтому в данном случае ответ будет 26.

Годы между двумя датами

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

формула разницы в год

Одна формула использует DATEDIF, а другая — функцию YEAR. Поскольку разница в количестве дней составляет всего 802, DATEDIF показывает 2 года, тогда как функция YEAR показывает 3 года.

разница в год

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

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

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