Evolcom.ru

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

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

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

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

Синтаксис

Аргументы функции CLEARRABDNY.MEAD описываются ниже.

Начальная_дата и конечная_дата Обязательные. Даты, между которыми должна быть рассчитана разница. Дата начала может быть до, во время или после даты окончания.

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

Цифры обозначают следующие выходные дни:

Число выходных дней

Выходной

Каждый день недели представлен строкой из семи символов (начиная с понедельника). Число 1 обозначает нерабочие дни, а число 0 — рабочие дни. В строке могут использоваться только символы 1 и 0. Если используется 1111111, то всегда будет возвращаться 0.

Например, 0000011 означает, что выходные дни — это суббота и воскресенье.

Необязательные праздники. Набор из одной или нескольких дат, которые должны быть исключены из календаря дней недели. Значение ‘holidays’ должно представлять собой массив ячеек, содержащих даты, или константу массива, содержащую порядковые значения, представляющие даты. Порядок дат или значений может быть произвольным.

Замечания

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

Если дата начала выходит за пределы диапазона для текущих данных, NETWORKDAYS. InTL возвращает значение ошибки #NUM !

Если дата конца_даты находится вне диапазона текущей даты, то функция NETWORKDAYS. Функция InTL возвращает значение ошибки #NUM!

Если строка weekend имеет недопустимую длину или содержит недопустимые символы, NETWORKDAYS. InTL возвращает #VALUE! значение ошибки #VALUE!

Пример

Скопируйте данные примера из приведенной ниже таблицы и вставьте их в ячейку A1 новой электронной таблицы Excel. Чтобы просмотреть результаты формул, выделите их и нажмите F2, а затем Enter. При необходимости вы можете изменить ширину столбцов, чтобы отобразить все данные.

Мы получаем 22 предстоящих рабочих дня. Общее количество дней между этими датами сокращается на девять (5 суббот и 4 воскресенья). Субботы и воскресенья по умолчанию считаются нерабочими днями.

Это дает -21, или 21 прошедший рабочий день.

Из 32 дней между 1 января 2006 года и 1 февраля 2006 года вычтите 10 выходных дней (4 пятницы, 4 субботы и 2 государственных праздника), чтобы получить 22 ближайших рабочих дня. Мы используем аргумент 7 в качестве праздничных дней, т.е. субботы и воскресенья. В этот период также отмечаются два государственных праздника.

Мы получаем 22 предстоящих рабочих дня. Тот же период времени, что и в примере выше, но четверги и субботы не работают.

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

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

Читайте так же:
Как переместить столбец / строку без замены / перезаписи существующих данных в Excel?

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

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

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

Определите диапазон, с которым вы хотите работать, и нажмите на маленькую стрелку рядом с «Number», если вы еще не сделали этого.

Стрелочка

В открывшемся слева окне выберите подходящий вам формат, а затем, в основной области, выберите тип: 14.03.12, 14 марта 12 года или другой. Нажмите OK .

Выбор формата

В ячейки, для которых вы только что изменили формат, введите данные. Я заполнил ячейки A1 и B1. Выберите ячейку D1, так как это единственная ячейка, которая имеет общий формат данных, иначе ваши вычисления будут неудачными. Щелкните сначала на поздней дате ( B1 ), а затем на ранней ( A1 ). Вы можете вычислить дни между ними, нажав «Enter».

В1-А1

Как вы видите, с 5 июня по 24 сентября насчитывается 111 дней.

Число 111

Способ 2: использование функции

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

Общий

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

«d» – количество дней;
«m» – количество полных месяцев;
«y» – количество полных лет;
«md» – посчитает дни без учета месяцев и годов;
«yd» – подсчет дней без учета только годов;
«ym» – посчитает месяцы не учитывая год.

Поставьте знак равенства в B3, введите DISNDATE и раскройте круглую скобку. Далее выделите начальную дату ( A1 ), затем конечную дату ( B1 ), заключите соответствующую единицу в перевернутые запятые и закройте скобки. Поставьте «;» между всеми аргументами. Для расчета нажмите «Enter».

У меня есть формула:

Функция разницы

Выбрав в качестве единицы «d» , я получила результат – 111.

Если изменить данное значение, например, на «md» , тогда формула посчитает разницу между 5 и 24 без учета месяцев и годов.

Меняя таким образом данный аргумент, получится вывести точный возраст человека. В одной ячейке будут года «y» , второй месяцы «ym» , третьей дни «md»

Только дни

Способ 3: считаем рабочие дни

Чтобы точно рассчитать количество рабочих дней в заданном периоде, воспользуйтесь функцией CLEANDAYS()

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

Читайте так же:
Как посчитать ячейки содержащие числа или нет в Excel?

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

Пример

Затем поставьте «=» и напишите функцию. Для меня это выглядит следующим образом:

A5 — дата начала, а B5 — дата окончания. Последний аргумент — праздники ( C5 ). Разделите их с помощью «;» .

ЧИСТРАБДНИ()

Нажатие кнопки «Enter» выведет результат, как показано в примере ячейки D5 — 21 день.

Теперь рассмотрим, больше ли праздничных дней в конкретном месяце. В январе, например, есть Новый год и Рождество. Выберите ячейку (D6) и поставьте в ней знак равенства. Затем нажмите на «f» в строке формул. Откроется окно функции «Вставка». В поле «Категория» выберите «Полный алфавитный список» и найдите нужную функцию в списке. Нажмите «OK» .

Список функций

Дальше необходимо выбрать аргументы функции. В «Нач_дата» выбираем начальное значение ( А6 ), в «Кон_дата» – конечное ( В6 ). В последнем поле вписываем даты праздников в скобках <> и кавычках «» . Потом нажимайте «ОК» .

Аргументы

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

D6

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

Диапазон

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

22 дня

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

В феврале было 19 рабочих дня.

Последний месяц

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

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

Рассчитать рабочие дни

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

Когда я показываю вам, как заполнять дни недели в своем семинаре по Power Excel довольно рано, я показываю, как щелкнуть правой кнопкой мыши по маркеру заполнения, перетащить дату, а затем выбрать «Заполнить дни недели». При этом заполняются даты с понедельника по пятницу. «Сколько из вас работает с понедельника по пятницу?» спрашиваю я аудиторию. Многие поднимают руки. Я говорю: «Это здорово. «Майкрософт» вряд ли заботится о других». Смех.

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

Однако две функции в Excel показывают, что команда Excel действительно заботится о людях, которые работают по нечетным рабочим неделям: NETWORKDAYS. INTL и WORKDAY. INTL.

Давайте вернемся к их первоначальным предшественникам — понедельнику-пятнице. На следующем рисунке начальная дата указана в B, а конечная — в C. Если вычесть =C5-B5 , то получим количество дней, прошедших между этими двумя датами. Количество дней недели можно рассчитать по формуле =NETWORKDAYS(B2,C2).

Читайте так же:
Как посчитать количество видимых листов в книге?

ЧИСТРАБДНИ Функция

Это становится еще лучше. В старой команде NETWORKDAYS имеется третий аргумент, указывающий рабочие праздники. На следующем рисунке праздники перечислены в H3: H15 позволяет подсчитать количество рабочих дней без праздников в столбце F.

Расчет рабочих дней без выходных

До Excel 2007 CLEAN и RABDEN были доступны, если была включена надстройка Analysis ToolPak, поставляемая со всеми копиями Excel. В Excel 2007 эти надстройки теперь являются частью ядра Excel. Microsoft добавила INTL-версии обеих функций с новым аргументом Weekend. Этот аргумент позволяет считать выходными два дня подряд, а также допускает однодневные выходные дни.

NETWORKDAYS.INTL

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

6 дней недели

Кроме того, в некоторых странах выходные дни не выпадают на субботу и воскресенье. Все показанные ниже страны, кроме Брунея-Даруссалема, получили функциональность с NETWORKDAYS. INTL и WORKDAY. INTL.

Загородные выходные

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

Я живу в том же округе, что и Зал славы профессионального футбола в Кантоне, штат Огайо. Но туристический объект номер один в нашем округе — это не Зал славы. Лучшим туристическим местом является рынок Хартвилл и блошиный рынок. Открытая в 1939 году, она является популярным местом для тех, кто ищет свежие продукты и выгодные предложения. Первоначально ресторан назывался Hartville Kitchen. А соседний хозяйственный магазин Хартвилла настолько велик, что внутри него построили целый дом. Но Рынок является бенефициаром нового секретного участка для рабочих и будних дней. Рынок открыт по понедельникам, четвергам, пятницам и субботам. Это означает, что выходными днями для него являются вторник, среда и воскресенье.

Начиная с Excel 2010, вместо использования 1-7 или 11-17 в качестве аргумента выходного дня можно передать семизначный двоичный текст, чтобы указать, открыто или закрыто предприятие в данный день. Это звучит немного необычно, но 1 используется для обозначения того, что магазин закрыт на выходные, а 0 — для обозначения того, что магазин открыт. В конце концов, 1 обычно означает «включено», а 0 — «выключено». Но аргумент называется Weekend, поэтому 1 означает, что сегодня выходной, а 0 означает, что у вас нет выходного.

Так, для расписания на понедельник, четверг, пятницу и субботу в Hartville Marketplace вы должны использовать «0110001». Каждый раз, когда я набираю одну из этих текстовых строк, мне приходится беззвучно повторять в голове: «Понедельник, вторник, среда», когда я набираю каждую цифру.

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

Торговые дни между двумя датами

Кстати, выше я не использовал необязательный аргумент «Праздники», потому что День памяти, Четвертое июля и День труда — самые популярные дни для посещения Хартвилла.

Если вы когда-нибудь будете в Северо-Восточном Огайо, обязательно загляните в Хартвилл, чтобы увидеть полностью американский дом в Hartville Hardware и попробовать отличную еду в Hartville Kitchen.

Смотреть видео

  • Математика дат в Excel: вычесть более раннюю дату из более поздней + 1
  • Чтобы не учитывать праздники, используйте ЧИСЛО
  • Чтобы не учитывать праздники, используйте третий аргумент ЧИСЛА
  • Для нестандартных праздников используйте NETWORKDAYS.INTL.
  • Семизначный секретный двоичный код для рабочих недель, которые не являются последовательными днями
  • Alt + ESF для вставки специальных формул.
Читайте так же:
Как подсчитать количество экземпляров, если длина строки больше X в Excel?

Стенограмма видео

Узнайте больше о Excel в эпизоде 2023 — Расчет рабочих дней даже для нестандартных рабочих недель!

Я запишу всю книгу в виде подкаста. Вы можете перейти к списку воспроизведения, нажав «i» в правом верхнем углу!

Приветствую вас, я Билл Джален, и добро пожаловать обратно в сеть. В эпизоде 1977 года «Заполнение дней недели» я рассказывал об этой функции. Дни недели заполняются, когда вы щелкаете правой кнопкой мыши на маркере заполнения, перетаскиваете его, отпускаете и выбираете «Заполнить дни недели». Эта функция отлично работает во многих странах мира, но ведь есть страны, где выходные не суббота-воскресенье, верно? К сожалению, Microsoft Excel не заботится о вас! Вот как найти рабочие дни между двумя датами; нам просто нужно знать, сколько дней между датами. Если вы работаете каждый день, мы можем вычесть более позднюю дату из более ранней на +1 и посмотреть, сколько рабочих дней между ними, верно? Это отлично работает, если вы работаете каждый день, верно? NETWORKDAYS раньше существовал, когда в инструменте анализа, вы знаете, долгое время он стал частью Microsoft Excel в Excel 2007. Если вы хотите избежать выходных, суббот и воскресений, вы можете использовать его.

Указывая начальную дату, запятую, конечную дату, он исключает субботы и воскресенья, но праздники все равно включаются в расчеты. Если мы хотим исключить праздники, мы используем = ЧИСЛО от начальной даты до конечной, запятую, а затем необязательный аргумент, где находится праздник. Хорошо, я выберу это, нажму F4, чтобы заблокировать его, и он рассчитает количество рабочих дней без праздников. Давайте посчитаем все это и скажем «Заполнить без форматирования», и теперь вы увидите, что есть 351 день, 251, если не включать субботу и воскресенье, но 239, если мы не включаем все праздники, так что это отличная, отличная функция.

Если вы находитесь в Соединенных Штатах или вообще не находитесь ни в одной из стран, перечисленных в строках 2-6, то во всех этих странах ваши выходные — пятница и суббота, суббота в Непале, четверг и пятница в Афганистане, здесь, в Иране, только пятница. А вот действительно злая страна, с которой будет трудно справиться, — это Бруней. Мне пришлось искать Бруней на карте! У вас есть выходные по пятницам и воскресеньям? Как это грустно, правда? Я не знаю, это однодневная рабочая неделя, наверное, я все равно не знаю, ладно. Если вам нужно посчитать выходные помимо субботы и воскресенья, как бы вы это сделали?

Вот завод, который работает с понедельника по субботу, так что единственным выходным у них является воскресенье, ну, думаю, они подарили нам Excel 2010 = NETWORKDAYS. INTL! Начинаем точно так же, вот начальная дата, вот конечная дата, и третий аргумент, мы указываем, какой выходной, что означает воскресенье. Для Брунея по-прежнему нет варианта, потому что это пятница и воскресенье, но для двух дней подряд или одного дня подряд теперь есть. Вы найдете ответ, нажав F4. Теперь вот праздники. Вы можете скопировать это, введя alt = «» ESF или Insert Special Formulas, хорошо? Итак, как умер леопард (? Или нестандартная рабочая неделя. Воскресенье и среда были днями, когда парикмахерские были закрыты. Хартвиллский рынок в Огайо — это блошиный рынок, на который мы ходили, замечательное место. Для тех, кто собирается посетить Зал славы профессионального футбола, он находится всего в 20 минутах езды, и открыт в понедельник, вторник, четверг и пятницу, верно?

Читайте так же:
Как переместить каждую вторую строку в столбец в Excel

Так что их выходные — вторник, среда или что-то в этом роде, как это сделать с NETWORKDAYS? Приятно, что была добавлена эта новая опция, которая не документирована в подсказке. Итак, NETWORKDAYS.INTL, здесь начальная дата, запятая, здесь конечная дата, запятая, а затем секрет, которого нет в выпадающем списке, — 7 двоичных цифр в кавычках! Комбинации из 0 и 1, начиная с понедельника, 1 означает, что сегодня выходной, 0 означает, что он открыт. Рынок в Хартвилле открыт по понедельникам, поэтому введите 0, он закрыт во вторник и среду, он открыт в четверг, пятницу и субботу, он закрыт в воскресенье, закройте кавычки. Я не знаю, здесь нет праздников, потому что если 4 июля выпадает на понедельник или четверг, это их самый большой день, у всех выходной, поэтому все летят сюда, и будет трудно найти парковку. Таким образом, фактическое количество рабочих дней между этими двумя датами я могу щелкнуть правой кнопкой мыши и заполнить без форматирования, хорошо, мне нравится этот секрет.

Когда вы зайдете на сайт Excel Help, вы найдете его, но если вы просто посмотрите на всплывающую подсказку, вы не узнаете, что он там есть, если у вас нет книги. И на странице 99 вы читали об этом или видели это видео, так что в любом случае это здорово. Нажмите на «i» в правом верхнем углу, чтобы купить книгу, $10 за электронную книгу, $25 за печатную книгу, все эти удивительные советы, 2,5-месячный подкаст — все это у вас на ладони. Итак, математика дат в Excel, вычтите более раннюю дату из более поздней +1, которая отсчитывает субботу и воскресенье. Используйте функцию ЧИСЛО, чтобы игнорировать выходные дни, и убедитесь, что третий аргумент в ЧИСЛО — это аргумент праздника. Для этого нажмите F4. Для нестандартных рабочих недель — NETWORKDAYS. Она дает вам возможность использовать любые 2 или 1 выходной день подряд. Также есть «секретный 7-бинарный код» для недель с непоследовательными рабочими днями, даже в Брунее это обрабатывается.

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

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