시급 계산기

12시 부터 1시간, 24시 부터 1시간 밥 먹는 시간 제외, 24시간 이내 시급 계산하기...

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	날짜	시작	끝	시급	일변	01-06	06-12	13-22	22-24	01-06	06-12	13-22	22-24	계	야간	주간
3	7월20일	1	0	99,500	1	5	6	9	2	0	0	0	0	22	5,000	3,500
4	7월21일	21	9	44,500	1	0	0	1	2	5	3	0	0	11
5	7월22일	22	7	29,500	1	0	0	0	2	5	1	0	0	8
6	7월23일	12	23	48,500	0	0	0	9	1	0	0	0	0	10
7	7월24일	6	22	75,000	0	0	6	9	0	0	0	0	0	15

 

F3 =N(D3<C3)   ; 날짜 변경이 있는지 없는지 판단

G3 =IF(D3+F3*24>6,6,IF(D3+F3*24<=1,1,F3*24+D3))-IF(C3<1,1,IF(C3>=6,6,C3))

H3 =IF(D3+F3*24>12,12,IF(D3+F3*24<=6,6,F3*24+D3))-IF(C3<6,6,IF(C3>=12,12,C3))

I3 =IF(D3+F3*24>22,22,IF(D3+F3*24<=13,13,F3*24+D3))-IF(C3<13,13,IF(C3>=22,22,C3))

J3 =IF(D3+F3*24>24,24,IF(D3+F3*24<=22,22,F3*24+D3))-IF(C3<22,22,IF(C3>=24,24,C3))

K3 =IF(D3+F3*24>6+24,6+24,IF(D3+F3*24<=1+24,1+24,F3*24+D3))-IF(C3<1+24,1+24,IF(C3>=6+24,6+24,C3))

L3 =IF(D3+F3*24>12+24,12+24,IF(D3+F3*24<=6+24,6+24,F3*24+D3))-IF(C3<6+24,6+24,IF(C3>=12+24,12+24,C3))

M3 =IF(D3+F3*24>22+24,22+24,IF(D3+F3*24<=13+24,13+24,F3*24+D3))-IF(C3<13+24,13+24,IF(C3>=22+24,22+24,C3))

N3 =IF(D3+F3*24>24+24,24+24,IF(D3+F3*24<=22+24,22+24,F3*24+D3))-IF(C3<22+24,22+24,IF(C3>=24+24,24+24,C3))

O3 =SUM(G3:N3) ; 총 근무 시간 (참조용)

E3 =SUM(H3:I3,L3:M3)*P$3+SUM(G3,J3:K3,N3)*Q$3

; F3:O3 잡아 아래로 원하는 만큼 복사하면 작업 끝.

 

수식설명 : 구간별로 해당 시간을 구함. 시작을 S, 끝을 F라고 두었을 때 a시~b시 구간에 해당하는 경우 (F<a이면 a, a<F<b이면 F, b<F면 b) - (S<a이면 c, a<S<b이면 S, S>b이면 b) 시간임. G3부터 N3는 구간만 다를 뿐 같은 로직. 이걸 한 셀에 밀어넣을 수도 있겠지만 무척 길어질 것임. 더 쌈박한 로직이 있을 것 같은데 떠오르지 않음. 이것저것 비교할 것이 많아 뜻과 같지 않았음. 여하튼 위 수식으로 시작/끝 시간만 입력하면 24시간 이내 어떤 구간이라도 해당 시급을 계산합니다.

 

(2008.11.13 이렇게 시각적으로 하지 않아도 SUMPRODUCT를 이용한 구간이 많은 시간계산을 활용하면 보다 간단하게 될 길이 있을 것임)