День выплаты может быть одним из восьми возможных дней:
- 12-е (если 13-е - это Страстная пятница, потому что тогда 13-е, 14-е и 15-е будут трехдневными выходными)
- 13 (если это последний рабочий день недели *)
- 14-е (если это последний рабочий день недели *)
- 15 (если это рабочий день *)
- с 4-го по последний день месяца (если с 3-го по последний день - Страстная пятница, потому что тогда последние три дня месяца образуют трехдневные выходные)
- с третьего по последний день месяца (если это последний рабочий день недели *)
- 2-ой-последний день месяца (если это последний рабочий день недели *)
- последний день месяца (если это рабочий день *)
__________
* Рабочий день - с понедельника по пятницу, за исключением Страстной пятницы.
В Excel 2013
WEEKDAY(date, 2) возвращает 1, если
date - понедельник,…, 7, если
date - воскресенье.
Это указано как недавнее расширение; если это не сработает для вас, так и скажите, и мы можем обойти это.
Используя эту
WEEKDAY функцию, и
EOMONTH функцию , которая сообщает нам в последний день месяца, мы можем перевести приведенный выше список к следующей формуле:
=AND(OR(AND(DAY(A1)=12,A1+1=F$1), AND(DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(DAY(A1)=15,WEEKDAY(A1,2)<=5), AND(EOMONTH(A1,0)-A1=3,A1+1=F$1), AND(EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5)), A1<>F$1)
где дата Страстной пятницы (14 апреля 2017 г.) (вручную) вводится в ячейку F1 .
Эта формула может быть грубо разбита на
=AND(
OR( AND(something1), AND(something2), AND(something3),
AND(something4), AND(something5), AND(something6)
),
something7
)
где
something1 - это DAY(A1)=12,A1+1=F$1 , который проверяет первую пулю (четверг, 12-е число месяца, когда следующий день - Страстная пятница).something2 - это DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1) , что проверяет вторую и третью пули (13-й или 14-е в пятницу или в день (четверг) до Страстной пятницы).something3 - это DAY(A1)=15,WEEKDAY(A1,2)<=5) , который проверяет четвертую пулю (15-е число в будний день).something4 - это EOMONTH(A1,0)-A1=3,A1+1=F$1 , что проверяет пятую пулю (с 4-го по последний день месяца, когда следующий день - Страстная пятница).something5 - это EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1) , который проверяет наличие 6-я и 7-я марки (2-й или 3-й до последнего дня месяца, в пятницу или за день до Страстной пятницы).something6 - это A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5 , что проверяет восьмую пулю (последний день месяца, в будний день).something7 - это A1<>F$1 , что на сегодняшний день является Страстной пятницей и исключает ее.
Без этого теста мы выделим (цвет / оттенок) четверг, 13 апреля, и пятницу, 14 апреля.
Чтобы проверить это, вы можете установить F1 на каждую из следующих дат на 2017 год и убедиться, что выделены правильные дни.
- Фактическая Страстная пятница этого года: 14 апреля, которая заставляет день выплаты жалованья быть в четверг, 13 апреля.
- 13 января, что приведет к выплате в четверг, 12 января.
- 15 сентября (или 15 декабря), что приведет к выплате в четверг 14-го числа.
- Пятница, 28 апреля, что приведет к выплате в четверг, 27 апреля.
- 29 сентября, что приведет к выплате в четверг, 28 сентября.
- 31 марта (или 30 июня), что приведет к тому, что выплаты начнутся со второго по последний день этого месяца.
Примечание. Если для F1 задана дата, которая не является пятницей, будут отображаться неверные результаты (например, более двух дней выплаты в месяц).
