Функция РАБДЕНЬ() в EXCEL

Автор: | 29.12.2021
Нет комментариев

Функция РАБДЕНЬ.

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

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

РАБОЧИЙ ДЕНЬ — встроенная функция в Excel 2007, 2010, 2013 и 2016. Вы можете ввести его вручную или с помощью мастера. В предыдущих выпусках вам нужно было специально включить набор инструментов анализа.

вам нужно использовать следующие аргументы:

РАБОЧИЙ ДЕНЬ (начальная_дата; дни; [будние_дни])

Первые два являются обязательными, а последний — необязательным:

  • Start_Date — отсчет дней.
  • Дни — это дни недели, которые нужно добавить или вычесть из начальной точки. Положительное число возвращает будущий момент, отрицательное число возвращает прошлое.
  • Праздники — это необязательный список дат, которые считаются нерабочими днями. Это может быть диапазон ячеек, содержащий даты, которые вы хотите исключить из вычислений, или константу массива.

Синтаксис

= РАБДЕНЬ (начальная_дата, дни, [праздники]) — английская версия

= РАБДЕНЬ (начальная_дата; количество дней; [праздники]) — русская версия

Аргументы функции

  • start_date (start_date) — дата, которую мы берем за отправную точку;
  • days — общее количество рабочих дней без выходных. В аргументе можно использовать как положительные, так и отрицательные числа;
  • [праздники] — (необязательно) это диапазон дат, который вы хотите исключить из расчета. Например, это может быть любой национальный праздник. Кроме того, это может быть ссылка на диапазон ячеек, содержащих даты, или массив порядковых номеров, содержащих даты.

Что возвращает функция

Свидание через определенное количество рабочих дней.

Как добавить или вычесть рабочие дни.

Чтобы рассчитать рабочие дни в Excel, следуйте этим простым правилам:

  • Чтобы добавить дни недели, введите положительное число в качестве аргумента дней в формуле РАБОЧИЙ ДЕНЬ.
  • Чтобы вычесть дни недели, используйте отрицательное число.

Предположим, у нас есть начальная точка времени в ячейке A10 и список праздников в ячейках A2: A7. Вы хотите знать, какая дата будет 90 рабочими днями в будущем и в прошлом.

Сделать это можно по следующим формулам:

Чтобы добавить такую ​​же сумму к начальной точке, но с учетом праздников в A2: A7:

= РАБОЧИЙ ДЕНЬ (10 австралийских долларов; 90 австралийских долларов; 2 австралийских доллара: 7 австралийских долларов)

Чтобы вычесть 90 рабочих дней без учета праздников:

= РАБОЧИЙ ДЕНЬ (10 австралийских долларов; -90, 2 австралийских доллара: 7 австралийских долларов)

Чтобы рассчитать сроки выполнения на основе текущей даты, используйте функцию СЕГОДНЯ () в качестве отправной точки:

Чтобы добавить 90 рабочих дней:

= РАБОЧИЙ ДЕНЬ (СЕГОДНЯ (), 90; 2 доллара США: 7 долларов США)

Чтобы вернуться на тот же временной интервал с сегодняшнего дня:

= РАБОЧИЙ ДЕНЬ (СЕГОДНЯ (), — 90)

Чтобы указать время начала непосредственно в формуле, используйте формулу ДАТА():

= РАБОЧИЙ ДЕНЬ (ДАТА (2020, 1, 7), 90; 2 австралийских доллара: 7 австралийских долларов)

На следующем снимке экрана показаны результаты всех этих и некоторых других формул РАБОЧИЙ ДЕНЬ:

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

= РАБОЧИЙ ДЕНЬ (10 австралийских долларов; С1; 2 австралийских доллара: 7 австралийских долларов)

Где A10 — дата начала, а C1 — количество предыдущих (отрицательные числа) или следующих (положительные числа) рабочих дней, за исключением праздников.

Настраиваем свой график выходных дней.

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

он появился в Excel 2010, но недоступен в более ранних версиях программы.

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

Его синтаксис следующий:

РАБДЕНЬ.INTL (дата_пуска, дни, [выходные], [праздники])

Первые два аргумента обязательны и нам знакомы:

Start_Date — это начальная точка во времени.

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

Последние два аргумента необязательны:

Выходные: указывает, какие дни отдыха следует учитывать. Это может быть число или текст, как показано ниже.

Число

Выходные

1 или отсутствует

Суббота Воскресенье

2

Воскресение понедельник

3

Понедельник вторник

4

Вторник Среда

5

Среда Четверг

6

Четверг Пятница

7

Пятница суббота

одиннадцать

Только воскресенье

12

Только понедельник

13

Только вторник

14

Только среда

15

Только четверг

16

Только пятница

17

Только суббота

Текст выходного дня представляет собой серию из семи нулей и единиц, представляющих семь дней недели, начиная с понедельника. 1 означает нерабочий день, а 0 — соответственно наоборот. Например:

  • «0000010» — суббота — отдыхаем.
  • «1000001» — понедельник, воскресенье — отдых.

А вот как это может выглядеть на примере:

Рекомендую обратить внимание на дизайн

= РАБДЕНЬ.INTL (10 австралийских долларов; 90; «1000001 ″; 2 австралийских доллара: 7 австралийских долларов)

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

Все остальное, думаю, из скриншота достаточно ясно.

Комментарий. Функции Excel РАБОЧИЙ ДЕНЬ и РАБОЧИЙ ДЕНЬ.INTL возвращают порядковые числа (числа), представляющие даты. Чтобы они отображались так, как нужно, выберите нужные ячейки и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Дата» и определите желаемый формат.

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

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

Функция РАБДЕНЬ

  1. Функция РАБДЕНЬ почти противоположна функции ЧИСТРАБДНИ. Возвращает дату до или после указанного количества дней недели (кроме выходных).

    = РАБОЧИЙ ДЕНЬ (A1; B1)
    = РАБОЧИЙ ДЕНЬ (A1; B1)

Примечание: функция РАБДЕНЬ возвращает порядковый номер даты. Примените формат даты к ячейке, чтобы отобразить ее.

Календарь ниже поможет вам лучше понять функцию РАБОЧИЙ ДЕНЬ).

Опять же, если вы предоставите список праздников, функция РАБОЧИЙ ДЕНЬ вернет дату до или после указанного количества рабочих дней (исключая выходные и праздничные дни).

Складывать и вычитать календарные дни

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

Например, в налоговом учете необходимо создать резерв по сомнительным долгам. В том числе необходимо рассчитать, когда у покупателя есть задолженность сроком 45 дней с момента продажи. Для этого введите дату отгрузки в ячейку. Например, это ячейка D2. Таким образом, формула будет выглядеть так: = D2 + 45. Аналогичным образом вычитаются дни. Главное, чтобы ячейка с датой, к которой вы добавите число, имела правильный формат. Чтобы проверить это, щелкните ячейку правой кнопкой мыши, выберите «Форматировать ячейки» и убедитесь, что выбран формат «Дата».

Как выглядит формат ячейки в Excel

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

Например, нужно посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки и введите знак «=» в отдельной ячейке. Затем вычтите ноябрьскую дату из декабрьской. Получится 56 дней. Помните, что в этом случае будет засчитан последний день, но не будет засчитан первый день. Если вам нужно, чтобы сумма включала оба дня, добавьте один в формулу. Если, с другой стороны, вам нужно посчитать количество дней без учета обеих дат, вам нужно вычесть единицу.

Сделать ячейку, в которой всегда стоит текущая дата

Введите в ячейку функцию СЕГОДНЯ, и она всегда будет содержать текущую дату. Для правильной работы функции необходимо учитывать два условия. Сначала проверьте правильность даты на вашем компьютере. Программа учтет это. Во-вторых, укажите в настройках Excel, чтобы формулы пересчитывались автоматически. Для этого в верхнем левом углу открытого листа Excel нажмите «Файл». В открывшемся меню выберите «Параметры», затем вкладку «Формулы» и убедитесь, что в столбце «Расчеты в книге» выбран параметр «Автоматически».

В ячейке содержится Дата

Столбец A содержит значения в формате даты, например, в ячейке A6 — 13.08.2012.

Чтобы настроить правило условного форматирования, вам необходимо:

  • диапазон яркости A6: A14
  • выберите пункт меню Главная / Стили / Условное форматирование / Создать правило…
  • выберите тип правила Используйте формулу для определения отформатированных ячеек
  • в поле Формат значений, для которого действительна следующая формула, введите формулу = ДЕНЬ НЕДЕЛИ (A6; 2)> 5
  • задать формат, например, красную заливку фона ячейки.

Даты в столбце A с днем ​​недели> 5 будут выделены красным.

В ячейке с пользовательским форматом ДДД или ДДДД содержится Дата

 

Пусть столбцы B и C также содержат значения в формате даты, например, в ячейку B6 введите формулу = A6 .

Для ячеек в диапазоне B6: B14 установите настраиваемый формат DDD (для диапазона C6: C14 DDDD).

Хотя теперь в ячейках отображаются понедельник и понедельник, значения в ячейках по-прежнему имеют формат даты. Вы можете проверить это, скопировав значение (CTRL + V), например, из ячейки C6 и вставив его как значение (Home / Clipboard / Paste / Paste value). В строке формул будет отображаться 13 августа 2012 г., а не понедельник. Следовательно, вам необходимо настроить правило условного форматирования.

В ячейке содержится Число

Пусть столбец D содержит номер дня недели, например, в ячейку D6 введите формулу = ДЕНЬ НЕДЕЛИ (A6; 2) .

В этом случае правило условного форматирования настраивается аналогично случаю, когда ячейка содержит дату, только формула несколько упрощена = $ D6> 5

Пошаговая инструкция создания календаря в Excel

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

Делаем заголовки

Введите следующую формулу в ячейку A1: = «Календарь на» & ГОД (СЕГОДНЯ ()) & «год». Функция СЕГОДНЯ () возвращает текущую дату (мы будем часто ее использовать). В результате группа функций ГОД (СЕГОДНЯ ()) вернет нам текущий год.

Теперь создадим заголовки месяцев. В ячейке, где находится январь, напишите следующую формулу = ДАТА (ГОД (СЕГОДНЯ ()); 1; 1). Эта функция возвращает 1 января текущего года (в моем случае 01.01.2015). Отформатируйте эту ячейку так, чтобы отображалось только название месяца. Для этого откройте формат ячейки, нажав Ctrl + 1, и выберите соответствующий формат (см. Рисунок).

 

Для создания заголовков следующих месяцев мы будем использовать еще одну очень полезную функцию = ДАТА (B3; 1). Эта функция возвращает дату, равную указанному количеству месяцев (в нашем случае — одному) от указанной даты. Итак, мы получаем следующее изображение (к тому же я добавил немного форматирования):

 

Проставляем числа

Для автоматического добавления чисел в календарь мы будем использовать только одну формулу — формулу массива. Звучит устрашающе, но если разобраться, все легко и просто. Выделите ячейки B5: H10 и введите следующую формулу (не спешите нажимать Enter):
= ДАТА (ГОД (B3); МЕСЯЦ (B3); 1–1) — (ДЕНЬ НЕДЕЛИ (ДАТА (ГОД (B3), МЕСЯЦ (B3), 1–1)) — 1) + {0: 1: 2: 3: 4: 5: 6} * 7 + {1; 2; 3; 4; 5; 6; 7}

Вместо Enter нажмите комбинацию клавиш Ctrl + Shift + Enter, чтобы ввести формулу массива. В результате в выбранном диапазоне мы получаем дату, указанную в ячейке B3 месяца.

Мы выполняем аналогичную операцию с другими месяцами или просто копируем диапазон дат января в следующие месяцы. Получаем такую ​​картинку:

 

Добавляем условного форматирования

Теперь в январе календарь содержит даты 29, 30, 31 декабря и часть февраля. Давайте немного спрячем эти цифры. Для этого воспользуемся условным форматированием. Выделите ячейки B5: H10 и создайте правило условного форматирования. Ячейки, для которых следующая формула истинна, будут отформатированы:
= И (МЕСЯЦ (B5) 1 + 3 * (ЧАСТНАЯ (СТРОКА (B5) -5.9)) + ЧАСТНАЯ (СТОЛБЦА (B5); 9))

Я выбрал в качестве формата текст серого шрифта и получил следующее изображение:

 

Автоматически выделяем текущую дату в календаре

Для этого создадим еще одно правило условного форматирования. Ячейка будет отформатирована только в том случае, если ее значение равно текущей дате (= СЕГОДНЯ()):

 

Подготовка ячеек для работы с датами

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

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

Далее откроется окно, в котором нужно выбрать формат «Дата», тип формата, который будет использоваться, и нажать кнопку «ОК».

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

Подсчет количества дней с помощью простой формулы

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

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

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

После ввода формулы нужно нажать клавишу Enter и вы получите результат.

Как видите, в этом случае количество дней между двумя датами равно 41.

Второй способ: определение часов через временной промежуток

Предположим, у нас есть время 11 часов 45 минут, мы хотим знать, сколько оно будет через 2 часа 15 минут. Мы будем следовать инструкциям, чтобы получить результат.

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

Активируем пустую ячейку, переходим в строку формул, где ставим знак «=» без кавычек. Нажмите на ячейку с текущим временем, поставьте знак «+», добавьте соседнюю ячейку. Нажмите кнопку «Enter». В результате вы получаете время, которое будет через определенный период (в нашем случае через 2 часа 15 минут).

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