Учитывая диапазон дат, подсчитайте количество рабочих дней за период оплаты, в который попадает этот диапазон.

Метод сводной таблицы. Введите эту формулу

G1:12/26/2008
G2:=G1+14

и заполните до строки 28. Затем

A14: Date
A15: 1/21/2009
A16: =A15+1
B14: Count
B15: {=SUM(($B$1:$B$10<=A15)*(WEEKDAY(A15)<7)*(WEEKDAY(A15)>1)*($D$1:$D$10>=A15))}
C14: Week
C15: =INDEX($G$1:$G$28,MATCH(A15+13,$G$1:$G$28))

Заполните формулы A, B и C до строки 321, чтобы остановиться на 23.11.09 (или дальше, если хотите).

Выберите A14:C321 и создайте сводную таблицу. Поместите «Неделя» в область строк и «Количество» в область данных.

Введите следующие формулы

G1:12/26/2008
G2:=G1+14

и заполните до строки 28. Затем

D1:=IF(C1=DATE(9999,12,31),TODAY(),C1)
E1:=NETWORKDAYS(B1,INDEX($G$1:$G$28,MATCH(B1+13,$G$1:$G$28)))
F1:=10-NETWORKDAYS(D1+1,INDEX($G$1:$G$28,MATCH(D1+14,$G$1:$G$28)))

и заполнить по мере необходимости. потом

H1:{=SUM(($B$1:$B$10<=G1-14)*($D$1:$D$10>G1))}
I1:{=SUM(($B$1:$B$10<G1)*($B$1:$B$10>=G1-14)*($E$1:$E$10))}
J1:{=SUM(($D$1:$D$10>G1-14)*($D$1:$D$10<=G1)*($F$1:$F$10))}
K1:=SUM(H1*10,I1,J1)

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

Очевидно, вы можете объединить несколько столбцов для краткости, если хотите.

Здесь я делаю несколько предположений:

1. Тип отпуска указан в столбце A. Даты начала и окончания указаны в столбцах B и C.
2. Ряды начинаются с 1-го ряда.
3. Вас интересуют только платежные периоды, начинающиеся 14 декабря 2008 г.
4. Вы не принимаете во внимание банковские/государственные праздники (хотя их учет не так уж и важен).
5. Отпуск работника включает в себя дату окончания.

Я использовал несколько ячеек для решения этой проблемы:

• В ячейке D1: =ЕСЛИ(C1=ДАТА(9999,12,31),СЕГОДНЯ(),C1)
• В ячейке E1: =B1 - MOD(B1-ДАТА(2008,12,14),14) - 1
• В ячейке F1: =D1 - MOD(D1-ДАТА(2008,12,14),14) - 1
• В ячейке G1: =(F1-E1)/14 - 1
• В ячейке H1: =(ЧИСТРАБДНИ(E1,B1) + ЧИСТРАБДНИ(F1,D1) + (G1 - 2) * 10)/ G1

Ячейка D1 определяет, продолжается ли отпуск. Ячейка G1 подсчитывает количество периодов оплаты между начальным и конечным периодами оплаты. Ячейка H1 содержит ответ, который вы ищете.

Вы можете скопировать эти ячейки для других строк. Кроме того, я бы рассмотрел возможность замены TODAY() на 31.12.9999 для столбца C, если это возможно.

ОБНОВЛЕНО

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

Я делаю те же предположения, что и выше, за исключением того, что предположение 2 заменено предположением о том, что строки начинаются со строки 2.

Я определил следующие имена:

• FirstPayPeriod: =ДАТА(2008, 12,14)
• PayPeriodLength: = 14
• MaxDaysPerPeriod: =NETWORKDAYS(FirstPayPeriod, FirstPayPeriod + PayPeriodLength)
• NoEndDate: =ДАТА(9999, 12, 31)

Я определил следующие ячейки и скопировал нужное количество строк:

• Ячейка D2: = ЕСЛИ (C2 = NoEndDate, FirstPayPeriod + PayPeriodLength * ROUNDUP ((СЕГОДНЯ() - FirstPayPeriod)/PayPeriodLength, 0) - 1, C2)
• Ячейка E2: =ЧИСТРАБДНИ(B2,D2)

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

• Ячейка F1: =FirstPayPeriod
• Ячейка G1: = F1 + PayPeriodLength

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

Наконец, я определил следующую ячейку и скопировал ее в столько столбцов справа, сколько есть периодов оплаты (ЗА ИСКЛЮЧЕНИЕМ ПОСЛЕДНЕГО ПЕРИОДА ПЛАТЕЖА — СМ. НИЖЕ), а также скопировал вниз во столько строк, сколько есть периодов оплаты. это данные:

• Ячейка F2: =ЕСЛИ(ИЛИ(G$1 ‹= $B2, F$1 > $D2), 0, МИН($E2, ЧИСТРАБДНИ(F$1, G$1 - 1), ЧИСТРАБДНИ($B2, G$1), ЧИСТРАБДНИ (F$1, $D2)))

Для последнего платежного периода я сделал следующее. Предположим, что столбец AD содержит данные за последний платежный период:

• Ячейка AD2: = ЕСЛИ (AC$1 > $D2, 0, MIN($E2, ЧИСТРАБДНИ(AD$1, AD$1 + PayPeriodLength - 1), ЧИСТРАБДНИ(AC$1, $D2)))

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