[구글시트] 소요시간 계산하기

구글시트에서 한 셀에 출근시간(시,분), 퇴근시간(시, 분)을 같이 입력했습니다. 그리고 9시간 30분 이후에는 1시간씩 추가 수당을 지급해야 합니다. 즉, OT계산을 위한 근무시간을 계산하는 방법을 찾아 봤습니다. mid로 텍스트를 추출해서 계산하였습니다.

근무시간을 맞춤숫자형식으로 바꿉니다.

저는 위에처럼 09:00에 출근해서 21:30에 퇴근을 했다면, 09002130 숫자만 입력을 합니다.

맞춤 숫자 형식으로 00:00-00:00으로 지정해 놓으면, 위 처럼 입력을 하면 09:00-21:30 으로 표시됩니다.

근무시간 마춤숫자형식으로 00:00-00:00 으로 설정

이제 OT를 계산해 보겠습니다.

MID 함수

MID함수

=MID(문자열, 시작, 추출_길이) 입니다.

근무시간의 7번째부터 2자리를 추출하고 근무시간의 1번째부터 2자리를 추출해서 빼면 근무한 시간이 나옵니다.

=MID(A2, 7, 2) - MID(A2, 1, 2) 
= 21 - 9 
=12
그래서 추가시간은 12시간으로 나옵니다. 여기에 10을 빼주겠습니다. 9시간 29분까지는 추가시간 급여가 없으니까요.
=MID(A2, 7, 2) - MID(A2, 1, 2) 
=3
시간은 3로 구했습니다.

 

이제 분을 계을 계산해 봅니다.

=MID(A2, 10, 2) - MID(A2, 3, 2)
=30-00
=30

즉 30분이 나옵니다. 30분 이상은 OT 1시간으로 계산해야 합니다.

뒤에 분에서 앞에 분을 뺏을 때, 30분보다 크거나 같은면 시간에서 -8를 해 주고
MID(A2, 10, 2) - MID(A2, 3, 2)>=30 
30분보다 작으면 -9을 해주었습니다.
MID(A2, 10, 2) - MID(A2, 3, 2)<30 

그리고 9시간29분이면, OT가 없으므로 공백으로 둡니다.

 

그래서 IFS를 사용했습니다.

IFS함수

=IFS(조건, 결과, 조건1, 결과1, 조건2, 결과2......)입니다.

근무시간이 9시간 29분이면 비워두고,

근무시간이 9시간 30분이상이면, 시간을 계산합니다.

=IFS
(근무시간이 9시간이거나 작고 분이 30분도 작으면 비워두고,
=IFS(AND(MID(A2,7,2)-MID(A2,1,2)<=9,MID(A2,10,2)-MID(A2,4,2)<30),

근무시간이 9시간이상이면, 시간을 뺀거에 -9를하고
MID(A2,7,2)-MID(A2,1,2)>9,MID(A2,7,2)-MID(A2,1,2)-9

분을 뺏을 때 30분이거나 이상이면 한시간을 덜 뺀다. -8
MID(A2,10,2)-MID(A2,4,2)>=30,MID(A2,7,2)-MID(A2,1,2)-8)

 

=IFS(AND(MID(A2,7,2)-MID(A2,1,2)<=9,MID(A2,10,2)-MID(A2,4,2)<30),"",MID(A2,7,2)-MID(A2,1,2)>9,MID(A2,7,2)-MID(A2,1,2)-9,MID(A2,10,2)-MID(A2,4,2)>=30,MID(A2,7,2)-MID(A2,1,2)-8)

 

 

OT계산

결과입니다.

9시간 29분이하는 공백

9시간 30분이상은 OT 1시간 계산

앞에서 두자리 MID(A2, 1, 2)는 LEFT(A2,2)로 대신할 수 있으며

뒤에서 두자리 MID(A2, 10, 2)는 RIGHT(A2,2)로 대신할 수 있습니다.