영업시간 계산 (휴일 제외, 09:00 시작, 끝은 실제로)

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=1wBMUjE6oOoZfsykxzlimh5NvMxith5Z

 

	A	B	C	D	E	F	G	H	I	J	K
1	요청		완료		소요시간		9:00	12:00		2008-01-01	신정
2	2008-04-20	9:00	2008-04-21	16:00	6.0		13:00	18:00		2008-02-06	설
3	2008-04-20	9:00	2008-04-21	10:00	1.0					2008-02-07	설
4	2008-04-21	8:00	2008-04-21	17:00	7.0		0.0			2008-02-08	설
5	2008-04-21	18:30	2008-04-22	19:00	9.0		6.0			2008-03-01	삼일절
6	2008-05-02	19:00	2008-05-06	14:00	4.0		0.0			2008-05-05	어린이날
7	2008-09-12	21:00	2008-09-16	12:00	3.0		6.0			2008-05-12	석가탄신일
8										2008-06-06	현충일
9										2008-08-15	광복절
10										2008-09-13	추석
11										2008-09-14	추석
12										2008-09-15	추석
13										2008-10-03	개천절
14										2008-12-25	기독탄신일

 

E2 =((WEEKDAY(A2,2)<6)*(COUNTIF(J$1:J$14,A2)=0)*SUMPRODUCT(H$1:H$2-N(B2<G$1:G$2)*G$1:G$2-N((B2>=G$1:G$2)*(B2<H$1:H$2))*B2-N(B2>=H$1:H$2)*H$1:H$2)+(WEEKDAY(C2,2)<6)*(COUNTIF(J$1:J$14,C2)=0)*(SUMPRODUCT(N(D2<G$1:G$2)*G$1:G$2+N((D2>=G$1:G$2)*(D2<H$1:H$2))*D2+N(D2>=H$1:H$2)*H$1:H$2-G$1:G$2)+N(D2>H$2)*(D2-H$2)))*24+SUMPRODUCT((ROW(INDIRECT(A2&":"&C2))>A2)*(ROW(INDIRECT(A2&":"&C2))<C2)*(WEEKDAY(ROW(INDIRECT(A2&":"&C2)),2)<6)*(COUNTIF(J$1:J$14,ROW(INDIRECT(A2&":"&C2)))=0)*8)-N(A2=C2)*8

 

E2의 수식 구조 :

G4 =(WEEKDAY(A2,2)<6)*(COUNTIF(J$1:J$14,A2)=0)*SUMPRODUCT(H$1:H$2-N(B2<G$1:G$2)*G$1:G$2-N((B2>=G$1:G$2)*(B2<H$1:H$2))*B2-N(B2>=H$1:H$2)*H$1:H$2)*24
G5 =(WEEKDAY(C2,2)<6)*(COUNTIF(J$1:J$14,C2)=0)*(SUMPRODUCT(N(D2<G$1:G$2)*G$1:G$2+N((D2>=G$1:G$2)*(D2<H$1:H$2))*D2+N(D2>=H$1:H$2)*H$1:H$2-G$1:G$2)+N(D2>H2)*(D2-H2))*24
G6 =SUMPRODUCT((ROW(INDIRECT(A2&":"&C2))>A2)*(ROW(INDIRECT(A2&":"&C2))<C2)*(WEEKDAY(ROW(INDIRECT(A2&":"&C2)),2)<6)*(COUNTIF(J$1:J$14,ROW(INDIRECT(A2&":"&C2)))=0)*8)-N(A2=C2)*8
G7 =SUM(G4:G6)