Excel, если дата праздничная, то
Обновлено: 03.07.2024
Часто бывает полезно вернуть дату праздника для заданного года, например, для приложения-расписания. Ни в Excel, ни в VBA нет встроенных функций для работы с праздниками — приходится создавать свои. Праздники можно рассматривать как фиксированные или плавающие. Фиксированные праздники — это те, которые приходятся на один и тот же день каждый год, например, Рождество. Плавающие праздники — это те, которые приходятся на разные дни в разные годы. Например, День Благодарения (в США) приходится на четвертый четверг ноября. Поэтому нам нужна функция, которая будет вычислять четвертый четверг. Мы обобщим эту функцию как в VBA, так и в форме функции рабочего листа, чтобы возвращать N-й день недели для любого месяца и года.
Другие плавающие праздники рассчитать немного сложнее. Например, в США День памяти отмечается в последний понедельник мая. В зависимости от года это может быть либо 4-й, либо 5-й понедельник. Итак, нам нужна функция для подсчета количества понедельников в мае. Мы обобщим это, чтобы вычислить число любых дней недели в любом месяце и году.
Наконец, есть Пасха, фактическая дата которой является каким-то причудливым результатом фаз луны. Я не претендую на авторство приведенных ниже формул для расчета Пасхи и не утверждаю, что понимаю, почему они работают, но они работают.
Для фиксированных праздников, таких как Рождество, это просто, поскольку дата праздника не меняется из года в год. Например, используйте следующую команду, чтобы получить дату Рождества в текущем году:
=ДАТА(ГОД(СЕЙЧАС()),12,25)
Однако другие праздники не привязаны к определенной дате. Например, День благодарения определен как 4-й четверг ноября. Поэтому его точная дата будет меняться из года в год. Для Дня Благодарения у нас есть явная функция VBA:
Открытая функция ThanksgivingDate(Yr As Integer) As Date
ThanksgivingDate = DateSerial(Yr, 11, 29 - _
WeekDay(DateSerial(Yr, 11, 1), vbFriday))
Завершить функцию
Мы можем обобщить это на праздники, которые определяются как N-й день некоторого месяца, например день рождения Мартина Лютера Кинга, отмечаемый в 3-й понедельник января. Следующая функция вернет N-й день недели для заданного месяца и года:
Открытая функция NDow(Y как целое число, M как целое число, _
N как целое число, DOW как целое число) как дата
NDow = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), _
(DOW + 1) Mod 8)) + ((N - 1) * 7))
Чтобы получить дату 3-го понедельника января 1998 года, используйте
=NDow (1998, 1, 3, 2)
Функция NDow также может быть записана в виде формулы рабочего листа:
=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)
+(DoW-WEEKDAY(DATE(Yr, Пн,1))))
Где Yr, Mon и DoW — ссылки на ячейки или значения, обозначающие год, месяц, N и день недели.
Это подойдет для большинства плавающих праздников. Однако День памяти отмечается в последний понедельник мая. Чтобы вычислить эту дату, нам сначала нужна функция, которая сообщает нам, сколько понедельников в месяце.
Открытая функция DOWsInMonth(Yr As Integer, M As Integer, _
DOW As Integer) As Integer
При ошибке GoTo EndFunction
Dim I As Integer
Dim Lim As Integer
Lim = Day(DateSerial(Yr, M + 1, 0))
DOWsInMonth = 0
For I = 1 To Lim
If WeekDay(DateSerial(Yr, M, I)) = DOW Then
DOWsInMonth = DOWsInMonth + 1
End If
Next I
Выйти из функции
EndFunction:
DOWsInMonth = 0
Конец функции
Вызов этой функции сообщит нам, сколько понедельников в мае 1998 года.
=DOWsInMonth(1998, 5, 2)
=СУММ((ДЕНЬ НЕДЕЛИ(ДАТА(B3,C3,(СТРОКА(ДВССЫЛ
("1:"&ДЕНЬ(ДАТА(B3,C3+1,0))))))) =D3)*1)
где B3 – год, C3 – месяц, а D3 – день недели (1=воскресенье, 2=понедельник, . 7=суббота)
В мае 1998 года 4 понедельника. Мы можем передать это в функцию NDOW, чтобы вернуть 25 мая 1998 года, дату Дня памяти. Таким образом, формула для возврата даты Дня памяти в 1999 году будет следующей:
=NDow(1999,5,DowsInMonth(1999,5,2),2)
Многие организации признают даты выходных дней отличными от фактической даты. Как правило, это делается, когда праздничный день приходится на выходной, а праздничные дни соблюдаются, чтобы сделать выходные трехдневными. Общее правило состоит в том, что если праздник приходится на субботу, он отмечается в пятницу перед праздником. Если праздник выпадает на воскресенье, он отмечается в следующий понедельник. Следующая формула вернет пятницу, если праздничный день приходится на субботу, понедельник, если праздничный день приходится на воскресенье, или саму дату, если она приходится на будний день. =ЕСЛИ(ДЕНЬНЕД(A2, 1)=1,A2+1,ЕСЛИ(ДЕНЬНЕД(A2,1)=7,A2-1,A2)) где A2 — дата праздника. В VBA вы можете использовать следующую функцию:
где TheDate — дата праздника.
Расчеты Пасхи теперь находятся в Расчетах Пасхи.
Другие процедуры, связанные с датой и временем, описаны на следующих страницах.
Я хочу проверить, является ли дата праздником или нет в Excel, если "праздник", то конкретная "ячейка даты праздника" связана с новой формулой, которая выполняет новую функцию. Это возможно? Заранее спасибо, ребята!
Вы можете использовать IF(), поэтому небольшой пример =if(A1>B1,do_holiday,not_holiday) , do_holday — это функция, которую вы хотите и т. д.
Как узнать, является ли свидание праздником? Содержится ли где-нибудь на листе список дат праздников? Какая новая формула? Не могли бы вы привести минимальный воспроизводимый пример, пожалуйста?
Привет. Спасибо за быстрые ответы. Я признателен за это. Кстати, я использую эту формулу, чтобы проверить, является ли введенная дата праздником или нет -> = ЕСЛИ (ИЛИ ($ B $ 2 = A12), «Праздник», «Не праздник»). Мой вопрос заключается в том, возможно ли, что конкретная ячейка даты, которая проверена как праздник, содержит формулу в той же ячейке, что и ячейка «дата праздника»? т. е. 1 января 2019 г. -> строка в ячейке (может ли она содержать новую формулу после строки?)
В этой формуле оператор ИЛИ не нужен: сработает =ЕСЛИ($B$2=A12;"Праздник", "Не праздник"). Не понимаю, что вы имеете в виду под возможно ли, что конкретная ячейка даты, которая проверена как праздник, содержит формулу в той же ячейке, что и ячейка «дата праздника». Можете ли вы отредактировать исходный вопрос, добавив дополнительную информацию и снимки экрана?
Привет. Спасибо. только что загрузил скриншот. то есть мой вопрос, возможно ли, что ячейка от 1 января 2019 года (B2) содержит другую формулу, которая может выполнять новую функцию? Еще раз спасибо.
2 ответа 2
Можно СЧИТАТЬ, ЕСЛИ анализируемая дата входит в диапазон праздничных дат. Если это значение count= 0, значит это не праздник.
Попробуйте что-нибудь вроде:
Или =IF(NETWORKDAYS.INTL($A14,$A14,1,$B$2:$B$3)=0,"Праздник","Не праздник"), если вы хотите включить субботу и воскресенье в качестве выходного дня . Я не думаю, что проблема в этом.
Нас не волнует, где дата появляется в списке праздников, нам просто нужно знать, появляется ли она вообще. Таким образом, мы можем обернуть СОВПАДЕНИЕ в ISNA, а затем поместить его в ЕСЛИ:
Я не совсем понимаю, что вы имеете в виду, когда говорите, что хотите, чтобы конкретная ячейка с датой "праздник" выполняла другую функцию в Excel.
Однако я предполагаю, что вы просто хотите, чтобы ячейка отображала что-то другое, кроме "Праздник", в зависимости от формулы. Если это так, вы можете просто добавить эту формулу вместо текста «Праздник»:
В этом примере показано, как получить дату праздника для любого года (2022, 2023 и т. д.). Если вы спешите, просто загрузите файл Excel.
Перед началом: функция ВЫБОР возвращает значение из списка значений на основе номера позиции. Например, =ВЫБОР(3,"Автомобиль","Поезд","Лодка","Самолет") возвращает Лодка. Функция ДЕНЬНЕД возвращает число от 1 (воскресенье) до 7 (суббота), представляющее день недели даты.
<р>1. Вот так выглядит электронная таблица. Если вы введете год в ячейку C2, Excel вернет все праздники в этом году. Конечно, Новый год, День независимости, День ветеранов и Рождество — это легко.
Приведенный ниже календарь поможет вам понять День благодарения 2025 года.
Объяснение: DATE(C2,11,1) сокращается до 01.11.2025. WEEKDAY(DATE(C2,11,1)) сокращается до 7 (суббота). Теперь формула сводится к 01.11.2025 + 21 + ВЫБРАТЬ(7,4,3,2,1,0,6, 5 ) = 01.11.2025 + 21 + 5 = 27.11.2025. Нам понадобились 5 дополнительных дней, потому что до первого четверга ноября осталось 5 дней. Оттуда проходит еще 21 день (3 недели) до 4-го четверга ноября. Неважно, на какой день приходится 1 ноября, функция ВЫБОР правильно добавляет количество дней до первого четверга ноября (обратите внимание на шаблон в списке значений). Оттуда всегда проходит еще 21 день до 4-го четверга ноября.Следовательно, эта формула работает для каждого года.
<р>3. Давайте посмотрим на День Мартина Лютера Кинга младшего. Эта формула почти такая же. День Мартина Лютера Кинга-младшего отмечается в 3-й понедельник января. На этот раз первая функция DATE сводится к первому января. Базовая позиция (0) в списке значений функции ВЫБОР теперь находится на втором месте (мы ищем понедельник)
Приведенный ниже календарь поможет вам лучше понять день Мартина Лютера Кинга-младшего в 2025 году.
Объяснение: DATE(C2,1,1) сокращается до 01.01.2025. WEEKDAY(DATE(C2,1,1)) сокращается до 4 (среда). Теперь формула сводится к 01.01.2025 + 14 + ВЫБРАТЬ(4,1,0,6, 5 ,4,3,2) = 01.01.2025 + 14 + 5 = 20.01.2025. Нам понадобилось 5 дополнительных дней, потому что до первого понедельника января осталось 5 дней.
Функция РАБДЕНЬ относится к категории функций даты и времени. Это помогает нам добавить или вычесть N рабочих дней из указанной нами даты.
Эта формула имеет различные варианты использования. Например, подумайте об оптовом бизнесе, который согласился доставлять заказы от розничных продавцов в течение 10 рабочих дней. Вместо того, чтобы отслеживать просроченные заказы вручную, оптовик может использовать функцию РАБДЕНЬ и быстро выявлять просроченные заказы.
Оглавление
Синтаксис
Синтаксис функции РАБДЕНЬ следующий:
Аргументы:
'start_date' — это обязательный аргумент, в который мы вводим либо дату, либо ссылку на ячейку, содержащую дату, с которой мы хотим начать наш расчет.
'дней' — это обязательный аргумент, в который мы вводим количество рабочих дней, которые мы хотим добавить или вычесть из значения в аргументе start_date.< /p>
'выходные дни' – необязательный аргумент, в котором вы можете указать массив дат, которые хотите исключить из рабочих дней. Даты могут быть представлены либо в виде диапазона ячеек, либо в виде массива порядковых номеров.
Важные характеристики функции РАБДЕНЬ
Примеры функции РАБДЕНЬ
Давайте рассмотрим несколько примеров, чтобы подробно понять функцию РАБДЕНЬ.
Пример 1. Простая ванильная формула для функции РАБДЕНЬ
Начнем с расчета рабочего дня через 15 дней от заданной даты. Мы пока не будем добавлять аргумент праздники. Мы будем использовать следующую формулу:
Здесь мы попытались рассчитать 15 рабочих дней в будущем, начиная с 25 декабря 2001 года, и функция вернула значение 15 января 2002 года. Обратите внимание, что в следующей строке мы всего лишь меняем значение в днях. аргумент отрицательный, чтобы вернуться на 15 рабочих дней назад, начиная с 25 декабря 2001 г. Тогда возврат, конечно, изменится на 4 декабря 2001 г.
Пример 2. Добавление аргумента праздники в микс
В нашем предыдущем примере функция вернула значение 15 января 2002 года. Однако действительно прошло 15 рабочих дней после введенной нами даты? Конечно, в этот период были и праздники. Для нашего следующего примера вам потребуется создать список праздников в декабре 2001 г. и январе 2002 г., чтобы учесть эти праздники в наших расчетах.
Когда закончите, используйте следующую формулу:
Теперь отчетность меняется на 17 января 2002 г., поскольку в рассматриваемом периоде есть два праздника (31 декабря и 1 января).
25 декабря тоже выходной. Однако включение нашей start_date в список праздников является спорным. Формула начинает свои вычисления, начиная со следующего дня, а start_date просто используется в качестве отправной точки.
Хорошо, это все, что нам нужно для взлета. Давайте поиграем мускулами, пристегнем ремни и поговорим о более сложных формулах.
Пример 3. Определение того, является ли дата рабочим днем
Допустим, вы хотите определить, приходится ли дата на рабочий день или нет. Вместо прокрутки календаря вы можете просто использовать для этого функцию РАБДЕНЬ.
Вы можете использовать следующую формулу, чтобы проверить, является ли дата рабочим днем или выходным/праздничным днем:
Чтобы понять эту формулу, давайте вернемся назад и посмотрим, как работает функция РАБДЕНЬ. Первое, что нам нужно сделать, это реализовать фундаментальный атрибут функции РАБДЕНЬ — она никогда не возвращает выходные/праздничные дни. Через минуту вы поймете, почему так важно, чтобы эта формула работала.
В формуле мы сначала вычли один день из start_date, а затем добавили 1 рабочий день с помощью аргумента days. Это эффективно возвращается к той же дате. Теперь вы можете задаться вопросом, почему мы не могли просто использовать следующую формулу:
Если вы попробуете использовать эту формулу, то поймете, что она не работает. Функция РАБДЕНЬ не вычисляет даты, если мы не указываем смещение в формуле. Итак, то, что мы делаем, вычитая и добавляя 1 день к дате, — это просто элементарная логика для достижения одной цели — заставить функцию РАБДЕНЬ получать ту же дату, что и в аргументе start_date.
Далее мы ввели список праздников. В нашем примере 25 декабря 2001 г. – это start_date, а также выходной день. Таким образом, функция РАБДЕНЬ вернет 26 декабря 2001 г. в качестве выходных данных.
Наконец, мы приравниваем эту дату к дате в ячейке A2 (т. е. к start_date). Если обе даты совпадают, окончательный результат будет отображаться как TRUE, а в противном случае FALSE. TRUE, по сути, говорит нам, что дата, возвращаемая функцией РАБДЕНЬ, совпадает с введенной датой, поэтому дата является рабочим днем, а FALSE говорит нам, что функция РАБДЕНЬ извлекла следующий рабочий день, поэтому дата должна быть выходным днем.< /p>
Теперь представьте, что 25 декабря 2001 года было воскресеньем, и предположим, что Рождество не добавлено в список праздников. Формула по-прежнему будет возвращать 26 декабря 2001 г., потому что помните, что она никогда не возвращает выходные/праздничные дни. Вот почему это важный атрибут для понимания.
Функция WORKDAY и WORKDAY.INTL
РАБДЕНЬ.МЕЖД — это расширение функции РАБДЕНЬ. Единственное отличие состоит в том, что функция РАБДЕНЬ.МЕЖД позволяет нам добавить дополнительный аргумент для предоставления пользовательских выходных. Ниже приведен синтаксис функции РАБДЕНЬ.МЕЖД:
Обратите внимание, что единственным отличием здесь от синтаксиса функции РАБДЕНЬ является наличие аргумента [выходные].
По умолчанию функции РАБДЕНЬ и РАБДЕНЬ.МЕЖД рассматривают субботу и воскресенье как выходные. Однако вы можете добавить собственные выходные в функцию РАБДЕНЬ.МЕЖД, используя следующие предопределенные коды, обозначающие выходные:
| 'выходной' код | Выходной |
|---|---|
| суббота, воскресенье | |
| 2 | воскресенье, понедельник | 3 | Понедельник, Вторник |
| 4 | Вторник, Среда | 5 | Среда, четверг |
| 6 | Четверг, пятница | 7 | пятница, суббота |
| 11 | воскресенье |
| 12 | Понедельник |
| 13 | Вторник |
| 14 | Среда |
| 15 | Четверг |
| 16 | Пятница |
| 17 | Суббота |
Давайте рассмотрим на примере, как работает функция РАБДЕНЬ.МЕЖД.
Скажем, у нас есть центр выполнения заказов, в котором хранятся запасы товаров и отправляются заказы клиентов. Теперь давайте предположим, что наш парк доставки выбрал двухдневный уик-энд, и все среды и четверги будут выходными. В этом случае мы хотели бы перенести некоторые из наших заказов, поставок и других торговых соглашений.
Все, что вам нужно сделать, это открыть электронную таблицу, указать даты получения заказов и ввести 30 в качестве аргумента days, при условии, что доставка занимает 30 рабочих дней. Далее мы будем использовать следующую формулу для расчета обновленных дат доставки на основе новых выходных, реализованных парком доставки:
Мы вставили 5 в качестве значения аргумента [weekend], потому что это код, представляющий выходные со среды по четверг. Обратите внимание, что некоторые даты были перенесены на предыдущий день, а некоторые — на следующий день.
Функция WORKDAY.INTL Пользовательские выходные
Другим заслуживающим внимания элементом этой формулы является код, который мы используем для аргумента выходные. Предопределенных кодов Excel обычно достаточно. Однако что произойдет, если ваш парк доставки решит, что понедельник и пятница должны быть выходными?
В этом случае мы используем текстовую строку в аргументе weekend, чтобы сообщить функции, какие дни нерабочие, а какие рабочие.Текстовая строка должна состоять из 7 символов, где первый символ представляет понедельник, а последний символ — воскресенье. В строке мы будем использовать комбинацию единиц и нулей, где 1 означает выходной день, а 0 — рабочий день.
Формула будет выглядеть следующим образом, если понедельник и пятница не работают:
Наши выходные данные теперь изменились, потому что мы снова изменили определение выходных в формуле. Использование 7-символьной текстовой строки позволяет вам не только установить два не следующих друг за другом выходных дня, но также позволяет вам установить 3 выходных дня, что невозможно при использовании предопределенных кодов Excel для аргумента выходные.< /p>
На этом наша сага о функциях WORKDAY завершается. Поэкспериментируйте с этими формулами и попытайтесь изменить их во время практики, чтобы увидеть, как это изменит вывод. Когда вы поддержите эти формулы, мы вернемся с другой функцией, чтобы сделать вашу жизнь немного проще.
Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!
Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.
Читайте также: