기준 구간이 많은 시간 계산

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

 

기준구간이 많은 시간계산이군요. 아래를 참고해 보세요.

 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

의뢰

접수

날짜

시작

끝

차합

0:00

9:00

2

2008-07-10

오전

00:00

2008-07-22

오후

7:00

3.67

8:00

0:00

0.33

0:00

19:00

0.33

4.33

12:00

13:00

3

2008-07-11

오전

40:41

2008-07-11

오전

14:23

0

8:40

9:14

0.01

0:00

0:00

0.00

0.01

18:00

1

4

2008-07-12

오후

12:34

2008-07-12

오후

13:34

0

12:12

13:13

0.01

0:00

0:00

0.00

0.01

5

6

4.33

7

4

일

8:00:00

 

G2 =(DATEDIF(A2,D2,"d")+N(A2=D2)-1)*8/24 ; 이틀 이상 날짜 차이일 경우 추가 계산한 결과
H2 =C2+N(B2="오후")*TIME(12,,)                   ; 오전/오후를 엑셀 시계로 바꿈
I2 =IF(A2=D2,F2+N(E2="오후")*TIME(12,,),1)   ; 보통은 24:00, 날짜가 같을 경우 접수 시간
J2 =I2-H2-SUMPRODUCT((I2-$Q$1:$Q$3<0)*I2+(I2-$Q$1:$Q$3>=0)*((H2-$Q$1:$Q$3<0)*$Q$1:$Q$3+(H2-$Q$1:$Q$3>=0)*H2)-((H2-$P$1:$P$3>0)*H2+(H2-$P$1:$P$3<=0)*((I2-$P$1:$P$3>0)*$P$1:$P$3+(I2-$P$1:$P$3<=0)*I2))) ; J2를 M2에 복사
K2 =0
L2 =IF(A2=D2,K2,F2+N(E2="오후")*TIME(12,,))
N2 =SUM(G2,J2,M2)

 

G2:N2 잡아 아래로 채워넣으면 표가 완성됩니다. N열의 차이합계는 24시간일 경우 1로 표시됩니다. 그걸 hh:mm:ss 형식으로 표시하려 하면 24시간이 넘는 N2 경우는 표시가 되지 않지요. 구지 표시하려면 A7, D7 처럼 정수부분을 따로 표시하고 소수 부분만 셀서식을 hh:mm:ss로 지정하면 됩니다. (토, 일, 휴일이 있을 경우 모두 빼려면 별도 수식을 넣어줘야 하며, 질문에 명시되어 있지 않아 제외했습니다.)

 

아래는 J2 수식이 어떻게 나왔는지, 프로그래밍 메모입니다. 관심 있다시면 함께 참고해 보세요.

 

		a	─	─	b				終	始
x	y								y	y
x				y					y	a
x							y		b	a
			x	y					y	x
			x				y		b	x
						x	y		x	x

 

기준 구간 a-b 에 대해 데이타 구간 x-y가 주어졌을 때 끝시간-시작시간의 수식은

=if(y<b,y,if(x<b,b,x)) - if(x>a,x,if(y>a,a,y)) 가 됩니다. 이걸 SUMPRODUCT에서도 먹혀들도록 N(논리식)으로 바꾼 것이 J2의 수식입니다.