토요 교대근무 평일/특근 연장시간 구분

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

 

아래와 같이 설계해 보시는 게 어떨까 합니다. 날자-이름-출근시간-퇴근시간이 입력되었을 때 평일연장,특근,특근연장이 계산되도록 했습니다.

 

a~g는 분기를 위한 계산(예=1, 아니오=0), 1~3은 구하고자 하는 분류별 시간입니다.

 

a. 토요일인가, 아닌가? (weekday(날자,1)=7?)*(1-c)

b. 일요일인가, 아닌가? (weekday(날자,1)=1?)*(1-c)

c. 휴일인가, 아닌가? (별도 휴일목록에서 countif>0 활용)

d. A조인가, 아닌가? (조별 인원명단에서 countif >0 활용)

e. A조가 근무하는 날인가? (mod(weeknum (날자),2) 활용)

f. 연장시간 계산 (퇴근시간-(weekday*(1-c), vlookup(옵션1)활용, 0;18:00, 7;13:00))

g. 근무시간 계산 (정시퇴근-출근시간-점심/휴식시간(?))

 

1. 평일 연장시간= (not(or(a,b,c))+a*(n(d=e)))*f

2. 특근= (a*n(d<>e)+b+c)*g

3. 특근 연장시간= (a*n(d<>e)+b+c)*f

 

아래는 위 로직을 엑셀시트로 구현한 하나의 예시입니다.

 

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	날자	이름	출근	퇴근		평연	특근	특연		A조	B조		휴일목록
3	2008-02-04	갑	09:00	19:00		1.0	0.0	0.0		갑	을		2008-01-01		0	18:00
4	2008-02-09	갑	09:00	19:00		0.0	4.0	6.0		병	정		2008-02-07		7	13:00
5	2008-02-09	을	09:00	19:00		6.0	0.0	0.0		무	기		2008-03-01
6	2008-02-02	을	09:00	19:00		0.0	4.0	6.0		경	신		2008-05-05
7	2008-02-03	을	09:00	19:00		0.0	9.0	1.0		임	계		2008-05-12
8	2008-05-12	을	09:00	19:00		0.0	9.0	1.0

 

G3 =(NOT(OR((WEEKDAY(B3,1)=7),(WEEKDAY(B3,1)=1),(COUNTIF(N$3:N$10,B3)>0)))+N(WEEKDAY(B3,1)=7)*(N(N(COUNTIF(K$3:K$8,C3)>0)=MOD(WEEKNUM(B3),2))))*(VALUE(E3-VLOOKUP(WEEKDAY(B3,1)*(1-N(COUNTIF(N$3:N$10,B3)>0)),P$3:Q$4,2,1))-INT(VALUE(E3-VLOOKUP(WEEKDAY(B3,1)*(1-N(COUNTIF(N$3:N$10,B3)>0)),P$3:Q$4,2,1))))*24

H3 =(N(WEEKDAY(B3,1)=7)*N(N(COUNTIF(K$3:K$8,C3)>0)<>MOD(WEEKNUM(B3),2))+N(WEEKDAY(B3,1)=1)+N(COUNTIF(N$3:N$10,B3)>0))*(VLOOKUP(WEEKDAY(B3,1)*(1-N(COUNTIF(N$3:N$10,B3)>0)),P$3:Q$4,2,1)-D3-INT(VLOOKUP(WEEKDAY(B3,1)*(1-N(COUNTIF(N$3:N$10,B3)>0)),P$3:Q$4,2,1)-D3))*24

I3 =(N(WEEKDAY(B3,1)=7)*N(N(COUNTIF(K$3:K$8,C3)>0)<>MOD(WEEKNUM(B3),2))+N(WEEKDAY(B3,1)=1)+N(COUNTIF(N$3:N$10,B3)>0))*(VALUE(E3-VLOOKUP(WEEKDAY(B3,1)*(1-N(COUNTIF(N$3:N$10,B3)>0)),P$3:Q$4,2,1))-INT(VALUE(E3-VLOOKUP(WEEKDAY(B3,1)*(1-N(COUNTIF(N$3:N$10,B3)>0)),P$3:Q$4,2,1))))*24

 

수식이 복잡한 것 같지만 아래 수식을 짜깁은 것에 불과합니다. 그리고 (시간-시간)계산을 하면 엑셀이 nnnn.mmmm으로 수치를 내는 바 (X-INT(X))*24 하면 수치 단위의 시간을 얻습니다 (f, g가 그에 해당). G3:I3 잡아 아래로 데이타 행 만큼 복사하시면 표가 완성됩니다.

 

a =N(WEEKDAY(B3,1)=7)

b =N(WEEKDAY(B3,1)=1)

c =N(COUNTIF(N$3:N$10,B3)>0)

d =N(COUNTIF(K$3:K$8,C3)>0)

e =MOD(WEEKNUM(B3),2)

f =VALUE(E3-VLOOKUP(WEEKDAY(B3,1)*(1-H12),P$3:Q$4,2,1))

g =VLOOKUP(WEEKDAY(B3,1)*(1-H12),P$3:Q$4,2,1)-D3

 

위 예시에서는 출근시간이 정시 보다 빠를 경우, 점심시간, 휴식시간을 감안하지 않았습니다만, 위 수식들을 활용하여 (정시-출근시간)*특근여부 로써 연장시간에 추가할 수 있을 것입니다. 점심/휴식시간 따위 시간 구간이 많은 경우라면 오픈백과의 구간이 많은 시간을 참조하여 응용해 보십사 합니다. (오픈백과 링크 : http://kin.naver.com/open100/db_detail.php?d1id=1&dir_id=1050202&eid=44+2Rf2VuWeaGYFn0bJhJUc0WDyIeRye&l_url=)