Генеричка формула
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Резиме
Да бисте израчунали радно време између два датума према прилагођеном распореду, можете да користите формулу засновану на функцијама ВИКЕНД и СУМПРОДУЦТ, уз помоћ РЕДА, ИНДИРЕКТА и СРЕДИНЕ. У приказаном примеру, формула у Ф8 је:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Који враћа 36 сати, према прилагођеном распореду где се ради 8 сати од понедељка до петка, 4 сата раде у суботу, а понедељак 3. септембра је празник. Празници се нуде у именованом опсегу Г6: Г8. Распоред рада уноси се као текстуални низ у колону Д и може се мењати по жељи.
Напомена: Ово је формула низа која се мора унети помоћу Цонтрол + Схифт + Ентер. Ако имате стандардни радни дан од 8 сати, ова формула је једноставнија.
Објашњење
У основи, ова формула користи функцију ВЕЕКДАИ за утврђивање дана у недељи (тј. Понедељка, уторка итд.) За сваки дан између два дата датума. ВЕЕКДАИ враћа број између 1 и 7. Са подразумеваним подешавањима, Недеља = 1 и Субота = 7.
Трик ове формуле је састављање низа датума које можете унети у функцију ВЕЕКДАИ. Ово се ради помоћу РОВ са ИНДИРЕЦТ:
ROW(INDIRECT(B6&":"&C6))
РОВ тумачи спојене датуме као бројеве редова и враћа низ попут овог:
(43346;43347;43348;43349;43350;43351;43352)
Сваки број у низу представља датум. Функција ВЕЕКДАИ затим процењује низ и враћа низ вредности у радним данима:
(2;3;4;5;6;7;1)
Ови бројеви одговарају дану у недељи сваког датума. Они се МИД функцији пружају као аргумент почетног броја, заједно са вредношћу у Д6, "0888884" за текст:
MID("0888884",(2;3;4;5;6;7;1),1)
Пошто МИД-у дајемо низ почетних бројева, он враћа низ резултата попут овог:
("8";"8";"8";"8";"8";"4";"0")
Ове вредности одговарају радним сатима сваког дана од датума почетка до датума завршетка. Обратите пажњу да су вредности у овом низу текст, а не бројеви. Да бисмо претворили у стварне бројеве, множимо са другим низом створеним за управљање празницима, као што је објашњено у наставку. Математичка операција присиљава текст на нумеричке вредности.
Празници
За руковање празницима користимо ИСНА, МАТЦХ и именовани опсег „празници“ овако:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Овај израз користи МАТЦХ за проналажење датума који су у празницима именованог опсега користећи исти низ датума генерисаних горе са ИНДИРЕЦТ и РОВ. МАТЦХ враћа број када се пронађу празници и грешку # Н / А ако није. Функција ИСНА "преокреће" резултате тако да ТРУЕ представља празнике, а ФАЛСЕ не празнике. ИСНА враћа низ или резултате попут овог:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Коначно, оба низа се множе међусобно унутар СУМПРОДУЦТ-а. Математичка операција присиљава ТРУЕ и ФАЛСЕ на 1 и нулу, а вредности текста у првом низу на нумеричке вредности (као што је горе објашњено), тако да на крају имамо:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Након множења, унутар СУМПРОДУЦТ имамо један низ који садржи сва радна времена у временском распону:
=SUMPRODUCT((0;8;8;8;8;4;0))
СУМПРОДУЦТ затим сумира све ставке у низу и враћа резултат 36.
