근무시간 계산 (전일 퇴근시간에 따라 기준시간 보정, 지각시간 계산)

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

 

	A	B	C	D	E	F	G	H
1	출근	9:00		야출	10:00		점심	12:00
2	퇴근	18:00			18:00			13:00
3	성명	수		목		금		토
4		1		2		3		4
5		9:51		9:50
6		0:06		17:45
7	근무	13:15		6:55
8	점심	1:00		1:00
9	외출
10	지각/조퇴			0:50	0:15

 

D7 =D6+N(D6<D5)-D5-D8-D9
D8 =IF(N(D6<D5)+D6<$H$2,N(D6<D5)+D6,IF(D5<$H$2,$H$2,D5)) - IF(D5>$H$1,D5,IF(N(D6<D5)+D6>$H$1,$H$1,N(D6<D5)+D6))
D10 =IF(D5>IF(N(B6<B5)+B6>="22:00",$E$1,$B$1),D5-IF(N(B6<B5)+B6>="22:00",$E$1,$B$1),0)
E10 =IF(N(D6<D5)+D6<$B$2,$B$2-(N(D6<D5)+D6),0)

 

수식을 간단히 설명하면, D7은 근무시간으로서 퇴근-출근-점심-외출의 수식으로 계산됩니다. 다만 입력된 퇴근시간이 출근시간보다 작을 경우 철야한 것으로 보고 '1' (엑셀은 하루 24시간을 1로 간주함)을 더해줍니다. 앞으로 수식에서 퇴근시간은 모두 N(퇴근<출근)을 이용, 1을 필요할 때 마다 더해준 것으로 사용하게 됩니다.

 

D8의 점심시간은 (실제로는 그럴 경우가 거의 없겠지만) 출근, 퇴근시간이 점심시간과 겹칠 경우에도 정확히 점심시간을 계산할 필요가 있으므로 아래의 로직을 사용합니다.

 

		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

 

점심시간 = if(y<b,y,if(x<b,b,x)) - if(x>a,x,if(y>a,a,y))

 

D10은 지각을 계산한 것인데, 지각은 전날 22:00를 넘었느냐 아니냐를 봐야 하므로 제시된 표의 첫날(B10)은 계산할 수 없습니다. 전날 출퇴근시간이 다른 시트에 있다면 D10 수식의 B5, B6로 되어 있는 주소를 적절히 바꿔주면 가능하겠지요.

 

E10의 조퇴 계산은 전날 퇴근시간에 관계없이 모두 "18:00"이 정시 퇴근시간으로 되어 있으므로 B2를 기준으로 두었습니다. 만약 전날 퇴근에 따라 "17:00"이 정시 퇴근인 경우라면 D10과 같이 IF문으로 보정해 주어야 합니다.